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.