问题描述
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.