ADMIN OPTION 的系统权限
![此处输入图片的描述](http://wangbinbin0326.github.io/images/database/oracle/user-revoke-admin.JPG)
方案
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 系统权限。