When Revoking Unlimited Tablespace System Privilege Tablespace Quota Also Removed (Doc ID 1169515.1)

Last updated on JANUARY 19, 2024

Applies to:

Symptoms

 
If the system privilege unlimited tablespace is revoked from a user, the explicit quota given on tablespaces is also revoked.

This is only seen in version 11.2.0.1 till 12.1.0.1 (and if compatible=11.2.0 or higher).
Previous versions do not show this behavior.

When the DBA role is revoked from a user, the same behavior is seen.
The behavior can be reproduced using the following example script, after revoking the system privilege unlimited tablespace the view dba_ts_quotas does not return the expected rows anymore:

connect / as sysdba

drop user <username> cascade;

create user <username> identified by <password>  default tablespace users temporary tablespace temp;

select tablespace_name,username,max_bytes from dba_ts_quotas where username='<username>';
select * from dba_sys_privs where grantee='<username>';

alter user <username> quota 100m on users;

select tablespace_name,username,max_bytes from dba_ts_quotas where username='<username>';
select * from dba_sys_privs where grantee='<username>';

grant unlimited tablespace to <username>;

select tablespace_name,username,max_bytes from dba_ts_quotas where username='<username>';
select * from dba_sys_privs where grantee='<username>';

revoke unlimited tablespace from <username>;

select tablespace_name,username,max_bytes from dba_ts_quotas where username='<username>';
select * from dba_sys_privs where grantee='<username>';

Changes

Database upgraded to version 11.2.0.1

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.