Tuesday, December 18, 2012

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;

image

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);

image

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';

image

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;

image

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

image

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

select * from dba_sys_privs where grantee='PO4';
.

image

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); 

image

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

image

 

To fix this, we can either :

Revoke the “UNLIMITED tablespace”
revoke unlimited tablespace from PO4;

image

Now the PO4 can not insert data to other tablespace.

image

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.

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates