17 | 08 | 2017
Latest Articles
Popular Articles

Security

Why I dislike the resource role

User Rating:  / 0
PoorBest 

Why I dislike the resource role

 

A couple of days ago I had a discussion with a developer who claimed the lack of the  RESOURCE role. I told him I' m not eager to grant the resource role, since granting this role grants as well the UNLIMITED TABLESPACE privilege. The latter one gives the possibility to create segments in the system tablespace, something we should avoid.

Let us take  look what the resource role is about. We' ll query dba_sys_privs, dba_tab_privs and dba_role_privs

Which are the system privileges granted to the resource role ?


DB:APP / USER:SYS >  select * from dba_sys_privs  where grantee='RESOURCE';

GRANTEE                        PRIVILEGE                                       


RESOURCE                       CREATE TRIGGER                          
RESOURCE                       CREATE SEQUENCE                       
RESOURCE                       CREATE CLUSTER                          
RESOURCE                       CREATE TYPE                                
RESOURCE                       CREATE PROCEDURE                     
RESOURCE                       CREATE TABLE                              
RESOURCE                       CREATE INDEXTYPE                        
RESOURCE                       CREATE OPERATOR                         

 Which are the object privileges granted to the resource role ?


DB:APP / USER:SYS >   select * from dba_tab_privs   where grantee='RESOURCE';

no rows selected
 

Which are the roles granted to the resource role ?


DB:APP / USER:SYS >   select * from dba_role_privs   where grantee='RESOURCE';

no rows selected

However by granting the resource role, the unlimited tablespace is as well granted, let' s take a look


DB:APP / USER:SYS > grant resource to APP_OWNER;

Grant succeeded.

DB:APP / USER:SYS > select * from dba_sys_privs where privilege like '%UNLIMITED%';

GRANTEE                        PRIVILEGE                               


SYSTEM                         UNLIMITED TABLESPACE                    
ORDSYS                         UNLIMITED TABLESPACE                    
SI_INFORMTN_SCHEMA             UNLIMITED TABLESPACE                    
MDSYS                          UNLIMITED TABLESPACE                    
OUTLN                          UNLIMITED TABLESPACE                    
CTXSYS                         UNLIMITED TABLESPACE                    
XDB                            UNLIMITED TABLESPACE                    
ORDPLUGINS                     UNLIMITED TABLESPACE                    
TSMSYS                         UNLIMITED TABLESPACE                    
WMSYS                          UNLIMITED TABLESPACE                    
EXFSYS                         UNLIMITED TABLESPACE                   
APP_OWNER                      UNLIMITED TABLESPACE                  
SYS                            UNLIMITED TABLESPACE                   
DBSNMP                         UNLIMITED TABLESPACE                   
SYSMAN                         UNLIMITED TABLESPACE

Which are the privilege granted to the schema owner APP_OWNER ?


DB:APP / USER:SYS > select * from dba_sys_privs  where grantee='APP_OWNER';

GRANTEE                PRIVILEGE                               


APP_OWNER              CREATE JOB                              
APP_OWNER              CREATE ANY TRIGGER                      
APP_OWNER              CREATE ANY TABLE                        
APP_OWNER              SELECT ANY TABLE                        
APP_OWNER              UNLIMITED TABLESPACE                    

Let us revoke the resource role, hence we notice the unlimited tablespace privilege will be revoked too


DB:APP / USER:SYS > revoke resource from APP_OWNER;

Revoke succeeded.

DB:APP / USER:SYS > select * from dba_sys_privs  where grantee='APP_OWNER';
GRANTEE                PRIVILEGE                               

APP_OWNER              CREATE JOB                              
APP_OWNER              CREATE ANY TRIGGER                      
APP_OWNER              CREATE ANY TABLE                        
APP_OWNER              SELECT ANY TABLE                        

Here we notice the schema owner APP_OWNER has been granted two additional roles as well.


DB:APP / USER:SYS > select * from dba_role_privs where grantee='APP_OWNER';

GRANTEE                GRANTED_ROLE                  


APP_OWNER              APP_OWNER_ROLE                
APP_OWNER              CTXAPP                        

Let us take a look what the app_owner_role is about, which are the privilege granted to this role ?


DB:APP / USER:SYS > select * from dba_sys_privs  where grantee='APP_OWNER_ROLE';

GRANTEE                      PRIVILEGE                               


APP_OWNER_ROLE               DEBUG ANY PROCEDURE                     
APP_OWNER_ROLE               DEBUG CONNECT SESSION                   
APP_OWNER_ROLE               CREATE TYPE                             
APP_OWNER_ROLE               CREATE SEQUENCE                         
APP_OWNER_ROLE               CREATE CLUSTER                          
APP_OWNER_ROLE               CREATE PUBLIC SYNONYM                   
APP_OWNER_ROLE               CREATE PROCEDURE                        
APP_OWNER_ROLE               QUERY REWRITE                           
APP_OWNER_ROLE               CREATE INDEXTYPE                        
APP_OWNER_ROLE               DROP PUBLIC SYNONYM                     
APP_OWNER_ROLE               CREATE TABLE                            
APP_OWNER_ROLE               CREATE SESSION                          
APP_OWNER_ROLE               ON COMMIT REFRESH                       
APP_OWNER_ROLE               CREATE MATERIALIZED VIEW                
APP_OWNER_ROLE               CREATE VIEW                             
APP_OWNER_ROLE               CREATE SYNONYM                          
APP_OWNER_ROLE               CREATE TRIGGER                          

The APP_OWNER_ROLE gives the schema owner even more privileges than the resource role, however the unlimited tablespace privilege has not been granted.

Hence we are able to controle were objects, segments are created.

Some of the privileges directly granted to the schema owner - APP_OWNER - are powerfull, they should be granted with care.

It is for this reason I recommended to the design team NOT to connect with the schema owner itself but with some dedicated users.

Database security is important.