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 .
