Revoke UNLIMITED TABLESPACE

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 is 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 user to have unlimited quota on ALL tablespaces of the database.

Consider you have a user GOLD who needs access to USERS tablespace only.

You plan to revoke UNLIMITED TABLESPACE from this user and instead grant him UNLIMITED quota on USERS tablespace .

Note : First  revoke unlimited tablespace from GOLD then grant quota unlimited

SQL> REVOKE   UNLIMITED TABLESPACE  FROM GOLD;

SQL> ALTER USER GOLD QUOTA UNLIMITED ON USERS;

View :  DBA_TS_QUOTAS

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, it will be revoke quota tablespace privilege too.The user won’t have any quota on any tablespace at all: