resource角色對quota資料表空間限額的影響

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限額，也不受其控制。