Oracle 实验 --- 授予 ADMIN OPTION 的系统权限

2019独角兽企业重金招聘Python工程师标准>>>

ADMIN OPTION 的系统权限

方案
1. DBA 将CREATE TABLE 系统权限连同ADMIN OPTION 授予给Jeff。
2. Jeff 创建一个表。
3. Jeff 将CREATE TABLE 系统权限授予Emi。
4. Emi 创建一个表。
5. DBA 撤销Jeff 的CREATE TABLE 系统权限。

###################
1. 创建用户 jeff 和Emi
create user jeff
identified by pass
default tablespace tbs_02
temporary tablespace temp_demo
profile default
account unlock;
grant create session to jeff;

create user Emi
identified by pass
default tablespace tbs_02
temporary tablespace temp_demo
profile default
account unlock;
grant create session to emi;

2. 查看用户的系统权限
#check 系统权限
SQL> select * from dba_sys_privs where grantee = 'JEFF';

GRANTEE    PRIVILEGE                      ADM
---------- ------------------------------ ---
JEFF       CREATE SESSION                 NO


#check 角色权限
SQL> select * from dba_role_privs where grantee = 'JEFF';

GRANTEE    GRANTED_ROLE                   ADM DEF
---------- ------------------------------ --- ---
JEFF       CONNECT                        NO  YES
JEFF       RESOURCE                       NO  YES


3. 创建test01表，
SQL> create table test01(id number(2), name varchar(10));
create table test01(id number(2), name varchar(10))
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> grant create any table to jeff with admin option;

Grant succeeded.

SQL> select * from dba_sys_privs where grantee= 'JEFF';

GRANTEE    PRIVILEGE                      ADM
---------- ------------------------------ ---
JEFF       CREATE SESSION                 NO
JEFF       CREATE ANY TABLE               YES

SQL> create table test01(id number(2), name varchar(10));

Table created.

4. 用jeff赋予emi用户创建表的权限

SQL> grant create any table to emi;

Grant succeeded.

SQL> select * from dba_sys_privs where grantee= 'EMI';

GRANTEE    PRIVILEGE                      ADM
---------- ------------------------------ ---
EMI        CREATE ANY TABLE               NO
EMI        CREATE SESSION                 NO

SQL> create table test01(id number(2), name varchar(10));

Table created.

5. 收回jeff创建表的系统权限
SQL> revoke create any table from jeff;

Revoke succeeded.

SQL> select * from dba_sys_privs where grantee= 'JEFF';

GRANTEE    PRIVILEGE                      ADM
---------- ------------------------------ ---
JEFF       CREATE SESSION                 NO

SQL> select * from dba_sys_privs where grantee= 'EMI';

GRANTEE    PRIVILEGE                      ADM
---------- ------------------------------ ---
EMI        CREATE ANY TABLE               NO
EMI        CREATE SESSION                 NO

结果
Jeff 的表仍然存在，但Jeff 无法创建新表。
Emi 的表仍然存在，而且她仍然具有CREATE TABLE 系统权限。

转载于:https://my.oschina.net/wangbinbin0326/blog/469861