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

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

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.