My Oracle Support Banner

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

Last updated on MARCH 03, 2019

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 <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 tc;

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


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