ANBOB™

If you are often grant privilege to new account like this “grant connect, resource to “, as you know from 11g the role “Connect” only have “create session” privilege , but now in 12c ,the role “Resource” privilege had changed too.

From 12c onwards, “Resource” role will not provide “unlimited tablespace” privilege and no longer available too. As we already know resource role is deprecated from 11g onwards and will not be available in future releases.

The MOS’s note. 1084014.6 written
”
from 8.1.7 until 11.2.0.4

Whenever an user is granted the RESOURCE and DBA roles these will be reflected in the DBA_ROLE_PRIVS view. Additionally a new privilege will be added to DBA_SYS_PRIVS. This additional privilege is UNLIMITED TABLESPACE .

Whenever one of the above two roles is revoked the UNLIMITED TABLESPACE privilege is automatically revoked as well. This is the expected behaviour.
”

#db version 11.2.0.3

sys@ANBOB>create user weejar1 identified by weejar1;
User created.

sys@ANBOB>grant resource to weejar1;
Grant succeeded.

sys@ANBOB>select * from dba_role_privs where grantee='WEEJAR1';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
WEEJAR1                        RESOURCE                       NO  YES

sys@ANBOB>select * from dba_sys_privs where grantee='RESOURCE';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO

sys@ANBOB>select * from dba_sys_privs where grantee='WEEJAR1';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
WEEJAR1                        UNLIMITED TABLESPACE                     NO

Tip:
If I query dba_sys_privs I’ll get “UNLIMITED TABLESPACE” privilege granted to user. “UNLIMITED TABLESPACE” privilege was assigned to user with RESOURCE role. This is very dangerous privilege.

As you can see, when you grant RESOURCE role to user, that user/application gets “UNLIMITED TABLESPACE” privilege and automatically has rights to write in any tablespace that he chooses to – even in SYSTEM tablespce.

Solution:

sys@ANBOB>revoke UNLIMITED TABLESPACE from weejar1;
Revoke succeeded.

sys@ANBOB>select * from dba_sys_privs where grantee='WEEJAR1';
no rows selected

Tip:
11.2.0.4 test same as 11.2.0.3

# in 12.1 or beyond

idle>show pdbs
              CON_ID CON_NAME                       OPEN MODE  RESTRICTED
-------------------- ------------------------------ ---------- ----------
                   2 PDB$SEED                       READ ONLY  NO
                   3 PDB1                           READ WRITE NO
                   4 ERPDB                          MOUNTED
                   5 DRMDB                          MOUNTED
idle>alter session set container=pdb1;
Session altered.

idle>create user weejar identified by weejar;
User created.

idle>grant resource to weejar;
Grant succeeded.

idle>select * from dba_sys_privs where grantee='RESOURCE';
GRANTEE                        PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
RESOURCE                       CREATE TRIGGER                           NO  YES
RESOURCE                       CREATE SEQUENCE                          NO  YES
RESOURCE                       CREATE TYPE                              NO  YES
RESOURCE                       CREATE PROCEDURE                         NO  YES
RESOURCE                       CREATE CLUSTER                           NO  YES
RESOURCE                       CREATE OPERATOR                          NO  YES
RESOURCE                       CREATE INDEXTYPE                         NO  YES
RESOURCE                       CREATE TABLE                             NO  YES

8 rows selected.

idle>select * from dba_sys_privs where grantee='WEEJAR';
no rows selected

idle>show rel
release 1201000100

idle>grant dba to weejar;
Grant succeeded.

idle>select * from dba_sys_privs where grantee='WEEJAR';
GRANTEE                        PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
WEEJAR                         UNLIMITED TABLESPACE                     NO  NO

Summary:
in 11g or above oracle versions .It is very dangerous to grant RESOURCE role without revoking UNLIMITED TABLESAPCE privilege before Import. With UNLIMITED TABLESPACE privilege given to user import data could be imported in any tablespace and you could load data to unwanted locations. but from 12c it’s gone. the dba role never changed.

打赏

微信扫一扫，打赏作者吧～