oracle 创建表空间 用户 授权_详解oracle数据库用户对表空间配额（quota）限制

概述

无意中用Scott创建测试表时发现在某个表空间没权限，查了下发现是quota方面的问题，所以顺便总结下这方面内容。

---

来源

在某个用户scott测试时发现在某个表空间没有权限

SQL> create table A as select OBJECT_NAME,OBJECT_ID from user_objects;create table A as select OBJECT_NAME,OBJECT_ID from user_objects *ERROR at line 1:ORA-01950: no privileges on tablespace 'USERS'

解除限制

SQL> set line 1000SQL> col tablespace_name for a30SQL> col username for a30SQL> select tablespace_name,username,max_bytes from DBA_TS_QUOTAS where username='SCOTT';SQL> grant unlimited tablespace to scott;

测试是否解决

可以创建表，问题解决

下面顺便总结下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.

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.

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 hwb IDENTIFIED BY password DEFAULT TABLESPACE data_ts QUOTA 100M ON test_ts QUOTA 500K ON data_ts TEMPORARY TABLESPACE temp_ts PROFILE hwb;

说明：在创建用户的时候，就指定用户在特定表空间上的配额，配额的指定可以禁止用户的对象使用过多的表空间

2、 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.

3、 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;

---

Quota 说明

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

CREATE USER SYSTEM IDENTIFIED BY  DEFAULT TABLESPACE SYSTEM TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; -- 2 Roles for SYSTEM GRANT AQ_ADMINISTRATOR_ROLE TO SYSTEM WITH ADMIN OPTION; GRANT DBA TO SYSTEM WITH ADMIN OPTION; ALTER USER SYSTEM DEFAULT ROLE ALL; -- 5 System Privileges for SYSTEM GRANT GLOBAL QUERY REWRITE TO SYSTEM; GRANT CREATE MATERIALIZED VIEW TO SYSTEM; GRANT CREATE TABLE TO SYSTEM; GRANT UNLIMITED TABLESPACE TO SYSTEM WITH ADMIN OPTION; GRANT SELECT ANY TABLE TO SYSTEM;  CREATE USER HWB IDENTIFIED BY  DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; -- 2 Roles for DAVE GRANT CONNECT TO HWB; GRANT RESOURCE TO HWB; ALTER USER HWB DEFAULT ROLE ALL; -- 1 System Privilege for HWB GRANT UNLIMITED TABLESPACE TO HWB;

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

---

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

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

SQL> conn / as sysdba;.SQL> create user hwb identified by hwb default tablespace users temporary tablespace temp quota 10M on users;

2、查询用户配额的信息：

select tablespace_name,username,max_bytes/1024/1024 MB from DBA_TS_QUOTAS where username='HWB';

3、更改用户的表空间限额：

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

 grant unlimited tablespace to HWB;

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

3.2、具体表空间限制

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

alter user HWB quota unlimited on users;

4、查看配额：

select tablespace_name,username,max_bytes from DBA_TS_QUOTAS where username='HWB';

说明：max_bytes 为-1，即不受限制。

5、 回收用户对表空间的配额:

5.1、全局：

前提是system privileges 授权了。

SQL> revoke unlimited tablespace from HWB;

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

select tablespace_name,username,max_bytes from DBA_TS_QUOTAS where username='HWB';

5.2、针对某个特定的表空间：

alter user HWB quota 0 on users;select tablespace_name,username,max_bytes from DBA_TS_QUOTAS where username='HWB';

---

如果某个表空间有多个用户的话，又不想用户都可以用表空间所有空间，大家可以考虑在quota上做限制。后面会分享更多DBA方面内容，感兴趣的朋友可以关注下！