privs与roles

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 

实验：

SQL> create user a identified by a default tablespace users;


User created.


SQL> create user b identified by a default tablespace users;


User created.


SQL> create user c identified by a default tablespace users;


User created.


SQL> grant create session,create table to a;


Grant succeeded.


SQL> grant create session to b,c;


Grant succeeded.


SQL> grant unlimited tablespace to a;


Grant succeeded.

SQL> conn a/a     
Connected.
SQL> create table ORDERS(a int,b int); 


Table created.



SQL> insert into orders values(1,2); 


1 row created.


SQL> commit;


Commit complete.


SQL> GRANT SELECT ,INSERT ON orders TO b WITH GRANT OPTION; 


Grant succeeded.


SQL> conn b/a
Connected.

SQL> GRANT SELECT ON a.orders TO c; 


Grant succeeded.


SQL> select * from a.orders;


         A          B
---------- ----------
         1          2


SQL> conn c/a
Connected.
SQL> select * from a.orders;


         A          B
---------- ----------
         1          2


SQL> conn a/a
Connected.
SQL>  REVOKE ALL ON orders FROM b;


Revoke succeeded.


SQL> conn b/a
Connected.
SQL> select * from a.orders;
select * from a.orders
                *
ERROR at line 1:
ORA-00942: table or view does not exist




SQL> conn c/a
Connected.
SQL> select * from a.orders;
select * from a.orders
                *
ERROR at line 1:
ORA-00942: table or view does not exist

<span style="color:#ff0000;">//对象权限会cascade</span>

</pre><pre code_snippet_id="407491" snippet_file_name="blog_20140626_4_2409646" name="code" class="html">

<pre name="code" class="html">Connected.

SQL> GRANT SELECT ,INSERT ON orders TO b WITH GRANT OPTION;

Grant succeeded.

SQL> conn b/a                 
Connected.
SQL> GRANT SELECT ON a.orders TO c;

Grant succeeded.

SQL> conn a/a
Connected.
SQL>  REVOKE ALL ON orders FROM c;

Revoke succeeded.

SQL> conn c/a
Connected.

SQL> select * from a.orders;

         A          B
---------- ----------
         1          2

SQL> insert into a.orders values(3,4);
insert into a.orders values(3,4)
              *
ERROR at line 1:
ORA-01031: insufficient privileges

<span style="color:#ff0000;">// insert 权限收回了，但是select权限没有收回</span>

</pre><pre code_snippet_id="407491" snippet_file_name="blog_20140626_9_9073549" name="code" class="html">

</pre><pre code_snippet_id="407491" snippet_file_name="blog_20140626_9_9073549" name="code" class="html">

If a user grants a role to another userand uses the WITH ADMIN OPTION, the 
second user may further grant the same role to a third user. Ifthe 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 doesnot “cascade”. 
It only applies to the user to whom the revocation is applied. 
1:删除以上用户并且重建a和b两个用户 
2:在sys用户下执行 

Sql> GRANT CONNECT TO a WITH ADMIN OPTION; 

此时a可以登录，而b不可以

SQL> conn a/a;
Connected.
SQL> conn b/a
ERROR:
ORA-01045: user B lacks CREATE SESSION privilege; logon denied




Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> conn a/a
Connected.
SQL> GRANT CONNECT TO b WITH ADMIN OPTION; 


Grant succeeded.


SQL> conn b/a
Connected.
SQL> conn / as sysdba
Connected.
SQL>  revoke CONNECT from a;


Revoke succeeded.


SQL> conn a/a
ERROR:
ORA-01045: user A lacks CREATE SESSION privilege; logon denied




Warning: You are no longer connected to ORACLE.
SQL> conn b/a
Connected.
SQL> /<span style="color:#ff0000;">/角色赋予的权限是不级联的</span>