Introduction
The "side-effects" for role DBA, for role RESOURCE and for privilege UNLIMITED TABLESPACE are changed by Oracle in every major release.
The most disturbing (and illogical) change was about "revoke UNLIMITED TABLESPACE ..." revokes all quotas too.
And please note: "grant UNLIMITED TABLESPACE ..." does not remove quotas which could be logical place to remove quotas because it overrides quotas. Instead, revoke removes quotas.
When Revoking Unlimited Tablespace System Privilege Tablespace Quota Also Removed (Doc ID 1169515.1)
Complain
"UNLIMITED TABLESPACE" is system privilege like "SELECT ANY TABLE".
Now, imagine that "revoke SELECT ANY TABLE ..." would remove all "SELECT ON <table>" privileges as side effect. This would be very very disturbing ... But Oracle decided to do it for quotas, revoke unlimited tablespace does it for quotas since 11.2+ !
Any reference would be good that provide insight view about what Oracle architects were thinking regarding quotas.
And why these rights are still not changed to be symmetric? Then it would be easy. Grant gives the right, Revoke removes the right and no side-effects, no confusion. Would'nt it? What could be the reason to keep exceptions?
How behavior is changed over releases?
I compiled the following table because I could not remember those exceptions and side-effects anymore by heart:
11.1.0.7
|
11.2.0.[2|3|4]
|
12.1.0.[1|2]
|
expected (symmetric behavior)
|
|
1. grant/revoke UNLIMITED TABLESPACE revokes all
quotas
|
N
|
Y
|
Y
|
N
|
2. grant/revoke RESOURCE revokes all quotas
|
N
|
Y
|
N
|
N
|
3. grant/revoke DBA revokes all quotas
|
N
|
Y
|
Y
|
N
|
4. grant RESOURCE grants unlimited tablespace
|
Y
|
Y
|
N
|
N
|
5. revoke RESOURCE revokes unlimited tablespace
|
Y
|
Y
|
N
|
N
|
6. grant RESOURCE / revoke unlimited tablespace
|
Y
|
Y
|
ORA-01952
|
ORA-01952
|
7. grant DBA grants unlimited tablespace
|
Y
|
Y
|
Y
|
N
|
8. revoke DBA revokes unlimited tablespace
|
Y
|
Y
|
Y
|
N
|
9. grant DBA / revoke unlimited tablespace
|
Y
|
Y
|
Y
|
ORA-01952
|
10. grant UNLIMITED TABLESPACE / grant RESOURCE /
revoke RESOURCE / revoke UNLIMITED TABLESPACE
|
ORA-01952
|
ORA-01952
|
Y
|
Y
|
11. grant UNLIMITED TABLESPACE / grant DBA /
revoke DBA / revoke UNLIMITED TABLESPACE
|
ORA-01952
|
ORA-01952
|
ORA-01952
|
Y
|
ORA-01952: system privileges not granted to 'TEST_USER'Green - represent good symmetric (repeatable) behaviorRed - represents bad non-symmetric (non-repeatable) behavior
Additionally, UNLIMITED TABLESPACE comes/goes with role RESOURCE and/or DBA. Therefore, grant/revoke RESOURCE and grant/revoke DBA also removes all quotas (additionally they remove privilege UNLIMITED TABLESPACE too, even in case if it was granted separately!).
Revoking DBA or RESOURCE Roles Revokes UNLIMITED TABLESPACE from the User (Doc ID 1084014.6)
There are improvements in 12.1 regarding role RESOURCE.
grant/revoke RESOURCE does not add/remove privilege UNLIMITED TABLESPACE and therefore does not remove quotas as well - Good!
I really hope that Oracle will fix this non-symmetric behavior soon regarding role DBA and regarding privilege UNLIMITED TABLESPACE as well (Bug 14347314).
Test case
Here is log of test case executed on Oracle version 12.1.0.2
SQL> select * from v$version;
BANNER CON_ID
---------------------------------------------------------------------------- ------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
SQL> -- test 1
SQL> create user test_user identified by a default tablespace users quota 100M on users quota 200M on sysaux;
User created.
SQL> select * from dba_ts_quotas where username = 'TEST_USER';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
---------------- ------------ ---------- ---------- ---------- ---------- ---
SYSAUX TEST_USER 0 209715200 0 25600 NO
USERS TEST_USER 0 104857600 0 12800 NO
SQL> grant UNLIMITED TABLESPACE to test_user;
Grant succeeded.
-- NOTE: QUOTAS ARE STILL HERE AFTER GRANT - good
SQL> select * from dba_ts_quotas where username = 'TEST_USER';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
---------------- ------------ ---------- ---------- ---------- ---------- ---
SYSAUX TEST_USER 0 209715200 0 25600 NO
USERS TEST_USER 0 104857600 0 12800 NO
SQL> revoke UNLIMITED TABLESPACE from test_user;
Revoke succeeded.
-- NOTE: REVOKE REMOVED ALL QUOTAS AS SIDE EFFECT - bad
SQL> select * from dba_ts_quotas where username = 'TEST_USER';
no rows selected
SQL> drop user test_user cascade;
User dropped.
SQL> -- test 2
SQL> create user test_user identified by a default tablespace users quota 100M on users quota 200M on sysaux;
User created.
SQL>
SQL> select * from dba_ts_quotas where username = 'TEST_USER';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
---------------- ------------ ---------- ---------- ---------- ---------- ---
SYSAUX TEST_USER 0 209715200 0 25600 NO
USERS TEST_USER 0 104857600 0 12800 NO
SQL> grant RESOURCE to test_user;
Grant succeeded.
SQL> revoke RESOURCE from test_user;
Revoke succeeded.
-- NOTE: QUOTAS ARE STILL HERE - good
SQL> select * from dba_ts_quotas where username = 'TEST_USER';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
---------------- ------------ ---------- ---------- ---------- ---------- ---
SYSAUX TEST_USER 0 209715200 0 25600 NO
USERS TEST_USER 0 104857600 0 12800 NO
SQL> drop user test_user cascade;
User dropped.
SQL> -- test 3
SQL> create user test_user identified by a default tablespace users quota 100M on users quota 200M on sysaux;
User created.
SQL> select * from dba_ts_quotas where username = 'TEST_USER';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
---------------- ------------ ---------- ---------- ---------- ---------- ---
SYSAUX TEST_USER 0 209715200 0 25600 NO
USERS TEST_USER 0 104857600 0 12800 NO
SQL> grant DBA to test_user;
Grant succeeded.
SQL> revoke DBA from test_user;
Revoke succeeded.
-- NOTE: QUOTAS ARE REMOVED AFTER REVOKE - bad
SQL> select * from dba_ts_quotas where username = 'TEST_USER';
no rows selected
SQL> drop user test_user cascade;
User dropped.
SQL> -- test 4
SQL> create user test_user identified by a default tablespace users quota 100M on users quota 200M on sysaux;
User created.
SQL> grant RESOURCE to test_user;
Grant succeeded.
-- NOTE: NO UNLIMITED TABLESPACE PRIVILEGE - good
SQL> select * from dba_sys_privs where grantee = 'TEST_USER' and privilege = 'UNLIMITED TABLESPACE';
no rows selected
SQL> drop user test_user cascade;
User dropped.
SQL> -- test 5
SQL> create user test_user identified by a default tablespace users quota 100M on users quota 200M on sysaux;
User created.
SQL> grant UNLIMITED TABLESPACE to test_user;
Grant succeeded.
SQL> grant RESOURCE to test_user;
Grant succeeded.
SQL> revoke RESOURCE from test_user;
Revoke succeeded.
-- NOTE: UNLIMITED TABLESPACE IS STILL HERE - good
SQL> select * from dba_sys_privs where grantee = 'TEST_USER' and privilege = 'UNLIMITED TABLESPACE';
GRANTEE PRIVILEGE ADM COM
--------- -------------------- --- ---
TEST_USER UNLIMITED TABLESPACE NO NO
SQL> drop user test_user cascade;
User dropped.
SQL> -- test 6
SQL> create user test_user identified by a default tablespace users quota 100M on users quota 200M on sysaux;
User created.
SQL> grant RESOURCE to test_user;
Grant succeeded.
-- NOTE: NO UNLIMITED TABLESPACE TO REMOVE - good
SQL> revoke UNLIMITED TABLESPACE from test_user;
revoke UNLIMITED TABLESPACE from test_user
*
ERROR at line 1:
ORA-01952: system privileges not granted to 'TEST_USER'
SQL> drop user test_user cascade;
User dropped.
SQL> -- test 7
SQL> create user test_user identified by a default tablespace users quota 100M on users quota 200M on sysaux;
User created.
SQL> grant DBA to test_user;
Grant succeeded.
-- NOTE: UNLIMITED TABLESPACE APPEARED AS SIDE EFFECT - bad
SQL> select * from dba_sys_privs where grantee = 'TEST_USER' and privilege = 'UNLIMITED TABLESPACE';
GRANTEE PRIVILEGE ADM COM
--------- -------------------- --- ---
TEST_USER UNLIMITED TABLESPACE NO NO
SQL> drop user test_user cascade;
User dropped.
SQL> -- test 8
SQL> create user test_user identified by a default tablespace users quota 100M on users quota 200M on sysaux;
User created.
SQL> grant UNLIMITED TABLESPACE to test_user;
Grant succeeded.
SQL> grant DBA to test_user;
Grant succeeded.
SQL> revoke DBA from test_user;
Revoke succeeded.
-- NOTE: UNLIMITED TABLESPACE DISAPPEARD AS SIDE EFFECT - bad
SQL> select * from dba_sys_privs where grantee = 'TEST_USER' and privilege = 'UNLIMITED TABLESPACE';
no rows selected
SQL> drop user test_user cascade;
User dropped.
SQL> -- test 9
SQL> create user test_user identified by a default tablespace users quota 100M on users quota 200M on sysaux;
User created.
SQL> grant DBA to test_user;
Grant succeeded.
-- NOTE: UNLIMITED TABLESPACE APPEARED AS SIDE EFFECT - bad
SQL> revoke UNLIMITED TABLESPACE from test_user;
Revoke succeeded.
SQL> drop user test_user cascade;
User dropped.
SQL> -- test 10
SQL> create user test_user identified by a default tablespace users quota 100M on users quota 200M on sysaux;
User created.
SQL> grant UNLIMITED TABLESPACE to test_user;
Grant succeeded.
SQL> grant RESOURCE to test_user;
Grant succeeded.
SQL> revoke RESOURCE from test_user;
Revoke succeeded.
-- NOTE: UNLIMITED TABLESPACE IS STILL HERE - good
SQL> revoke UNLIMITED TABLESPACE from test_user;
Revoke succeeded.
SQL> drop user test_user cascade;
User dropped.
SQL> -- test 11
SQL> create user test_user identified by a default tablespace users quota 100M on users quota 200M on sysaux;
User created.
SQL> grant UNLIMITED TABLESPACE to test_user;
Grant succeeded.
SQL> grant DBA to test_user;
Grant succeeded.
SQL> revoke DBA from test_user;
Revoke succeeded.
-- NOTE: UNLIMITED TABLESPACE DISAPPEARD AS SIDE EFFECT - bad
SQL> revoke UNLIMITED TABLESPACE from test_user;
revoke UNLIMITED TABLESPACE from test_user*
ERROR at line 1:
ORA-01952: system privileges not granted to 'TEST_USER'
SQL> drop user test_user cascade;
User dropped.
SQL> spool off
References
Bug 14347314 - GRANT AND REVOKE STATEMENTS SHOULD HAVE SYMMETRIC EFFECTS
Registered in July 2012
Bug 14347326 - EXPDP/IMPDP SHOULD RESTORE THE EXACT STATUS OF THE SOURCE DATABASE
Registered in July 2012
-- end --
