Magic in UNLIMITED TABLESPACE privilege

Some time ago I had a task to revoke DBA role from non-human application related user in DB. All apps-schema objects privs was explicitly given before, so except sys privs - no problem to revoke DBA role:
So, the plan was:
- explicitly grant all necessary sys privs not through a role;
- revoke DBA;
In a few minutes i've got an error about user unable to allocate space in some tablespace. So I looked at dba_sys_privs and didn't saw UNLIMITED TABLESPACE, despite I gave it explicitly 100% for sure.
I grant it to user one more time and problem was solved. My next step was testing this "magical" privilege:
SQL> create user TEST identified by "test";
SQL> select count(*) from dba_sys_privs where grantee ='TEST';
  COUNT(*)
----------
         0
Grant all sys privs to TEST which in DBA role:  
SQL> select 'grant '||privilege||' to TEST;' from dba_sys_privs where grantee='DBA';
SQL> select count(*) from dba_sys_privs where grantee ='TEST'; 
  COUNT(*)
----------
       202   
SQL> grant DBA to TEST;
Grant succeeded
SQL> revoke DBA from TEST;
Revoke succeeded
Check privs count:
SQL> select count(*) from dba_sys_privs where grantee ='TEST';
  COUNT(*)
----------
       201
Then I found MOS note and all became clear to me:
Revoking DBA or RESOURCE Roles Revokes UNLIMITED TABLESPACE from the User [ID 1084014.6]
And one more:
Role Restrictions [ID 11740.1]



1 comment: