My Oracle Support Banner

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

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


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.