关于unlimited tablespace系统权限

1. 系统权限unlimited tablespace是隐含(没有显式包括)在dba, resource角色中的一个系统权限；当用户得到dba或resource的角色时, unlimited tablespace系统权限也隐式受权给用户。


sys@JIAGULUN> create user t1 identified by t1;

User created.


sys@JIAGULUN> grant create session,create table to t1;

Grant succeeded.


sys@JIAGULUN> conn t1/t1;

Connected.

t1@JIAGULUN> create table t(id int);
create table t(id int)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'


t1@JIAGULUN> conn / as sysdba
Connected.


sys@JIAGULUN> grant resource to t1;

Grant succeeded.


sys@JIAGULUN> conn t1/t1
Connected.

t1@JIAGULUN> create table t(id int);

Table created.


t1@JIAGULUN> conn / as sysdba  
Connected.


sys@JIAGULUN> select * from dba_sys_privs where grantee='T1';


GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
T1                             CREATE TABLE                             NO
T1                             UNLIMITED TABLESPACE                     NO
T1                             CREATE SESSION                           NO


sys@JIAGULUN> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='RESOURCE';


GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO


2. 系统权限unlimited tablespace不能被授予role, 可以被授予用户.

sys@JIAGULUN> revoke unlimited tablespace from t1;

Revoke succeeded.


sys@JIAGULUN> select * from dba_sys_privs where grantee='T1';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
T1                             CREATE TABLE                             NO
T1                             CREATE SESSION   


sys@JIAGULUN>  CREATE ROLE r1;

Role created.


sys@JIAGULUN> grant unlimited tablespace to r1;
grant unlimited tablespace to r1
*
ERROR at line 1:
ORA-01931: cannot grant UNLIMITED TABLESPACE to a role


sys@JIAGULUN> grant unlimited tablespace to t1;

Grant succeeded.


3. 系统权限unlimited tablespace不会随着resource, dba被授予role而授予给用户.


sys@JIAGULUN> select * from dba_role_privs where grantee='T1';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
T1                             RESOURCE                       NO  YES


sys@JIAGULUN> revoke resource from t1;

Revoke succeeded.


sys@JIAGULUN> grant resource to r1;

Grant succeeded.


sys@JIAGULUN> select * from dba_role_privs where grantee='T1';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
T1                             R1                             NO  YES



sys@JIAGULUN> select * from dba_sys_privs where grantee='T1';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
T1                             CREATE TABLE                             NO
T1                             CREATE SESSION                           NO


sys@JIAGULUN> conn t1/t1
Connected.


t1@JIAGULUN> drop table t;

Table dropped.


t1@JIAGULUN> create table t(id int);
create table t(id int)
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'


t1@JIAGULUN> conn / as sysdba
Connected.

sys@JIAGULUN> grant unlimited tablespace to t1;

Grant succeeded.

sys@JIAGULUN> conn t1/t1
Connected.

t1@JIAGULUN> create table t(id int);

Table created.