Oracle的表空间quota详解(二)

Restricting the Quota Limits for User Objects in a Tablespace

You can restrict the quota limits for user objects in a tablespace by using the ALTER USER SQL statement to change the current quota of the user to zero.

After a quota of zero is assigned, the objects of the user in the tablespace remain, and the user can still create new objects, but the existing objects will not be allocated any new space.

For example, you could not insert data into one of this user's exiting tables. The operation will fail with an ORA-1536 space quota exceeded for tables error.

Granting Users the UNLIMITED TABLESPACE System Privilege

To permit a user to use an unlimited amount of any tablespace in the database, grant the user the UNLIMITED TABLESPACE system privilege. This overrides all explicit tablespace quotas for the user. If you later revoke the privilege, then you must explicitly grant quotas to individual tablespaces. You can grant this privilege only to users, not to roles.

Before granting the UNLIMITED TABLESPACE system privilege, you must consider the consequences of doing so.

Advantage:

You can grant a user unlimited access to all tablespaces of a database with one statement.

Disadvantages:

（1）The privilege overrides all explicit tablespace quotas for the user.

（2）You cannot selectively revoke tablespace access from a user with the UNLIMITED TABLESPACE privilege. You can grant selective or restricted access only after revoking the privilege.

Listing All Tablespace Quotas

Use the DBA_TS_QUOTAS view to list all tablespace quotas specifically assigned to each user. For example:

SELECT * FROM DBA_TS_QUOTAS;

TABLESPACE USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS

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

USERS JFEE 0 512000 0 250

USERS DCRANNEY 0 -1 0 -1

When specific quotas are assigned, the exact number is indicated in the MAX_BYTES column . This number is always a multiple of the database block size, so if you specify a tablespace quota that is not a multiple of the database block size, then it is rounded up accordingly. Unlimited quotas are indicated by -1.

注意当对用户赋予resource角色时将同时赋予unlimited tablespace的 系统权限。详情见下文

创建用户

SQL> create user test_privs identified by test_privs default tablespace users;

User created.

SQL> select * from dba_sys_privs where GRANTEE='TEST_PRIVS';

no rows selected

赋予resource角色

SQL> grant resource to TEST_PRIVS;

Grant succeeded.

查询resource角色所具有的系统权限

SQL> 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

查看用户所具有的角色

SQL> select * from dba_role_privs where GRANTEE='TEST_PRIVS';

GRANTEE GRANTED_ROLE ADM DEF

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

TEST_PRIVS RESOURCE NO YES

查询用户所具有的系统权限

SQL> select * from dba_sys_privs where GRANTEE='TEST_PRIVS';

GRANT