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系统权限.

有关用户的配额的操作说明

1. 创建用户时，指定限额 


SQL> conn / as sysdba;

Connected.

SQL> create user anqing identified by anqing default tablespace users temporary tablespace temp quota 10M on users;

User created.



查询用户配额的信息：

SQL> select tablespace_name,username,max_bytes from DBA_TS_QUOTAS where username='ANQING';



TABLESPACE_NAME USERNAME MAX_BYTES

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

USERS ANQING 10485760 



2.更改用户的表空间限额： 



不对用户做表空间限额控制: 



SQL> grant unlimited tablespace to anqing;

Grant succeeded.



这种方式是全局性的. 即修改用户多所有表空间的配额。



如果我们想改某个具体的,即针对用户的某个特定的表空间，可以使用如下SQL：



SQL> alter user anqing quota unlimited on users;

User altered.



查看配额：

SQL> select tablespace_name,username,max_bytes from DBA_TS_QUOTAS where username='ANQING';



TABLESPACE_NAME USERNAME MAX_BYTES

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

USERS ANQING -1


这时候max_bytes 为-1，即不受限制。



3. 回收用户对表空间的配额: 

同样两种方式，


全局：

SQL> revoke unlimited tablespace from anqing;

Revoke succeeded.


在查看配额，已经没有了相关信息：

SQL> select tablespace_name,username,max_bytes from DBA_TS_QUOTAS where username='ANQING';

no rows selected


针对某个特定的表空间：

SQL> alter user anqing quota 0 on users;

User altered.