Be Careful When Revoking UNLIMITED TABLESPACE

Imagine that you have been asked to implement an Oracle security checklist on a 11g Release 2 production database and, as part of this task, you follow the principle of least privilege.

You revoke all non-required privileges from PUBLIC , revoke any extra ANY privileges from non-required users, and, in brief, you allow all users to perform only what they really require and not more, so you revoke all extra privileges.

One of the system privileges that is against the least privilege principle is the UNLIMITED TABLESPACE privilege. You can manage each individual user so they have unlimited quota on the few tablespaces they require, but having UNLIMITED TABLESPACE causes users to have unlimited quota on ALL tablespaces of the database.

Consider you have a user KAMRAN, which needs access to USERS tablespace only, but UNLIMITED TABLESPACE has already been granted to him; no quota on USERS has been granted to him individually, only UNLIMITED TABLESPACE :

SQL> select * from dba_sys_privs where grantee='KAMRAN';
GRANTEE                        PRIVILEGE                        ADM
------------------------------ -------------------------------- ---
KAMRAN                         UNLIMITED TABLESPACE             NO
SQL> select * from dba_ts_quotas where username='KAMRAN';
no rows selected

You plan to revoke UNLIMITED TABLESPACE from this user and instead grant him UNLIMITED quota on USERS tablespace , so you plan to grant required quota first and then revoke UNLIMITED TABLESPACE as follows:

SQL> alter user KAMRAN quota unlimited on USERS;
User altered.
SQL> select * from dba_ts_quotas where username='KAMRAN';
TABLESPACE_NAME      USERNAME    BYTES  MAX_BYTES   BLOCKS MAX_BLOCKS DRO
-------------------- ---------- ------ ---------- -------- ---------- ---
USERS                KAMRAN          0         -1        0         -1 NO

Now that KAMRAN has UNLIMITED quota on USERS tablespace and you have confirmed it with the above query, you THINK you can safely revoke UNLIMITED TABLESPACE and issue the following command:

SQL> revoke UNLIMITED TABLESPACE from KAMRAN;
Revoke succeeded.

And you think everything is fine.

BUT …
This won’t work and causes the following error as soon as the user tries to create a new segment or extend an existing one:

ORA-01536: space quota exceeded for tablespace

You have granted required quota first and then revoked UNLIMITED TABLESPACE so why doesn’t it work ?

This is the reason:

When the UNLIMITED TABLESPACE privilege is revoked from a user, it ALSO revokes all granted quotas on any individual tablespace from the user. In other words, after revoking this privilege from a user, the user won’t have any quota on any tablespace at all:

SQL> revoke UNLIMITED TABLESPACE from KAMRAN;
Revoke succeeded.
SQL> select * from dba_ts_quotas where username='KAMRAN';
no rows selected

This is an unexpected behavior for this privilege, and if you are unaware of it, it can cause you trouble if you revoke it from an application user in the above order in your production database.

You will need to revoke UNLIMITED TABLESPACE first and then grant required quota in each tablespace, even though the user will experience a lack of quota for a short period of time between the two commands.