WITH ADMIN OPTION与WITH GRANT OPTION区别

最新推荐文章于 2022-07-27 20:42:19 发布

山与先生

最新推荐文章于 2022-07-27 20:42:19 发布

阅读量1k

点赞数

分类专栏： ORACLE 文章标签： Oracle Oracle权限

本文链接：https://blog.csdn.net/u010257584/article/details/108377108

版权

ORACLE 专栏收录该内容

35 篇文章 2 订阅

订阅专栏

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