Oracle中WITH ...... OPTION权限对于权限授予和收回的级联影响

1. WITH ADMIN OPTION实验：

使用SYS用户做一下操作：
创建两个用户A和B并赋予密码,并且创建时两个用户均为非锁定状态：
CREATE USER a IDENTIFIED BY oracle ACCOUNT UNLOCK;
CREATE USER b IDENTIFIED BY oracle ACCOUNT UNLOCK;

检查当前用户系统权限：

SQL> SELECT GRANTEE,PRIVILEGE FROM dba_sys_privs WHERE GRANTEE='A';

no rows selected

SQL> SELECT GRANTEE,PRIVILEGE FROM dba_sys_privs WHERE GRANTEE='B';

no rows selected

SYS赋予A用户CREATE SESSION系统权限的同时并赋予WITH ADMIN OPTION;权限
GRANT CREATE SESSION TO a WITH ADMIN OPTION;

/* 
    可赋系统权限用户，将系统权限赋予另一用户时，会在dba_sys_privs表中产生一条记录。
    将被赋权用户的用户名存入grantee字段，并且所有字母按全大写存储
    
    SQL> DESC dba_sys_privs;
    
    Name             Null?      Type
    ----------------------- -------- ----------------
    GRANTEE              VARCHAR2(128)
    PRIVILEGE              VARCHAR2(40)
    ADMIN_OPTION              VARCHAR2(3)
    COMMON               VARCHAR2(3)
    INHERITED              VARCHAR2(3)
*/

检查A用户系统权限：
SQL> SELECT * FROM dba_sys_privs WHERE GRANTEE = 'A';

GRANTEE     PRIVILEGE            ADMIN_OPTION    COMMON    INHERITED
-------- ------------------ --------------- ------- ----------
A          CREATE SESSION        YES             NO      NO

使用A用户登录到ORACLE数据库中：
CONN a/oracle

使用A用户赋予B用户create session权限
GRANT CREATE SESSION TO b;

登录回SYS用户，用SYS用户检查B用户的系统权限
/*
    SQL> SELECT * FROM dba_sys_privs WHERE GRANTEE = 'B';

    GRANTEE     PRIVILEGE            ADMIN_OPTION    COMMON    INHERITED
    -------- ------------------ --------------- ------- ----------
    B          CREATE SESSION        NO             NO      NO
*/

使用SYS用户收回A用户的CREATE SESSION系统权限。
REVOKE CREATE SESSION FROM a;

检查A用户和B用户现在的系统权限：

SQL> SELECT GRANTEE , PRIVILEGE , ADMIN_OPTION , COMMON , INHERITED FROM dba_sys_privs WHERE GRANTEE = 'A';

no rows selected

SQL> SELECT GRANTEE , PRIVILEGE , ADMIN_OPTION , COMMON , INHERITED FROM dba_sys_privs WHERE GRANTEE = 'B';

GRANTEE     PRIVILEGE            ADMIN_OPTION    COMMON    INHERITED
-------- ------------------ --------------- ------- ----------
B          CREATE SESSION        NO             NO      NO

我们发现A用户的CREATE SESSION系统权限被收回了，但是B用户的CREATE SESSION系统权限没有被收回。

因此我们得出结论，被WITH ADMIN OPTION权限赋予的系统权限，在赋权用户的该系统权限被收回时，该系统权限并不被级联收回。

2. WITH GRANT OPTION实验：
DROP USER a;
DROP USER b;
CREATE USER a IDENTIFIED BY oracle ACCOUNT UNLOCK;
CREATE USER b IDENTIFIED BY oracle ACCOUNT UNLOCK;

首先使用sys用户赋予a用户和b用户CREATE SESSION系统权限：
GRANT CREATE SESSION TO a;
GRANT CREATE SESSION TO b;

先检查A用户和B用户的对象权限：
SET LINES 300
SET PAGES 20
COL GRANTEE FOR A20
COL OWNER FOR A20
COL TABLE_NAME FOR A20
COL PRIVILEGE FOR A20

SQL> SELECT GRANTEE,OWNER,TABLE_NAME,PRIVILEGE FROM user_tab_privs WHERE GRANTEE='A';

no rows selected

SQL> SELECT GRANTEE,OWNER,TABLE_NAME,PRIVILEGE FROM user_tab_privs WHERE GRANTEE='A';

no rows selected

切换到HR用户：
CONN hr/hr

使用HR用户把EMPLOYEES表的SELECT对象权限赋予给a用户，并且需要对a再另赋WITH GRANT OPTION权限：

GRANT SELECT ON hr.EMPLOYEES TO a WITH GRANT OPTION;

赋权结束后检查A用户权限：
/*
    SQL> DESC user_tab_privs;
    
     Name                         Null?      Type
     ----------------------------------------------- -------- --------------------------------
     GRANTEE                          VARCHAR2(128)
     OWNER                              VARCHAR2(128)
     TABLE_NAME                          VARCHAR2(128)
     GRANTOR                          VARCHAR2(128)
     PRIVILEGE                          VARCHAR2(40)
     GRANTABLE                          VARCHAR2(3)
     HIERARCHY                          VARCHAR2(3)
     COMMON                           VARCHAR2(3)
     TYPE                              VARCHAR2(24)
     INHERITED                          VARCHAR2(3)

*/

SET LINES 300;
SET PAGES 20;
COL GRANTEE FOR A20;
COL OWNER FOR A20;
COL TABLE_NAME FOR A20;
COL PRIVILEGE FOR A20;
SQL> 
SQL> 
SQL> SELECT GRANTEE,OWNER,TABLE_NAME,PRIVILEGE FROM user_tab_privs WHERE GRANTEE='A';

GRANTEE          OWNER          TABLE_NAME           PRIVILEGE
-------------------- -------------------- -------------------- --------------------
A             HR           EMPLOYEES           SELECT

切换到A用户下:

CONN a/oracle

SET LINES 300;
SET PAGES 20;
COL GRANTEE FOR A20;
COL OWNER FOR A20;
COL TABLE_NAME FOR A20;
SQL> COL PRIVILEGE FOR A20;
SQL> 
SQL> SELECT GRANTEE,OWNER,TABLE_NAME,PRIVILEGE FROM user_tab_privs WHERE GRANTEE='A';

GRANTEE          OWNER          TABLE_NAME           PRIVILEGE
-------------------- -------------------- -------------------- --------------------
A             HR           EMPLOYEES           SELECT

把EMPLOYEES表的SELECT对象权限赋予给b用户:

/*
    这里需要注意的是由于目前用户不是HR用户
    对a用户对b用户赋予SELECT ON EMPLOYEES对象权限时，要特别指定表的归属用户HR。
*/
GRANT SELECT ON HR.EMPLOYEES TO b; 

检查B用户的对象权限:

SQL> SELECT GRANTEE,OWNER,TABLE_NAME,PRIVILEGE FROM user_tab_privs WHERE GRANTEE = 'B';

GRANTEE          OWNER          TABLE_NAME           PRIVILEGE
-------------------- -------------------- -------------------- --------------------
B             HR           EMPLOYEES           SELECT

切换回HR用户：
SQL> CONN hr/hr
Connected.
SQL> 

SQL> 
SQL> REVOKE SELECT ON hr.employees FROM a;

Revoke succeeded.

检查A用户和B用户的对象权限：
SQL> 
SQL> SELECT GRANTEE,OWNER,TABLE_NAME,PRIVILEGE FROM user_tab_privs WHERE GRANTEE = 'A';

no rows selected

SQL> SELECT GRANTEE,OWNER,TABLE_NAME,PRIVILEGE FROM user_tab_privs WHERE GRANTEE = 'B';

no rows selected

SQL> 

我们发现A用户和B用户的SELECT ON EMPLOYEES对象权限已都被收回了。

因此我们得出结论，被WITH GRANT OPTION权限赋予的对象权限，在赋权用户的该对象权限被收回时，该对象权限也一并被级联收回。