Let's compare CONNECT, RESOURCE and the new DB_DEVELOPER_ROLE. First lets see CONNECT:
SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> @find_all_privs
find_all_privs: Release 1.0.7.0.0 - Production on Thu Apr 06 15:25:13 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
NAME OF USER TO CHECK [ORCL]: CONNECT
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
User => CONNECT has been granted the following privileges
====================================================================
SYS PRIV => CREATE SESSION grantable => NO
SYS PRIV => SET CONTAINER grantable => NO
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
I have always said since 12c why include SET CONTAINER? in a PDB and for a LOCAL user SET CONTAINER doesn't make sense. If CONNECT only had CREATE SESSION then there is no problem using this Oracle designed role but because of SET CONTAINER we should not use it as it has one extra right that you do not need.
Here is an example of trying to use it from a LOCAL user in the 23c PDB:
SQL> create user vc identified by vc;
User created.
SQL> grant connect to vc;
Grant succeeded.
SQL> connect vc/vc@//192.168.56.18:1521/freepdb1
Connected.
SQL> alter session set container=cdb$root;
ERROR:
ORA-01031: insufficient privileges
SQL>
OK, we cannot use SET CONTAINER to traverse to the CDB as a LOCAL user. Next lets look at the rights for RESOURCE:
SQL> @find_all_privs
find_all_privs: Release 1.0.7.0.0 - Production on Thu Apr 06 15:31:51 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
NAME OF USER TO CHECK [ORCL]: RESOURCE
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
User => RESOURCE has been granted the following privileges
====================================================================
ROLE => SODA_APP which contains =>
TABLE PRIV => EXECUTE object => XDB.DBMS_SODA_ADMIN grantable => NO
TABLE PRIV => EXECUTE object => XDB.DBMS_SODA_USER_ADMIN grantable => NO
TABLE PRIV => READ object => XDB.JSON$USER_COLLECTION_METADATA grantable => NO
SYS PRIV => CREATE ANALYTIC VIEW grantable => NO
SYS PRIV => CREATE ATTRIBUTE DIMENSION grantable => NO
SYS PRIV => CREATE CLUSTER grantable => NO
SYS PRIV => CREATE HIERARCHY grantable => NO
SYS PRIV => CREATE INDEXTYPE grantable => NO
SYS PRIV => CREATE MATERIALIZED VIEW grantable => NO
SYS PRIV => CREATE OPERATOR grantable => NO
SYS PRIV => CREATE PROCEDURE grantable => NO
SYS PRIV => CREATE PROPERTY GRAPH grantable => NO
SYS PRIV => CREATE SEQUENCE grantable => NO
SYS PRIV => CREATE SYNONYM grantable => NO
SYS PRIV => CREATE TABLE grantable => NO
SYS PRIV => CREATE TRIGGER grantable => NO
SYS PRIV => CREATE TYPE grantable => NO
SYS PRIV => CREATE VIEW grantable => NO
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
The grants to RESOURCE have grown since earlier versions. There were 9 system grants in 11.2 for instance and now 15. So the size and reach of this role grows as Oracle grown. If we grant CONNECT and RESOURCE then the user/schema gets 17 SYSTEM PRIVILEGES. Does a user really need 17 system rights.
In the talks that occurred last year in advance of 23c some Oracle staff showed use of a package DBMS_DEVELOPER_ADMIN to grant this role. Lets check the role exists in 23c FREE:
SQL> select * from dba_roles where role like '%DEV%';
ROLE
--------------------------------------------------------------------------------
ROLE_ID PASSWORD AUTHENTICAT COM O INH IMP
---------- -------- ----------- --- - --- ---
EXTERNAL_NAME
--------------------------------------------------------------------------------
DB_DEVELOPER_ROLE
94 NO NONE YES Y YES NO
GRAPH_DEVELOPER
115 NO NONE YES Y YES NO
2 rows selected.
SQL>
Yes, it does, its called DB_DEVELOPER_ROLE. The package referenced by some people DBMS_DEVELOPER_ADMIN is not in the 23c Free database:
SQL> select object_name,owner,object_type from dba_objects where object_name like '%DEVELOPER%';
OBJECT_NAME
--------------------------------------------------------------------------------
OWNER
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
APEX_WORKSPACE_DEVELOPERS
PUBLIC
SYNONYM
APEX_APPL_DEVELOPER_COMMENTS
PUBLIC
SYNONYM
APEX_DEVELOPER_ACTIVITY_LOG
PUBLIC
SYNONYM
WWV_FLOW_DEVELOPERS
APEX_220200
TABLE
WWV_FLOW_DEVELOPERS_PK
APEX_220200
OBJECT_NAME
--------------------------------------------------------------------------------
OWNER
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
INDEX
WWV_FLOW_DEVELOPERS_PK2
APEX_220200
INDEX
WWV_FLOW_DEVELOPERS_ID
APEX_220200
INDEX
WWV_FLOW_DEVELOPERS_FKIDX
APEX_220200
INDEX
WWV_FLOW_DEVELOPER_WORKSPACES
APEX_220200
VIEW
OBJECT_NAME
--------------------------------------------------------------------------------
OWNER
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
WWV_FLOW_DEVELOPERS_T1
APEX_220200
TRIGGER
WWV_FLOW_FND_DEVELOPER_API
APEX_220200
PACKAGE
WWV_FLOW_DEVELOPER_TOOLBAR
APEX_220200
PACKAGE
WWV_BIU_FLOW_DEVELOPERS_AUDIT
APEX_220200
TRIGGER
WWV_FLOW_FND_DEVELOPER_API
APEX_220200
OBJECT_NAME
--------------------------------------------------------------------------------
OWNER
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
PACKAGE BODY
WWV_FLOW_DEVELOPER_TOOLBAR
APEX_220200
PACKAGE BODY
APEX_WORKSPACE_DEVELOPERS
APEX_220200
VIEW
APEX_APPL_DEVELOPER_COMMENTS
APEX_220200
VIEW
APEX_DEVELOPER_ACTIVITY_LOG
APEX_220200
VIEW
18 rows selected.
SQL>
Check rights granted to the DB_DEVELOPER_ROLE:
SQL> @find_all_privs
find_all_privs: Release 1.0.7.0.0 - Production on Thu Apr 06 15:39:48 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
NAME OF USER TO CHECK [ORCL]: DB_DEVELOPER_ROLE
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
User => DB_DEVELOPER_ROLE has been granted the following privileges
====================================================================
ROLE => CTXAPP which contains =>
SYS PRIV => CREATE SEQUENCE grantable => NO
TABLE PRIV => DELETE object => CTXSYS.DR$DICTIONARY grantable => NO
TABLE PRIV => EXECUTE object => CTXSYS.CTX_ANL grantable => NO
TABLE PRIV => EXECUTE object => CTXSYS.CTX_DDL grantable => NO
TABLE PRIV => EXECUTE object => CTXSYS.CTX_ENTITY grantable => NO
TABLE PRIV => EXECUTE object => CTXSYS.CTX_OUTPUT grantable => NO
TABLE PRIV => EXECUTE object => CTXSYS.CTX_THES grantable => NO
TABLE PRIV => EXECUTE object => CTXSYS.CTX_ULEXER grantable => NO
TABLE PRIV => EXECUTE object => CTXSYS.DRIENTL grantable => NO
TABLE PRIV => EXECUTE object => CTXSYS.DRITHSL grantable => NO
TABLE PRIV => INSERT object => CTXSYS.DR$DICTIONARY grantable => NO
TABLE PRIV => INSERT object => CTXSYS.DR$THS grantable => NO
TABLE PRIV => INSERT object => CTXSYS.DR$THS_BT grantable => NO
TABLE PRIV => INSERT object => CTXSYS.DR$THS_FPHRASE grantable => NO
TABLE PRIV => INSERT object => CTXSYS.DR$THS_PHRASE grantable => NO
TABLE PRIV => SELECT object => CTXSYS.DR$DICTIONARY grantable => NO
TABLE PRIV => UPDATE object => CTXSYS.DR$DICTIONARY grantable => NO
TABLE PRIV => UPDATE object => CTXSYS.DR$THS_PHRASE grantable => NO
ROLE => SODA_APP which contains =>
TABLE PRIV => EXECUTE object => XDB.DBMS_SODA_ADMIN grantable => NO
TABLE PRIV => EXECUTE object => XDB.DBMS_SODA_USER_ADMIN grantable => NO
TABLE PRIV => READ object => XDB.JSON$USER_COLLECTION_METADATA grantable => NO
SYS PRIV => CREATE ANALYTIC VIEW grantable => NO
SYS PRIV => CREATE ATTRIBUTE DIMENSION grantable => NO
SYS PRIV => CREATE CUBE grantable => NO
SYS PRIV => CREATE CUBE BUILD PROCESS grantable => NO
SYS PRIV => CREATE CUBE DIMENSION grantable => NO
SYS PRIV => CREATE DIMENSION grantable => NO
SYS PRIV => CREATE DOMAIN grantable => NO
SYS PRIV => CREATE HIERARCHY grantable => NO
SYS PRIV => CREATE JOB grantable => NO
SYS PRIV => CREATE MATERIALIZED VIEW grantable => NO
SYS PRIV => CREATE MINING MODEL grantable => NO
SYS PRIV => CREATE MLE grantable => NO
SYS PRIV => CREATE PROCEDURE grantable => NO
SYS PRIV => CREATE SEQUENCE grantable => NO
SYS PRIV => CREATE SESSION grantable => NO
SYS PRIV => CREATE SYNONYM grantable => NO
SYS PRIV => CREATE TABLE grantable => NO
SYS PRIV => CREATE TRIGGER grantable => NO
SYS PRIV => CREATE TYPE grantable => NO
SYS PRIV => CREATE VIEW grantable => NO
SYS PRIV => DEBUG CONNECT SESSION grantable => NO
SYS PRIV => EXECUTE DYNAMIC MLE grantable => NO
SYS PRIV => FORCE TRANSACTION grantable => NO
SYS PRIV => ON COMMIT REFRESH grantable => NO
TABLE PRIV => EXECUTE object => SYS.JAVASCRIPT grantable => NO
TABLE PRIV => READ object => SYS.V_$PARAMETER grantable => NO
TABLE PRIV => READ object => SYS.V_$STATNAME grantable => NO
TABLE PRIV => SELECT object => SYS.DBA_PENDING_TRANSACTIONS grantable => NO
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
That's a lot of grants both system and object and role grants. There is one duplicate system privilege grant, that is CREATE SEQUENCE, granted direct to the role and via the CTXAPP role. So there are 25 system privileges granted. Wow. The duplicate is not good. The grant on v_$PARAMETER not good for security as if an attacker gets access to a user with DV_DEVELOPER_ROLE then they can find out all parameters. Also the grant to SYS.JAVASCRIPT would give too much away. also the grant to DEBUG CONNECT SESSION would allow debugging on the owners objects or objects where debug rights exist; this could be used to learn "secret" values such as passwords, keys etc passed and used in PL/SQL where not normally visible. Is CREATE MLE really necessary for all developers? no, only if they wish to create Javascript procecures. The same argument can be applied across the board.
If a developer has CONNECT, RESOURCE or DB_DEVELOPER_ROLE then they have too many rights and if these role grants end up in production then change can take place or new objects be created.
We can compare just the system privilege grants in CONNECT and RESOURCE compared to DB_DEVELOPER_ROLE here:
In principal this new role is worse then RESOURCE in terms of rights BUT also neither matches your own application and objects or least rights.
A better approach is to not use Oracle designed roles BUT to design your own roles. For instance if a developer designs a simple application with TABLES, VIEWS, PL/SQL, SEQUENCES and TYPES then create your own role that has just these rights only as well as CREATE SESSION. Grant this role during development and revoke it afterwards during testing. If an update is needed to the objects then grant the role back. Also use a schema only account to own the objects and grant your role for deployment and use proxy to do the deploy as the schema.
Think about least rights at development time and at run time
#oracleace
#23c
#dbsec
