When Revoking Unlimited Tablespace System Privilege Tablespace Quota Also Removed [ID 1169515.1]
		修改时间 03-JAN-2011     类型 PROBLEM     状态 MODERATED

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.

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1 and later   [Release: 11.2 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.
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

This is expected behavior. for version 11.2 and later.

The unlimited tablespace system privilege is a super set of tablespace quotas.
If this privilege is revoked, the user will have to grant quotas to individual tablespaces explicitly.
Note that this behavior. is changed starting version 11.2 and is not seen in previous versions.
This behavior. change is not related to a specific fix.

Solution

After the system privilege unlimited tablespace is revoked, existing quota on individual tablespaces need to be granted again.

A request has been made to change the Security Guide on this topic.