Revoking DBA or RESOURCE Roles Revokes UNLIMITED TABLESPACE from the User (Doc ID 1084014.6)

Last updated on DECEMBER 12, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.0 to 11.2.0.3 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.
Checked for relevance on 31-May-2013


Symptoms

Revoking either RESOURCE or DBA roles revokes the UNLIMITED TABLESPACE privilege.


SQL> connect system/manager ---> this will be referred to as Session 1
Connected.

SQL> create user abc identified by abc;
Statement processed.

SQL> grant connect, resource to abc;
Statement processed.

SQL> connect abc/abc ---> this will be referred to as Session 2

SQL> create table test1 (c1 number);
Table created.



From another session connected as SYSTEM:

Session 1(SYSTEM):


SQL> grant dba to abc;
Statement processed.

SQL> revoke dba from abc;
Statement processed.

Session 2(ABC):


SQL> create table test2(c1 number);
create table test2(c1 number)
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'SYSTEM'





When the dba privilege is revoked, the quota resources from any tablespace are lost although they were acquired earlier via another role(RESOURCE).

Re granting the RESOURCE role to the user, will allow the user to allocate space in the tablespaces.


Session 1(SYSTEM):

SQL> grant resource to abc;
Statement processed.

Session 2(ABC):

SQL> create table test2(c1 number);
Table created.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms