Security
Why I dislike the resource role
User Rating: / 0
- Details
-
Parent Category: Articles
-
Created on Thursday, 18 March 2010 21:30
-
Last Updated on Monday, 12 March 2012 13:54
-
Published on Thursday, 18 March 2010 21:30
-
Written by Guy Lambregts
-
Hits: 4430
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.