resource角色对quota表空间限额的影响

前两天，看到论坛中有位兄弟说设置了用户对表空间的quota限额，但仍可以插入超过限额大小的数据量到表空间。

也是觉得很奇怪，那quota起什么作用？

用实验来说明：

1. 创建用户

SQL> create user res_user identified by user_123
         default tablespace dcsopen_tbspace
         quota 500K on dcsopen_tbspace;

grant create session, create table to res_user;

限定res_user用户在dcsopen_tbspace表空间中只能使用500K的容量。

2. 使用res_user账户登录

SQL> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
DCSOPEN_TBSPACE                         0     516096          0         63 NO

查看该用户可用最大容量为516096字节，大约500K。

3. 测试表空间可用容量

SQL> create table t as select * from all_objects where 1<>1;

创建一张表结构，用于测试。

SQL> insert into t select * from all_objects;
insert into t select * from all_objects
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'DCSOPEN_TBSPACE'

向其中插入数据，报错ORA-01536: space quota exceeded for tablespace 'DCSOPEN_TBSPACE'，提示用户当前使用容量已超过对表空间的限额值因此拒绝执行插入。

4. 为用户授予resource权限

SQL> grant resource to res_user;

SQL> insert into t select * from all_objects;

未提示错误。

SQL> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
DCSOPEN_TBSPACE                   5242880     516096        640         63 NO

发现容量早已超过MAX_BYTES值。

查询此时用户拥有的角色：

SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
RES_USER                       RESOURCE                             NO  YES NO

查询此时用户的系统权限：

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                   ADM

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

RES_USER                       CREATE SESSION                           NO

RES_USER                       UNLIMITED TABLESPACE               NO

RES_USER                       CREATE TABLE                               NO

用户具有了UNLIMITED TABLESPACE的权限，即对表空间没有限额。

为了验证这点，可以revoke resource from res_user，再查询user_sys_privs，发现确实UNLIMITED TABLESPACE权限是跟随RESOURCE角色的。

5. 可以不授予resource，但仍让用户具有无限容量权限

SQL> alter user res_user quota unlimited on dcsopen_tbspace;

SQL> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
DCSOPEN_TBSPACE                   5242880         -1        640         -1 NO

-1表示无上限。

结论：

1. 可以为用户指定使用某个表空间的限额，当使用容量超过限额，会提示错误。限额值可以用user_ts_quotas表查询。

2. 若为用户授予resource角色，则用户自动具有UNLIMITED TABLESPACE权限，即使用quota限额，也不受其控制。