A Problem with REVOKE

If you grant the DBA role to a user, Oracle also grants it the UNLIMITED TABLESPACE system privilege. If you then revoke the DBA role from this user, Oracle also revokes its UNLIMITED TABLESPACE system privilege. This isn’t too much of an issue.

 

However, if you grant the UNLIMITED TABLESPACE system privilege to a user by itself THEN grant it the DBA role, Oracle seems to have no idea where the UNLIMITED TABLESPACE system privilege came from. If you then revoke the DBA role from this user, Oracle still revokes the UNLIMITED TABLESPACE system privilege from it. This may not be what you intended to do. You can see what I mean in the example below, which I tested in an Oracle 11.2 database.

 

First I created a user:

 

SQL> create user a identified by b

  2  /

 

User created.

 

SQL>

 

I checked that it had no roles nor system privileges:

 

SQL> select granted_role from dba_role_privs

  2  where grantee = 'A'

  3  /

 

no rows selected

 

SQL> select privilege from dba_sys_privs

  2  where grantee = 'A'

  3  /

 

no rows selected

 

SQL>

 

I granted the DBA role to the user and checked that this also gave it the UNLIMITED TABLESPACE system privilege:

 

SQL> grant dba to a

  2  /

 

Grant succeeded.

 

SQL> select granted_role from dba_role_privs

  2  where grantee = 'A'

  3  /

 

GRANTED_ROLE

------------------------------

DBA

 

SQL> select privilege from dba_sys_privs

  2  where grantee = 'A'

  3  /

 

PRIVILEGE

----------------------------------------

UNLIMITED TABLESPACE

 

SQL>

 

I revoked the DBA role and checked that Oracle also revoked the UNLIMITED TABLESPACE system privilege:

 

SQL> revoke dba from a

  2  /

 

Revoke succeeded.

 

SQL> select granted_role from dba_role_privs

  2  where grantee = 'A'

  3  /

 

no rows selected

 

SQL> select privilege from dba_sys_privs

  2  where grantee = 'A'

  3  /

 

no rows selected

 

SQL>

 

I granted the UNLIMITED TABLESPACE system privilege to the user independently:

 

SQL> grant unlimited tablespace to a

  2  /

 

Grant succeeded.

 

SQL> select granted_role from dba_role_privs

  2  where grantee = 'A'

  3  /

 

no rows selected

 

SQL> select privilege from dba_sys_privs

  2  where grantee = 'A'

  3  /

 

PRIVILEGE

----------------------------------------

UNLIMITED TABLESPACE

 

SQL>

 

I granted the DBA role to the user:

 

SQL> grant dba to a

  2  /

 

Grant succeeded.

 

SQL> select granted_role from dba_role_privs

  2  where grantee = 'A'

  3  /

 

GRANTED_ROLE

------------------------------

DBA

 

SQL> select privilege from dba_sys_privs

  2  where grantee = 'A'

  3  /

 

PRIVILEGE

----------------------------------------

UNLIMITED TABLESPACE

 

SQL>

 

I revoked the DBA role from the user. Oracle revoked the UNLIMITED TABLESPACE system privilege at the same time despite the fact that I had granted it separately:

 

SQL> revoke dba from a

  2  /

 

Revoke succeeded.

 

SQL> select granted_role from dba_role_privs

  2  where grantee = 'A'

  3  /

 

no rows selected

 

SQL> select privilege from dba_sys_privs

  2  where grantee = 'A'

  3  /

no rows selected

SQL>