DB_Developer_Role in 23c

Starting with Oracle database 23c, the new role “DB_DEVELOPER_ROLE” allows administrators to quickly assign all necessary privileges to developers need to design/build/deploy the applications for Oracle database (including required system privileges to build data model and object privileges required to monitor and debug applications)
 
By using this role, administrators no longer need to guess which privilege may be necessary for application developers.
 
It is a recommended best practise to grant application developers the DB_DEVELOPER_ROLE, rather than individually granting these privileges, or granting the user the DBA role, as the new DB_DEVELOPER_ROLE adhere to the concept of “least privilege” principles and ensure the greater security for development environment.
 
the new DB_DEVELOPER_ROLE can be used either at CDB root (for common users) or at the PDB (for local users)
 
the following sql will provide us all the system privilege assigned to this role.

 

demo@FREEPDB1> select privilege
  2  from role_sys_privs
  3  where role ='DB_DEVELOPER_ROLE' ;
 
PRIVILEGE
------------------------------
CREATE DOMAIN
CREATE MLE
CREATE ANALYTIC VIEW
CREATE HIERARCHY
CREATE ATTRIBUTE DIMENSION
EXECUTE DYNAMIC MLE
CREATE CUBE BUILD PROCESS
CREATE CUBE
CREATE CUBE DIMENSION
CREATE MINING MODEL
CREATE JOB
DEBUG CONNECT SESSION
ON COMMIT REFRESH
CREATE DIMENSION
CREATE TYPE
CREATE MATERIALIZED VIEW
CREATE TRIGGER
CREATE PROCEDURE
FORCE TRANSACTION
CREATE SEQUENCE
CREATE VIEW
CREATE SYNONYM
CREATE TABLE
CREATE SESSION
 
24 rows selected.
 
demo@FREEPDB1>

for all the object level privileges

 

demo@FREEPDB1> select table_name,privilege
  2  from role_tab_privs
  3  where role ='DB_DEVELOPER_ROLE';
 
TABLE_NAME                PRIVILEGE
------------------------- ------------------------------
JAVASCRIPT                EXECUTE
V_$STATNAME               READ
V_$PARAMETER              READ
DBA_PENDING_TRANSACTIONS  SELECT

 

For all other granted role

 

demo@FREEPDB1> select granted_role
  2  from role_role_privs
  3  where role ='DB_DEVELOPER_ROLE' ;
 
GRANTED_ROLE
---------------
SODA_APP
CTXAPP
 
demo@FREEPDB1>

 
SODA_APP role is there for to work with JSON collections, where as CTXAPP role is a system defined role the enable users to create and delete Oracle Text preferences, and allow users to create preferences and use PL/SQL packages.