WITH ADMIN OPTION与WITH GRANT OPTION区别

WITH ADMIN OPTION：面向系统权限，表示被授予权限的用户可以将该权限再授予其他用户，即可以实现系统权限的传递功能；权限回收无级联，祸不及子孙政策；

  来看下WITH ADMIN OPTION回收系统权限的无级联演示：

SYS连接：创建c##cy、c##nn用户，用with admin option授予create table权限给c##cy；c##cy连接：创建表tb_priv_test，授予create table权限给c##nn；c##nn连接：创建表tb_child_books；SYS连接：回收c##cy的create table权限；c##cy连接：创建表提示ORA-01031: insufficient privileges；c##nn连接：创建表成功。 SYS@primpdb 10:01:52> create user c##cy identified by cy123 default tablespace users temporary tablespace temp quota unlimited on users profile default; User created. SYS@primpdb 10:13:33> create user c##nn identified by nn123 default tablespace users temporary tablespace temp quota unlimited on users profile default; User created. SYS@primpdb 10:16:46> grant connect,create table to c##cy with admin option; Grant succeeded. SYS@primpdb 10:17:35> conn c##cy/cy123 Connected. Session altered. C##CY@primpdb 10:19:16> create table tb_priv_test (pid number(2) primary key,pname varchar2(50),ptype number(1)); Table created. C##CY@primpdb 10:22:47> grant connect,create table to c##nn; Grant succeeded. C##CY@primpdb 10:25:20> conn c##nn/nn123; Connected. Session altered. C##NN@primpdb 10:25:57> create table tb_child_books (bid number(4) primary key,bname varchar2(50),btype number(1)); Table created. C##NN@primpdb 10:26:01> conn / as sysdba Connected. Session altered. SYS@primpdb 10:26:22> revoke create table from c##cy; Revoke succeeded. SYS@primpdb 10:26:42> conn c##cy/cy123 Connected. Session altered. C##CY@primpdb 10:26:48> create table test (tid number(1),tname varchar2(10)); create table test (tid number(1),tname varchar2(10)) * ERROR at line 1: ORA-01031: insufficient privileges C##CY@primpdb 10:27:23> select table_name from user_tables; TABLE_NAME -------------------------------------------------------------------------------- TB_PRIV_TEST C##CY@primpdb 10:27:42> conn c##nn/nn123; Connected. Session altered. C##NN@primpdb 10:27:48> select table_name from user_tables; TABLE_NAME -------------------------------------------------------------------------------- TB_CHILD_BOOKS C##NN@primpdb 10:27:52> create table test (tid number(1),tname varchar2(10)); Table created. WITH GRANT OPTION：面向对象权限，表示允许对象权限的接受者可以将该对象权限授予其他用户，即可以实现对象权限的传递功能；权限回收级联，连坐政策。

  来看下WITH GRANT OPTION回收对象权限的级联演示：

c##kk连接：用with grant option授予select on tb_wzgame_users权限给c##cy；c##cy连接：查询c##kk.tb_wzgame_users成功，授予select on c##kk.tb_wzgame_users权限权限给c##nn；c##nn连接：查询c##kk.tb_wzgame_users成功；c##kk连接：回收c##cy的select on tb_wzgame_users权限；c##cy连接：查询c##kk.tb_wzgame_users提示ORA-00942: table or view does not exist；c##nn连接：查询c##kk.tb_wzgame_users提示ORA-00942: table or view does not exist。 C##CY@primpdb 10:40:34> conn c##kk/kk123; Connected. Session altered. C##KK@primpdb 10:40:57> grant select on tb_wzgame_users to c##cy with grant option; Grant succeeded. C##KK@primpdb 10:41:15> conn c##cy/cy123 Connected. Session altered. C##CY@primpdb 10:41:23> select * from c##kk.tb_wzgame_users; USERID USERNAME TEL ---------- -------------------------------------------------- ----------- 1 ChengYu 136****4501 C##CY@primpdb 10:41:26> grant select on c##kk.tb_wzgame_users to c##nn; Grant succeeded. C##CY@primpdb 10:42:07> conn c##nn/nn123 Connected. Session altered. C##NN@primpdb 10:42:16> select * from c##kk.tb_wzgame_users; USERID USERNAME TEL ---------- -------------------------------------------------- ----------- 1 ChengYu 136****4501 C##NN@primpdb 10:42:21> conn c##kk/kk123 Connected. Session altered. C##KK@primpdb 10:42:33> revoke select on tb_wzgame_users from c##cy; Revoke succeeded. C##KK@primpdb 10:42:52> conn c##cy/cy123 Connected. Session altered. C##CY@primpdb 10:43:00> select * from c##kk.tb_wzgame_users; select * from c##kk.tb_wzgame_users * ERROR at line 1: ORA-00942: table or view does not exist C##CY@primpdb 10:43:04> conn c##nn/nn123 Connected. Session altered. C##NN@primpdb 10:43:20> select * from c##kk.tb_wzgame_users; select * from c##kk.tb_wzgame_users * ERROR at line 1: ORA-00942: table or view does not exist C##NN@primpdb 10:43:23> conn c##kk/kk123 Connected. Session altered. C##KK@primpdb 10:43:32> select * from tb_wzgame_users; USERID USERNAME TEL ---------- -------------------------------------------------- ----------- 1 ChengYu 136****4501