详解Oracle的unlimited tablespace系统权限

9u05mg0m29

1.系统权限unlimited tablespace是隐含在dba, resource角色中的一个系统权限. 当用户得到dba或resource的角色时,unlimited tablespace系统权限也隐式受权给用户.
2.系统权限unlimited tablespace不能被授予role,可以被授予用户.
3.系统权限unlimited tablespace不会随着resource, dba被授予role而授予给用户.
1实验1
SQL>create user u1 identified by u1;
User created.
SQL>grant connect, resource to u1;
Grant succeeded.
SQL>select * from dba_role_privs a where a.grantee='U1';
GRANTEEGRANTED_ROLEADM DEF
-------------------- -------------------- --- ---
U1RESOURCE NO YES
U1CONNECT NO YES
SQL>select * from dba_sys_privs a where a.grantee='U1';
GRANTEEPRIVILEGE ADM
-------------------- -------------------- ---
U1UNLIMITED TABLESPACE NO
SQL>revoke unlimited tablespace from u1;
Revoke succeeded.
SQL>select * from dba_sys_privs a where a.grantee='U1';
no rows selected
2实验2
SQL>create role r1;
Role created.
SQL>grant unlimited tablespace to r1;
ORA-01931: cannot grant UNLIMITED TABLESPACE to a role
不能受权给角色r1.

SQL>grant unlimited tablespace to u1;
Grant succeeded.
可以受权给用户u1.
3实验3
SQL>revoke resource from u1;
Revoke succeeded.
SQL> grant resource to r1;
Grant succeeded.
SQL>grant r1 to u1;
Grant succeeded.
SQL> select * from dba_role_privs a where a.grantee='U1';
GRANTEEGRANTED_ROLEADM DEF
-------------------- -------------------- --- ---
U1R1NOYES
U1CONNECT NO YES
SQL> select * from dba_sys_privs a where a.grantee='U1';
no rows selected
系统权限中没有unlimit tablespace系统权限.