Impact of the resource role on the quota of the quota tablespace

Impact of the resource role on the quota of the quota tablespace

Two days ago, I saw a brother in the Forum saying that the user set the table space's quota, but the data volume that exceeds the quota can still be inserted into the tablespace.

I also think it is strange. What role does quota play?

Use experiments to describe:

1. Create a user

SQL> create user res_user identified by user_123
Default tablespace dcsopen_tbspace
Quota 500 K on dcsopen_tbspace;

Grant create session, create table to res_user;

Limit that the res_user user can only use KB of capacity in the dcsopen_tbspace tablespace.

2. Log On with the res_user account

SQL> select * from user_ts_quotas;

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

Check that the user's maximum available capacity is 516096 bytes, about 500 kb.

3. Test the available table space capacity

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

Create a table structure for testing.

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'

Insert data into the table with the error ORA-01536: space quota exceeded for tablespace 'dcsopen _ TBSPACE ', prompting that the current capacity used has exceeded the upper limit of the table space and thus the insertion is denied.

4. Grant the resource permission to the user

SQL> grant resource to res_user;

SQL> insert into t select * from all_objects;

No error is prompted.

SQL> select * from user_ts_quotas;

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

It is found that the capacity has already exceeded the MAX_BYTES value.

Query the roles owned by the user at this time:

SQL> select * from user_role_privs;

USERNAME GRANTED_ROLE adm def OS _
---------------------------------------------------------------------
RES_USER RESOURCE NO YES NO

Query the user's system permissions at this time:

SQL> select * from user_sys_privs;

USERNAME PRIVILEGE ADM

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

RES_USER CREATE SESSION NO

RES_USER UNLIMITED TABLESPACE NO

RES_USER CREATE TABLE NO

The user has the unlimited tablespace permission, that is, there is no limit to the table space.

To verify this, You can revoke resource from res_user and query user_sys_privs. It is found that the unlimited tablespace permission follows the RESOURCE role.

5. Do not grant resource permissions, but still grant users unlimited capacity permissions.

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 indicates no upper limit.

Conclusion:

1. You can specify the limit for using a tablespace. If the used capacity exceeds the limit, an error is returned. You can use the user_ts_quotas table to query the quota value.

2. If the resource role is granted to the user, the user automatically has the unlimited tablespace permission, that is, the quota is not controlled by the user.