DB_DEVELOPER_ROLE in Oracle 23 ai

Oracle 23 ai introduced a new role called 'DB_DEVELOPER_ROLE' . 

At first glance , it looks like , it is easier way to grant privileges . 

With ease , it comes with security risk. This is seemingly powerful role that encompasses other predefined roles RESOURCE , CTXAPP. 

We were discouraged to use the standard role and grant the necessary ( least possible) privileges to do the job. 

With RESOURCE , we can create lot of DB objects. ( See the output from Listing 2) 

SQLcl: Release 24.1 Production on Thu Jun 20 19:27:21 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Last Successful login time: Thu Jun 20 2024 19:27:22 +00:00

Connected to:

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.4.0.24.05

SQL> create user appdevuser identified by XXXXXXX;

User APPDEVUSER created.

SQL> grant db_developer_role to appdevuser;

Grant succeeded.

Listing 1 

----------

SQL> select 'SYS PRIVS' as Priv_Type  , privilege from dba_sys_privs where grantee = 'DB_DEVELOPER_ROLE'

  2  union all

  3  select 'ROLES' as Priv_Type  , granted_role from dba_role_privs where grantee = 'DB_DEVELOPER_ROLE'

  4  union all

  5  select 'TAB PRIVS' as Priv_Type  , table_name from dba_tab_privs where grantee = 'DB_DEVELOPER_ROLE'

  6* ;

PRIV_TYPE    PRIVILEGE                    

____________ ____________________________ 

SYS PRIVS    CREATE DOMAIN                

SYS PRIVS    CREATE MLE                   

SYS PRIVS    EXECUTE DYNAMIC MLE          

SYS PRIVS    CREATE CUBE BUILD PROCESS    

SYS PRIVS    CREATE CUBE                  

SYS PRIVS    CREATE CUBE DIMENSION        

SYS PRIVS    CREATE MINING MODEL          

SYS PRIVS    CREATE JOB                   

SYS PRIVS    DEBUG CONNECT SESSION        

SYS PRIVS    ON COMMIT REFRESH            

SYS PRIVS    CREATE DIMENSION             

SYS PRIVS    FORCE TRANSACTION            

SYS PRIVS    CREATE SESSION               

ROLES        CTXAPP                       

ROLES        RESOURCE                     

TAB PRIVS    JAVASCRIPT                   

TAB PRIVS    DBA_PENDING_TRANSACTIONS     

TAB PRIVS    DBMS_RLS                     

TAB PRIVS    DBMS_REDACT                  

TAB PRIVS    DBMS_TSDP_MANAGE             

TAB PRIVS    DBMS_TSDP_PROTECT            

TAB PRIVS    V_$STATNAME                  

TAB PRIVS    V_$PARAMETER                 

23 rows selected. 

Listing 2 

----------

SQL> select   privilege 

  2  from dba_sys_privs 

  3* where grantee in (  'RESOURCE' , 'CTXAPP');

PRIVILEGE                     

_____________________________ 

CREATE TABLE                  

CREATE CLUSTER                

CREATE SYNONYM                

CREATE VIEW                   

CREATE SEQUENCE               

CREATE PROCEDURE              

CREATE TRIGGER                

CREATE MATERIALIZED VIEW      

CREATE TYPE                   

CREATE OPERATOR               

CREATE INDEXTYPE              

CREATE ATTRIBUTE DIMENSION    

CREATE HIERARCHY              

CREATE ANALYTIC VIEW          

CREATE PROPERTY GRAPH         

CREATE SEQUENCE               

16 rows selected. 

As I learned from Tom Kyte , I usually follow  / advise the developers of the API based approach. 

With this approach , 

a) one schema will have only base tables 

b) second schema will have the views / packages 

c) third schema will have access to the objects created in the second schema.

d) application will be using only the third schema to interact with the database. 

"DB_DEVELOPER_ROLE" seems to be going in other direction .Please be sure to proceed with caution .