f Oracle revoke DBA的爛設計 ~ 迪貝之家

Oracle revoke DBA的爛設計

有時為了方便AP作業
會暫時把DBA權限放出去
但收回時就出現space quota error
TMD,真是什麼爛設計啊!
人生就是在血和淚的教訓中成長
真的是很靠北

還真冤枉了前一次跟我要DBA權限的同仁,就發了line 給他,說聲抱歉。



Revoking DBA or RESOURCE Roles Revokes UNLIMITED TABLESPACE from the User (Doc ID 1084014.6)
Last updated on AUGUST 04, 2018

APPLIES TO:
Oracle Database - Enterprise Edition - Version 8.1.7.0 to 11.2.0.3 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.
Checked for relevance on 31-May-2013

SYMPTOMS
Revoking either RESOURCE or DBA roles revokes the UNLIMITED TABLESPACE privilege.

SQL> connect system/manager ---> this will be referred to as Session 1
Connected.

SQL> create user abc identified by abc;
Statement processed.

SQL> grant connect, resource to abc;
Statement processed.

SQL> connect abc/abc ---> this will be referred to as Session 2

SQL> create table test1 (c1 number);
Table created.

From another session connected as SYSTEM:

Session 1(SYSTEM):

SQL> grant dba to abc;
Statement processed.

SQL> revoke dba from abc;
Statement processed.

Session 2(ABC):

SQL> create table test2(c1 number);
create table test2(c1 number)
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'SYSTEM'


When the dba privilege is revoked, the quota resources from any tablespace are lost although they were acquired earlier via another role(RESOURCE).

Re granting the RESOURCE role to the user, will allow the user to allocate space in the tablespaces.

Session 1(SYSTEM):

SQL> grant resource to abc;
Statement processed.

Session 2(ABC):

SQL> create table test2(c1 number);
Table created.