关于WITH ADMIN OPTION和WITH GRANT OPTION

--创建两个用户赋予密码确保用户为非锁定状态，并查看用户的系统权限
CREATE USER a IDENTIFIED BY oracle ACCOUNT UNLOCK;
CREATE USER b IDENTIFIED BY oracle ACCOUNT UNLOCK;
--查看用户权限
SYS@10.10.10.1:1521/ORCLPDB>select GRANTEE,PRIVILEGE from dba_sys_privs where GRANTEE='A';
no rows selected
SYS@10.10.10.1:1521/ORCLPDB>select GRANTEE,PRIVILEGE from dba_sys_privs where GRANTEE='B';
no rows selected
--新创建的用户无任何权限

--将创建会话权限授予A用户
SYS@10.10.10.1:1521/ORCLPDB>GRANT CREATE SESSION TO a WITH ADMIN OPTION;
Grant succeeded.
SYS@10.10.10.1:1521/ORCLPDB>select GRANTEE,PRIVILEGE from dba_sys_privs where GRANTEE='A';
G PRIVILEGE
- ----------------------------------------
A CREATE SESSION

--连上A用户将CREATE SESSION权限授予B用户
SYS@10.10.10.1:1521/ORCLPDB>conn a/oracle@10.10.10.1:1521/ORCLPDB
Connected.
A@10.10.10.1:1521/ORCLPDB>GRANT CREATE SESSION TO b;
Grant succeeded.

--此时查看A和B用户的权限（在具有DBA权限用户下查询，我这里是sys用户）
SYS@10.10.10.1:1521/ORCLPDB>select GRANTEE,PRIVILEGE from dba_sys_privs where GRANTEE in ('A','B');
GRANT PRIVILEGE
----- ----------------------------------------
A     CREATE SESSION
B     CREATE SESSION

--此时A和B都具有创建会话的权限了，测试能不能登录
SYS@10.10.10.1:1521/ORCLPDB>conn a/oracle@10.10.10.1:1521/ORCLPDB
Connected.
A@10.10.10.1:1521/ORCLPDB>show user
USER is "A"
A@10.10.10.1:1521/ORCLPDB>conn b/oracle@10.10.10.1:1521/ORCLPDB
Connected.
B@10.10.10.1:1521/ORCLPDB>show user
USER is "B"

--A和B用户都能正常登录，此时将A权限收回
SYS@10.10.10.1:1521/ORCLPDB>REVOKE CREATE SESSION FROM A;
Revoke succeeded.
SYS@10.10.10.1:1521/ORCLPDB>select GRANTEE,PRIVILEGE from dba_sys_privs where GRANTEE in ('A','B');
GRANT PRIVILEGE
----- ----------------------------------------
B     CREATE SESSION

--此时A的权限被撤销了，B的权限还在。测试一下A，B的登录情况
SYS@192.168.62.1:1521/ORCLPDB>conn a/oracle@10.10.10.1:1521/ORCLPDB
ERROR:
ORA-01045: user A lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
@>conn b/oracle@10.10.10.1:1521/ORCLPDB
Connected.
B@10.10.10.1:1521/ORCLPDB>

--授予A用户CREATE SESSION
SYS@10.10.10.1:1521/ORCLPDB>GRANT CREATE SESSION TO a;
Grant succeeded.
SYS@10.10.10.1:1521/ORCLPDB>select GRANTEE,PRIVILEGE from dba_sys_privs where GRANTEE in ('A','B');
GRANT PRIVILEGE
----- ----------------------------------------
A     CREATE SESSION
B     CREATE SESSION

--登录HR用户，将EMPLOYEES表的SELECT权限授予A用户，另赋予WITH GRANT OPTION
B@10.10.10.1:1521/ORCLPDB>conn hr/hr@10.10.10.1:1521/ORCLPDB
Connected.
HR@10.10.10.1:1521/ORCLPDB>GRANT SELECT ON  EMPLOYEES TO a WITH GRANT OPTION;
Grant succeeded.
HR@192.168.62.1:1521/ORCLPDB>select GRANTOR,OWNER,TABLE_NAME,PRIVILEGE from user_tab_privs where GRANTOR ='A';

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

--登录到A用户将EMPLOYEES的SELECT权限授予B用户，并查询A，B的用户权限
A@10.10.10.1:1521/ORCLPDB>GRANT SELECT ON HR.EMPLOYEES TO b;
Grant succeeded.

--登录到B用户查询授权者为A的条目
A@10.10.10.1:1521/ORCLPDB>conn b/oracle@10.10.10.1:1521/ORCLPDB
Connected.
B@10.10.10.1:1521/ORCLPDB>select GRANTOR,OWNER,TABLE_NAME,PRIVILEGE from user_tab_privs where GRANTOR ='A';

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

--这里在B上看见一条授权者为A，对象为HR.EMPLOYEES的表的SELECT权限。测试一下权限没毛病。
B@10.10.10.1:1521/ORCLPDB>select * from hr.employees;

--登录HR用户收回HR.EMPLOYEES的SELECT权限
HR@10.10.10.1:1521/ORCLPDB>REVOKE SELECT ON EMPLOYEES FROM a;
Revoke succeeded.

--再检查A.B的权限
HR@10.10.10.1:1521/ORCLPDB>select GRANTOR,OWNER,TABLE_NAME,PRIVILEGE from user_tab_privs where GRANTOR ='A';
no rows selected
HR@10.10.10.1:1521/ORCLPDB>select GRANTOR,OWNER,TABLE_NAME,PRIVILEGE from user_tab_privs where GRANTOR ='B';
no rows