对象权限测试:
初始状态下,user2和user3都没有user1.t1的对象权限
user1下存在t1表:
duzz$user1@orcl>select * from t1;
C1
----------
1111
Elapsed: 00:00:00.00
user2不能查询user1.t1:
duzz$user2@orcl>select * from user1.t1;
select * from user1.t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.00
user3不能查询user1.t1:
duzz$user3@orcl>select * from user1.t1;
select * from user1.t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.01
user1赋予user2在t1上的对象权限,同时允许user2将该权限赋予别的用户:
duzz$user1@orcl>grant all on t1 to user2 with grant option;
Grant succeeded.
Elapsed: 00:00:00.00
user2已经可以查询user1.t1,同时user2将user1.t1的对象权限赋予user3:
duzz$user2@orcl>select * from user1.t1;
C1
----------
1111
Elapsed: 00:00:00.00
duzz$user2@orcl>grant all on user1.t1 to user3;
Grant succeeded.
Elapsed: 00:00:00.00
duzz$user2@orcl>
user3也可以查询user1.t1了:
duzz$user3@orcl>select * from user1.t1;
C1
----------
1111
Elapsed: 00:00:00.00
user1尝试直接从user3那里回收权限,执行语句虽然没有报错,但是并没有实际作用:
duzz$user1@orcl>revoke all on t1 from user3;
Revoke succeeded.
Elapsed: 00:00:00.00
user3仍然能够查询user1.t1:
duzz$user3@orcl>select * from user1.t1;
C1
----------
1111
Elapsed: 00:00:00.00
user1这次开始从user2那里回收权限,
duzz$user1@orcl>revoke all on t1 from user2;
Revoke succeeded.
Elapsed: 00:00:00.01
user2权限被收回:
duzz$user2@orcl>select * from user1.t1;
select * from user1.t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.01
与此同时,user3的权限也被收回:
duzz$user3@orcl>select * from user1.t1;
select * from user1.t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.01
对于对象权限,虽然不能从非直接授予的用户回收,但是回收具有级联性。
系统权限测试:
user1具有CREATE VIEW的系统权限,同时该权限可以grant给别的用户,user2和user3则没有该权限:
duzz$user1@orcl>select * from session_privs;
PRIVILEGE
---------------------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE VIEW
Elapsed: 00:00:00.00
user2没有CREATE VIEW的系统权限:
duzz$user2@orcl>select * from session_privs;
PRIVILEGE
------------------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
Elapsed: 00:00:00.01
user3也没有CREATE VIEW的系统权限:
duzz$user3@orcl>select * from session_privs;
PRIVILEGE
------------------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
Elapsed: 00:00:00.01
user1将CREATE VIEW的权限赋予user2,同时赋予user2可以将该权限赋予其它用户的权限:
duzz$user1@orcl>grant create view to user2 with admin option;
Grant succeeded.
Elapsed: 00:00:00.01
user2获得CREATE VIEW的权限:
duzz$user2@orcl>select * from session_privs;
PRIVILEGE
-------------------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE VIEW
Elapsed: 00:00:00.00
user2将CREATE VIEW的权限赋予user3:
duzz$user2@orcl>grant create view to user3;
Grant succeeded.
Elapsed: 00:00:00.00
user3获得CREATE VIEW的权限:
duzz$user3@orcl>select * from session_privs;
PRIVILEGE
--------------------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE VIEW
Elapsed: 00:00:00.01
不同于对象权限,系统权限可以直接从非直接授予的用户上收回。
user1尝试直接从user3上回收CREATE VIEW的权限:
duzz$user1@orcl>revoke create view from user3;
Revoke succeeded.
Elapsed: 00:00:00.01
user3的CREATE VIEW的权限被收回:
duzz$user3@orcl>select * from session_privs;
PRIVILEGE
---------------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
Elapsed: 00:00:00.01
测试系统权限是不是也像对象权限一样具有撤销级联性。
user2再次将CREATE VIEW的权限赋予user3:
duzz$user2@orcl>grant create view to user3;
Grant succeeded.
Elapsed: 00:00:00.01
user1收回user2的CREATE VIEW的权限:
duzz$user1@orcl>revoke create view from user2;
Revoke succeeded.
Elapsed: 00:00:00.00
user2的CREATE VIEW的权限被收回:
duzz$user2@orcl>select * from session_privs;
PRIVILEGE
------------------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
Elapsed: 00:00:00.00
不过,user3仍然拥有CREATE VIEW的权限:
duzz$user3@orcl>select * from session_privs;
PRIVILEGE
-------------------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE VIEW
Elapsed: 00:00:00.00
对于系统权限,可以从非直接授予的用户收回权限,但是不具备回收级联性。
总结:
对于对象权限,虽然不能从非直接授予的用户回收,但是回收具有级联性。
对于系统权限,可以从非直接授予的用户收回权限,但是不具备回收级联性。
Oracle PL/SQL之对象权限与系统权限的撤销差异
原创mb61c186c579369 ©著作权
©著作权归作者所有:来自51CTO博客作者mb61c186c579369的原创作品,请联系作者获取转载授权,否则将追究法律责任
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
mysql赋予所有database权限 mysql 赋予权限
这篇文章主要介绍了MySQL中基本的用户和权限管理方法,包括各个权限所能操作的事务以及操作权限的一些常用命令语句,是MySQL入门学习中的基础知识,需要的朋友可以参考下一、简介 各大帖子及文章都会讲到数据库的权限按最小权限为原则,这句话本身没有错,但是却是一句空话。因为最小权限,这个东西太抽象,很多时候你并弄不清楚具体他需要哪些权限。 现在很多mysql用着root账户在操
mysql赋予所有database权限 linux mysql mysql mysql5.7 mysql+linux