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.
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.
This is documented here:
Thanks for your comment Delfino, this can be a different explanation causing DBAs to pay extra attention to this behavior.
Interesting article , thank you for sharing. Sometimes i feel unlimited tablespace is like an old weed , just like connect and resource role. When setting up Database it should be part of standard procedure to Add only what is really needed.
Thanks for your comment Mathijs , I somehow agree with you.
you should note that this behavior starts with 11gR2 version.
In 11gR1 version granted quotas on individual tablespaces still exist after revoking UNLIMITED TABLESPACE privilege.
Btw, interesting article – thanks for sharing.
Thanks for your comment Marko.
You are right , 11g R2 is the first version that has this behavior , thanks for pointing to this.
Thanks for sharing.
I described a similar scenario few months ago comparing a different behaviour while revoking RESOURCE role to users on 10gR2 and 11gR2.
Thanks for Sharing your post Marco, found it valuable and complete.
This story tells you: get things fully tested as you can before move to production.
Thanks for reading my post Yang , you are absolutely right !
hi dear kamran
thank you for your good post
It’s my pleasure too see your post here
Thanks for your comment Mahdi
Thanks Kamran. You have saved me from making a change which looked supposedly straight forward but would have caused revocation of even explicitly granted tablespace quotas.
Worth reading this post
Thanks i got now unlimited tablespace priviledge.