Thursday, November 19, 2015

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>

3 comments:

Mustafa DOĞANAY said...

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

Andrew Reid said...

Dear Mustafa,

Thank you for your comment.
It's always good to get a link to a document on My Oracle Support as it lets readers get extra detail if they are interested.
I notice that you have a lot of worked examples on your blog so I will take a longer look when I get a moment.

Kind Regards,

Andrew

Mustafa DOĞANAY said...

thx..


Blogger Andrew Reid said...
I notice that you have a lot of worked examples on your blog so I will take a longer look when I get a moment.