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.
0 comments:
Post a Comment