ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 047-040

047-040

原创 Linux操作系统 作者:jbymy2000 时间:2012-03-17 09:58:17 0 删除 编辑
40. User OE, the owner of the ORDERS table, issues the following command:
GRANT SELECT ,INSERT ON orders TO hr WITH GRANT OPTION.
The user HR issues the following command:
GRANT SELECT ON oe.orders TO scott.
Then, OE issues the following command:
REVOKE ALL ON orders FROM hr.
Which statement is correct?
A. The user SCOTT loses the privilege to select rows from OE.ORDERS.
B. The user SCOTT retains the privilege to select rows from OE.ORDERS.
C. The REVOKE statement generates an error because OE has to first revoke
the SELECT privilege from SCOTT.
D. The REVOKE statement generates an error because the ALL keyword cannot
be used for privileges that have been granted using WITH GRANT OPTION.
Answer: A
Sys用户下:grant create table to test1 with admin option;
而后test1用户授予test2了,然后sys收回test1,则test2的系统权限还在,对象权限则收回了.
用户获得权限不需要重新登录即生效,系统权限赋给角色直接生效,角色赋给角色需要重新
登录, 角色赋予的权限是不级联的
另: 通过角色得到的DDL 权限在DDL 操作中是可用的, 通过角色得到的DML 权限在DDL 操
作中不可用, this may explain why you grant select on table to role. And then you grant
role to ueer.But when you create procedure included the statement ”select count(*)
from table ”, it get error.
查看用户具有的系统权限
select * from dba_sys_privs where grantee='TEST1';
查看用户具有的对象权限
select * from dba_tab_privs where grantee='TEST2';
查看角色具有的系统权限
select * from dba_sys_privs where grantee='DBA';
查看角色具有的对象权限
select * from dba_tab_privs where grantee='role_test';
查看用户具有的角色
select * from dba_role_privs where grantee='ROLE_TEST'
A、B、C三个用户,A用户把自己表的权限赋给了B、B又赋给了C,这时侯A用户是不能直接
revoke C用户关于该表的权限。(实验结果表明可以执行,但没有效果)但可以revoke B用户关于
该表权限,而C用户关于该表的权限也取消呢?(实验表明是发生了级联反映)
SQL> create user oe identified by oe default tablespace users;
SQL> create user scott identified by scott default tablespace users;
SQL> create user hr identified by hr default tablespace users;
SQL> grant create session,create table to oe;
SQL> grant create session to hr,scott;
Sql> grant unlimited tablespace to oe;
SQL> connec oe/oe
Sql> create table ORDERS(a int,b int);
SQL> insert into orders values(1,2); commit;
Sql> GRANT SELECT ,INSERT ON orders TO hr WITH GRANT OPTION;
SQL> connect hr/hr
Sql> GRANT SELECT ON oe.orders TO scott;
SQL> select * from oe.orders;有数据
SQL> connect scott/scott
SQL> select * from oe.orders;也有数据
现在 oe用户 REVOKE ALL ON orders FROM hr,分别查看此时用户hr和scott对表情况
在oe连接下执行
sql> REVOKE ALL ON orders FROM hr;
此时在hr用户下再执行
SQL> select * from oe.orders;
第 1 行出现错误:
ORA-00942: 表或视图不存在
说明 对hr的权限收回成功,但此时scott的权限是否也级联收回呢?
在scott下执行
SQL> select * from oe.orders;
第 1 行出现错误:
ORA-00942: 表或视图不存在
说明发生了 “ascade”的效应啊?但上面英文明明说不能级联的啊?????
现在恢复初始,看oe用户能否直接revoke 用户scott
在oe下执行
SQL> GRANT SELECT ,INSERT ON orders TO hr WITH GRANT OPTION;
在 hr下执行(如果hr不执行下面语句,则scott是无法获得对oe.orders操作的)
SQL> GRANT SELECT ON oe.orders TO scott;
此时scott可以操作oe.orders表了
而后在oe下直接执行
sql> REVOKE ALL ON orders FROM scott;
可以执行成功,但好像权限并没有收回
scott仍然可以操作oe.orders表!!!!!!!!
下面实验角色的赋予和收回(角色赋予的权限是不级联的)
资料:If a user grants a role to another user and uses the WITH ADMIN OPTION, the
second user may further grant the same role to a third user. If the first user revokes
the role from the second user, the third user retains the role until it is explicitly
revoked from the third user by a qualified user.
In other words—the REVOKE statement for system privileges does not “cascade”.
It only applies to the user to whom the revocation is applied.
1:删除以上用户并且重建oe和scott两个用户
2:在sys用户下执行
Sql> GRANT CONNECT TO oe WITH ADMIN OPTION;
此时oe可以登陆,而scott登陆则报错 ORA-01045: 用户 SCOTT 没有 CREATE SESSION 权限;
3:用oe用户登陆执行
Sql> GRANT CONNECT TO scott WITH ADMIN OPTION;
此时scott可以登陆连接了
4: 用sys用户执行
sql> revoke CONNECT from oe;
此时 oe已经不再能登陆连接了,而 scott用户仍然可以登陆连接,说明 角色对应的权限并没有
被级联收回,验证了上面英文说明!!!!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11312660/viewspace-718809/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 047-034
下一篇: 047-042
请登录后发表评论 登录
全部评论

注册时间:2012-01-10

  • 博文量
    416
  • 访问量
    202986