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

Last updated on NOVEMBER 22, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.

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 and later (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 tc cascade;

create user tc identified by tc default tablespace users temporary tablespace temp;

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

alter user tc quota 100m on users;

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

grant unlimited tablespace to TC;

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

revoke unlimited tablespace from tc;

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

Changes

Database upgraded to version 11.2.0.1

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms