oracle权限with admin option和with grant option的区别

jiawang

1. with admin option

with admin option : 被授予该权限的用户有权将某个权限(如create any table)授予其他用户或角色，取消是不级联的。

创建user1、user2用户：
SQL> CREATE USER user1 IDENTIFIED BY oracle;
User created.
SQL> CREATE USER user2 IDENTIFIED BY oracle;
User created.


授予user1用户create session权限
SQL> GRANT CREATE session TO user1 WITH ADMIN OPTION;  
Grant succeeded.

SQL> conn user1/oracle;
Connected.

SQL> GRANT CREATE session TO user2 ;
Grant succeeded.

收回user1用户create session权限
[oracle@sztech ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 7 16:26:00 2021
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> revoke CREATE session  FROM USER1;
Revoke succeeded.

user1用户报错
SQL> conn user1/oracle;
ERROR:
ORA-01045: user USER1 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.


user2权限保留
SQL> conn user2/oracle
Connected.


但管理员可以显式回收user2的权限
SQL> revoke create session from user2;
Revoke succeeded.


SQL> conn user2/oracle;
ERROR:
ORA-01045: user USER2 lacks CREATE SESSION privilege; logon denied



2. with grant option

with grant option：权限赋予/取消是级联的，在上例中：若管理员收回user1用with grant option授权的权限时，user2权限会因级联而失效。