给用户其它表空间操作权限（用户对表空间配额quota）

一 官网的说明

• Oracle 官网对quota的定义如下：
  A limit on a resource, such as a limit on the amount of database storage used by a database user. A database administrator can set tablespace quotas for each Oracle Database username.
  quota是对资源的限制，例如对数据库用户使用的数据库存储量的限制。数据库管理员可以为每个Oracle数据库用户名设置表空间配额。

• 有关Oracle Quota 这块，只在Oracle 的安全管理这块搜到了一些内容。
  Managing Security for Oracle Database Users

1.1 Assigning a Tablespace Quota for the User

You can assign each user a tablespace quota for any tablespace (except a temporary tablespace). Assigning a quota accomplishes the following: 可以为每个用户分配任何表空间的表空间配额(临时表空间除外)。

（1）Users with privileges to create certain types of objects can create those objects in the specified tablespace.
具有创建特定类型对象权限的用户可以在指定的表空间中创建这些对象。

（2）Oracle Database limits the amount of space that can be allocated for storage of a user’s objects within the specified tablespace to the amount of the quota.
oracle会 限制分配给用户对象的存储空间大小，即可分配给该用户在指定表空间配额量。

By default, a user has no quota on any tablespace in the database. If the user has the privilege to create a schema object, then you must assign a quota to allow the user to create objects. At a minimum, assign users a quota for the default tablespace, and additional quotas for other tablespaces in which they can create objects.
默认情况下，用户在数据库中的任何表空间上都没有配额。 如果用户拥有创建架构对象的权限，则必须分配配额以允许该用户创建对象。至少要为用户分配默认表空间的配额，还可以为他们可以创建对象的其他表空间分配额外配额。

The following CREATE USER statement assigns the following quotas for the test_ts and data_ts tablespaces:

CREATE USER acc_01
IDENTIFIED BY password
DEFAULT TABLESPACE data_ts
QUOTA 100M ON test_ts
QUOTA 500K ON data_ts
TEMPORARY TABLESPACE temp_ts
PROFILE clerk;

在创建用户的时候，就指定用户在特定表空间上的配额

You can assign a user either individual quotas for a specific amount of disk space in each tablespace or an unlimited amount of disk space in all tablespaces. Specific quotas prevent a user’s objects from using too much space in the database. 可以为一个用户在每个表空间中都单独分配特定数量的配额，也可以让该用户在所有表空间中分配无限数量的空间。配额的指定可以禁止用户的对象使用过多的表空间

You can assign quotas to a user tablespace when you create the user, or add or change quotas later. (You can find existing user quotas by querying the USER_TS_QUOTAS view.) 。 可以在创建用户时就将配额分配给用户表空间，也可以在以后添加或更改配额。 可以通过查询USER_TS_TORITS视图找到现有的用户配额。

If a new quota is less than the old one, then the following conditions remain true:如果新配额比原有配额少，那么需要满足一下条件
（1）If a user has already exceeded a new tablespace quota, then the objects of a user in the tablespace cannot be allocated more space until the combined space of these objects is less than the new quota.
如果这个用户已经超过了新配额，则在这个用户的对象的合并空间小于新配额之前，不能为其分配更多的空间。

（2）If a user has not exceeded a new tablespace quota, or if the space used by the objects of the user in the tablespace falls under a new tablespace quota, then the user’s objects can be allocated space up to the new quota.
如果这个用户没有超过新配额，或者如果用户的对象在表空间中使用的空间属于新的表空间配额，则可以为用户的对象分配最多到新配额的空间。

1.2 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.
可以将用户当前配额更改为零，从而限制表空间中用户对象的配额。在分配了零的配额之后，表空间中该用户对象保持不变，并且该用户仍然可以创建新的对象，但是现有的对象将不会被分配任何新的空间。例如，无法在这个用户的表中插入数据。会报Ora-1536错：超出了空间配额。

1.3 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.
若要允许用户在数据库中对任何表空间都有无限配额，这将覆盖该用户的所有显式表空间配额。如果稍后取消无限配额，则必须显式地将配额授予各个表空间。unlimited只能授权给用户，而不能授予角色。

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.
(1)无限配额会覆盖用户的所有显式表空间配额。
(2)不能有选择地撤销具有无限配额的用户对表空间的访问。只有在撤消无限配额后，才能授予选择性或受限的访问权限。

1.4 Listing All Tablespace Quotas

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

SQL> SELECT * FROM DBA_TS_QUOTAS;

TABLESPACE USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS
---------- --------- -------- ---------- ------- ----------
USERS ACC02 0 512000 0 250
USERS ACC030 -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.
MAX_BITES列是配额的数量。这个值是数据库块大小的倍数，如果指定的配额不是数据库块大小的倍数，会对这个值进行舍入。无限配额由-1表示。

二 Quota 说明

配额大小指的是用户指定使用表空间的的大小。在1.1 节里提到，默认情况下，用户对所有表空间都是没有配额的，即不受空间的限制。 查看几个用户的创建脚本来验证一下：

SQL> create user acc01 
identified by acc 
default tablespace users
temporary tablespace temp 
quota unlimites on tablespace;

SQL> create user acc02 
identified by acc 
default tablespace users
temporary tablespace temp;

SQL> grant unlimited tablespace to acc02;

从这2个脚本来看，默认情况下，都会对用户赋 unlimited tablespace 的权限。这是是在创建的时候指定的，当我们的用户创建好之后，我们也可以修改用户的配额。

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

• 创建用户时，指定限额

SQL> conn / as sysdba;
Connected.
SQL> create user acc03 identified by acc 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='ACC03';
 
TABLESPACE_NAME      USERNAME    MAX_BYTES
------------------------------ ---------- ----------
USERS                    ACC03       104857602

• 更改用户的表空间限额：

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

TABLESPACE_NAME            USERNAME         MAX_BYTES
---------------------- --------------------- ----------
TESTCLOB               TEST					-1
TESTDATAS              TEST					-1
TESTINDEXS             TEST					-1

一个用户的默认表空间只能有一个，但是可以为其授权在别的表空间中创建对像：

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

SQL> grant unlimited on tablespace to scott;

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

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

SQL> alter user TEST quota unlimited on MEETING;

用户已更改。

• 查看配额：

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

TABLESPACE_NAME            USERNAME                MAX_BYTES
------------------------------ ------------------------------ ----------
TESTCLOB               TEST					-1
TESTDATAS              TEST					-1
TESTINDEXS             TEST					-1
MEETING                TEST					-1

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

回收用户对表空间的配额,同样两种方式，

• 全局：

SQL> revoke unlimited tablespace from test01;
Revoke succeeded.

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

SQL> select tablespace_name,username,max_bytes from  DBA_TS_QUOTAS where username='TEST01';
no rows selected

针对某个特定的表空间：

SQL> alter user test quota 0 on meeting;
User altered.

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

TABLESPACE_NAME            USERNAME                MAX_BYTES
------------------------------ ------------------------------ ----------
TESTCLOB               TEST					-1
TESTDATAS              TEST					-1
TESTINDEXS             TEST					-1