Applies to:

Oracle Server - Enterprise Edition
Information in this document applies to any platform.
Checked for relevance on 30-Dec-2011

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

Whenever an user is granted the RESOURCE and DBA roles these will be reflected in the DBA_ROLE_PRIVS view. Additionally a new privilege will be added to DBA_SYS_PRIVS. This additional privilege is UNLIMITED TABLESPACE .

Whenever one of the above two roles is revoked the UNLIMITED TABLESPACE privilege is automatically revoked as well. This is the expected behaviour.

Solution

The problem can be solved by either granting the RESOURCE role once again, granting quota on specific tablespaces this user has to work with or by granting directly the UNLIMITED TABLESPACE privilege.

SQL> grant RESOURCE to ABC;

SQL> alter user ABC quota unlimited on TOOLS;

SQL> grant UNLIMITED TABLESPACE to ABC;