DB_DEVELOPER_ROLE Role in Oracle Database 23ai

Oracle database 23ai introduced a new DB_DEVELOPER_ROLE role to provide the basic roles and privileges Oracle believe are necessary for a database developer.

◉ DB_DEVELOPER_ROLE Role Basics

Oracle database 23ai introduced a new DB_DEVELOPER_ROLE role to provide the basic roles and privileges Oracle believe are necessary for a database developer. Prior to this release, people would often grant the CONNECT and RESOURCE roles as a starting point for most developers, but the DB_DEVELOPER_ROLE role is more than the sum of these two roles, as we will see below.

The DB_DEVELOPER_ROLE role is granted and revoked in the normal way.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

grant db_developer_role to testuser1;

revoke db_developer_role from testuser1;

◉ Comparing CONNECT, RESOURCE and DB_DEVELOPER_ROLE

The following three sections display the system, role and object privileges associated with each role.

    ◉ CONNECT Role

Here are the system, role and object privileges associated with the CONNECT role.

variable v_role VARCHAR2(30)

exec :v_role := 'CONNECT';

-- System Privileges

select sp.privilege

from   dba_sys_privs sp

where  sp.grantee = :v_role

order by 1;

PRIVILEGE

----------------------------------------

CREATE SESSION

SET CONTAINER

SQL>

-- Role Privileges

select rp.granted_role

from   dba_role_privs rp

where  rp.grantee = :v_role

order by 1;

no rows selected

SQL>

-- Object Privileges

column privilege format a30

column table_name format a30

select tp.privilege, tp.table_name 

from   dba_tab_privs tp

where  tp.grantee = :v_role

order by 1, 2;

no rows selected

SQL>

    ◉ RESOURCE Role

Here are the system, role and object privileges associated with the RESOURCE role. There are seven more system privileges granted to the RESOURCE role in 23ai compared to 19c.

variable v_role VARCHAR2(30)

exec :v_role := 'RESOURCE';

-- System Privileges

select sp.privilege

from   dba_sys_privs sp

where  sp.grantee = :v_role

order by 1;

PRIVILEGE

----------------------------------------

CREATE ANALYTIC VIEW

CREATE ATTRIBUTE DIMENSION

CREATE CLUSTER

CREATE HIERARCHY

CREATE INDEXTYPE

CREATE MATERIALIZED VIEW

CREATE OPERATOR

CREATE PROCEDURE

CREATE PROPERTY GRAPH

CREATE SEQUENCE

CREATE SYNONYM

CREATE TABLE

CREATE TRIGGER

CREATE TYPE

CREATE VIEW

15 rows selected.

SQL>

-- Role Privileges

select rp.granted_role

from   dba_role_privs rp

where  rp.grantee = :v_role

order by 1;

GRANTED_ROLE

------------------------------------------------

SODA_APP

SQL>

-- Object Privileges

column table_name format a30

select tp.privilege, tp.table_name 

from   dba_tab_privs tp

where  tp.grantee = :v_role

order by 1, 2;

no rows selected

SQL>

    ◉ DB_DEVELOPER_ROLE Role

Here are the system, role and object privileges associated with the DB_DEVELOPER_ROLE role.

variable v_role VARCHAR2(30)

exec :v_role := 'DB_DEVELOPER_ROLE';

-- System Privileges

select sp.privilege

from   dba_sys_privs sp

where  sp.grantee = :v_role

order by 1;

PRIVILEGE

------------------------------

CREATE CUBE

CREATE CUBE BUILD PROCESS

CREATE CUBE DIMENSION

CREATE DIMENSION

CREATE DOMAIN

CREATE JOB

CREATE MINING MODEL

CREATE MLE

CREATE SESSION

DEBUG CONNECT SESSION

EXECUTE DYNAMIC MLE

FORCE TRANSACTION

ON COMMIT REFRESH

13 rows selected.

SQL>

-- Role Privileges

select rp.granted_role

from   dba_role_privs rp

where  rp.grantee = :v_role

order by 1;

GRANTED_ROLE

------------------------------------------------

CTXAPP

RESOURCE

SQL>

-- Object Privileges

column privilege format a30

column table_name format a30

select tp.privilege, tp.table_name 

from   dba_tab_privs tp

where  tp.grantee = :v_role

order by 1, 2;

PRIVILEGE                      TABLE_NAME

------------------------- ------------------------------

EXECUTE                        DBMS_REDACT

EXECUTE                        DBMS_RLS

EXECUTE                        DBMS_TSDP_MANAGE

EXECUTE                        DBMS_TSDP_PROTECT

EXECUTE                        JAVASCRIPT

READ                               V_$PARAMETER

READ                               V_$STATNAME

SELECT                           DBA_PENDING_TRANSACTIONS

SQL>

◉ Considerations

For many years we have been discouraged from using the predefined roles, and encouraged to use the "least privileges" approach when granting privileges to users. From a security perspective, the less privileges a user has, the smaller the attack surface.

We can see from the above comparison, the DB_DEVELOPER_ROLE role has more privileges than the CONNECT and the RESOURCE roles combined. For some people or companies this might be considered too much privilege. It's up to you or your company to decide if this role is suitable or not.

The addition of more privileges to the RESOURCE role means people who are in the habit of granting the CONNECT plus RESOURCE roles are getting something approaching the level of privilege granted by the DB_DEVELOPER_ROLE. It is worth considering the impact of this.

Source: oracle-base.com