0 Resource Role has “Unlimited Tablespace”

Resource Role has the privilege to for “Unlimited tablespace” including the SYSAUX. It is the well known bug. Here is the demo and testing.

 

Create the 2 tablespaces POTS2 and POTS3. Create the user PO4 and only grant the permission to POTS2.

create tablespace pots2 datafile '/home/oracle/app/oracle/oradata/podb/controlfile/pots02.dbf' size 10M;

create tablespace pots3 datafile '/home/oracle/app/oracle/oradata/podb/controlfile/pots03.dbf' size 10M;

create user PO4

identified by oracle

default tablespace POTS2

temporary tablespace tempts

quota 10M ON POTS2

profile default;

grant create session,create table to PO4;

Login as PO4 and check the current privileges. Only CREATE SESSION and CREATE TABLE.

show user;

select * from session_roles;

select * from session_privs;

create table t1( a number)  TABLESPACE POTS2 ;

insert into t1 values( 1);

We can use below query to confirm the table T1 is created under POTS2.

col owner format a20

col table_name format a20

col tablespace_name format a20

select owner, table_name, tablespace_name from dba_tables where table_name='T1' and owner='PO4';

Lets try to create the table on the tablespace POTS3. The table would get created because it is only updated the data dictionary. We would get the ORA-01950 error when insert the data, because the PO4 does not have any privilege on the tablespace POTS3;

Let’s grant PO4 RESOURCE role.

grant resource to PO4;

select * from role_sys_privs where role = 'RESOURCE';

The resource role contains below 8 privileges.

• CREATE SEQUENCE
• CREATE TRIGGER
• CREATE CLUSTER
• CREATE PROCEDURE
• CREATE TYPE
• CREATE OPERATOR
• CREATE TABLE
• CREATE INDEXTYPE

However, the DBA_SYS_PRIVS shows the PO4 has the “UNLIMITED TABLESPACE” privilege

select * from dba_sys_privs where grantee='PO4';

.

show user;

select * from session_roles;

select * from session_privs;

create table t3( a number)  TABLESPACE POTS3 ; 

insert into t3 values( 1); 

create table t3( a number)  TABLESPACE SYSAUX ; 

insert into t3 values( 1); 

PO4 user now can create the table on any tablespace even the SYSAUX. This is not the good things …

 

To fix this, we can either :

Revoke the “UNLIMITED tablespace”

revoke unlimited tablespace from PO4;

Now the PO4 can not insert data to other tablespace.

As alternative, create the new role with all 8 privileges and not includes the “UNLIMITETED TABLESPACE”

• CREATE SEQUENCE
• CREATE TRIGGER
• CREATE CLUSTER
• CREATE PROCEDURE
• CREATE TYPE
• CREATE OPERATOR
• CREATE TABLE
• CREATE INDEXTYPE

create role resource2;

grant CREATE SEQUENCE to resource2;

grant CREATE TRIGGER to resource2;

grant CREATE CLUSTER to resource2;

grant CREATE PROCEDURE to resource2;

grant CREATE TYPE to resource2;

grant CREATE OPERATOR to resource2;

grant CREATE TABLE to resource2;

grant CREATE INDEXTYPE  to resource2;

 

Here is the demo to create the new role “RESOURCE2” and only includes the 8 privileges to the role.