如果授予用户connect,resource时，此时用户已经有了UNLIMITED TABLESPACE权限。
此时如果授予用户DBA权限，然后再回收DBA角色；此时会收回UNLIMITED TABLESPACE权限。
近期遇到过此种情况，导致业务用户无法使用表空间，造成较为严重的事故，记录一下。
测试如下;
1.创建用户并授予connect,resource、dba权限并验证
SQL> create user test identified by test;
User created.
SQL> grant connect,resource to test;
Grant succeeded.
SQL>
SQL> select * from dba_role_privs where grantee=’TEST’;
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST RESOURCE NO YES
TEST CONNECT NO YES
SQL> select * from dba_sys_privs where grantee=’TEST’;
GRANTEE PRIVILEGE ADM
—————————— —————————————- —
TEST UNLIMITED TABLESPACE NO
SQL> grant dba to test;
Grant succeeded.
SQL> select * from dba_role_privs where grantee=’TEST’;
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST RESOURCE NO YES
TEST DBA NO YES
TEST CONNECT NO YES
SQL> select * from dba_sys_privs where grantee=’TEST’;
GRANTEE PRIVILEGE ADM
—————————— —————————————- —
TEST UNLIMITED TABLESPACE NO
2.回收dba权限并检查权限
SQL> revoke dba from test;
Revoke succeeded.
SQL> select * from dba_role_privs where grantee=’TEST’;
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST RESOURCE NO YES
TEST CONNECT NO YES
SQL> select * from dba_sys_privs where grantee=’TEST’;
no rows selected
SQL> grant connect,resource to test;
Grant succeeded.
SQL> select * from dba_role_privs where grantee=’TEST’;
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST RESOURCE NO YES
TEST CONNECT NO YES
SQL> select * from dba_sys_privs where grantee=’TEST’;
GRANTEE PRIVILEGE ADM
—————————— —————————————- —
TEST UNLIMITED TABLESPACE NO
