詳解Oracle的unlimited tablespace系統許可權

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';

GRANTEE
             GRANTED_ROLE        
ADM DEF

-------------------- -------------------- --- ---

U1                  
RESOURCE             NO  YES

U1                  
CONNECT              NO  YES

SQL>
select * from dba_sys_privs a where a.grantee='U1';

GRANTEE
             PRIVILEGE            ADM

-------------------- -------------------- ---

U1                  
UNLIMITED 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';

GRANTEE
             GRANTED_ROLE        
ADM DEF

-------------------- -------------------- --- ---

U1                  
R1     
             NO 
YES

U1                  
CONNECT              NO  YES

SQL> select * from dba_sys_privs a where a.grantee='U1';

no rows selected

系統許可權中沒有unlimit tablespace系統許可權.