使用者有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