用户有connect,resource,dba角色权限后回收dba权限导致无UNLIMITED TABLESPACE权限造成业务中断

如果授予用户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