DB_DEVELOPER_ROLE Role in Oracle Database 23ai

When a database administrator creates a new user for developers, they may struggle to decide which privileges to grant. This is to ensure the user has the minimum required access to start building applications. Traditionally, many administrators grant the CONNECT and RESOURCE roles to the user. These roles enable developers to connect to the database and perform essential actions, such as creating tables, views, triggers, and more. Privileges in CONNECT and RESOURCE Roles Below is an example of the privileges granted by these roles in Oracle Database 19c: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production SQL> CREATE USER usef IDENTIFIED BY a; User created. SQL> GRANT connect, resource TO usef; Grant succeeded. SQL> CONNECT usef/a@target:1521/PDBTARGET Connected. SQL> SELECT * FROM session_privs; CREATE SESSION CREATE TABLE CREATE CLUSTER CREATE SEQUENCE CREATE PROCEDURE CREATE TRIGGER CREATE TYPE CREATE OPERATOR CREATE INDEXTYPE SET CONTAINER 10 rows selected. Changes in Oracle 12c In Oracle 12c, the RESOURCE role underwent a significant change with the removal of the UNLIMITED TABLESPACE privilege. Users with only the RESOURCE role cannot create segments. Additionally, the SET CONTAINER privilege was added to the CONNECT role, but it is irrelevant for local users. Enhancements in Oracle 23ai Until Oracle 23ai, there were no substantial changes to the privileges in these roles. However, in Oracle 23ai, the number of privileges granted by the RESOURCE role increased from 8 to 17. Here’s an example: Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release SQL> CREATE USER usef IDENTIFIED BY a; User created. SQL> GRANT connect, resource TO usef; Grant succeeded. SQL> CONNECT usef/a@target:1521/pdb1 Connected. SQL> SELECT * FROM session_privs; CREATE PROPERTY GRAPH CREATE ANALYTIC VIEW CREATE HIERARCHY CREATE ATTRIBUTE DIMENSION SET CONTAINER CREATE INDEXTYPE CREATE OPERATOR CREATE TYPE CREATE MATERIALIZED VIEW CREATE TRIGGER CREATE PROCEDURE CREATE SEQUENCE CREATE VIEW CREATE SYNONYM CREATE CLUSTER CREATE TABLE CREATE SESSION 17 rows selected. Introduction of DB_DEVELOPER_ROLE Oracle 23ai introduces a new role, DB_DEVELOPER_ROLE, designed as a replacement for CONNECT and RESOURCE. This role includes essential privileges required for developers and is tailored to meet their needs at the starting point. If the DB_DEVELOPER_ROLE is granted, the user receives the following system privileges: SQL> CREATE USER usef IDENTIFIED BY a; User created. SQL> GRANT DB_DEVELOPER_ROLE TO usef; Grant succeeded. SQL> CONNECT usef/a@target:1521/pdb1 Connected. SQL> SELECT * FROM session_privs; 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. Additional Notes 1.Quota Management Users with DB_DEVELOPER_ROLE still need specific tablespace quotas or UNLIMITED TABLESPACE privileges to create segments: SQL> CREATE TABLE tb AS SELECT * FROM dual; ORA-01950: The object owner 'USEF' has insufficient quota on tablespace 'SYSTEM'. 2. Privilege Modifications Privileges granted to DB_DEVELOPER_ROLE cannot be revoked at the PDB level. To revoke a privilege, you must connect to the root container: SQL> SHOW CON_NAME CON_NAME ------------------ TEHRANPDB SQL> REVOKE CREATE VIEW FROM DB_DEVELOPER_ROLE; ORA-65092: system privilege granted with a different scope to 'DB_DEVELOPER_ROLE' SQL> SHOW CON_NAME CON_NAME ----------------- CDB$ROOT SQL> REVOKE CREATE VIEW FROM DB_DEVELOPER_ROLE CONTAINER=ALL; Revoke succeeded. Additional privileges can be granted or revoked at the PDB level if necessary SQL> GRANT SELECT ANY TABLE TO DB_DEVELOPER_ROLE; Grant succeeded. SQL> SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE grantee='DB_DEVELOPER_ROLE' AND PRIVILEGE LIKE 'SELECT%'; PRIVILEGE -------------------- SELECT ANY TABLE SQL> REVOKE SELECT ANY TABLE FROM DB_DEVELOPER_ROLE; Revoke succeeded. This new role simplifies privilege management for developers, offering a balanced and efficient starting point for application development.

May 9, 2025 - 16:09

DB_DEVELOPER_ROLE Role in Oracle Database 23ai

When a database administrator creates a new user for developers, they may struggle to decide which privileges to grant. This is to ensure the user has the minimum required access to start building applications.

Traditionally, many administrators grant the CONNECT and RESOURCE roles to the user. These roles enable developers to connect to the database and perform essential actions, such as creating tables, views, triggers, and more.

Privileges in CONNECT and RESOURCE Roles
Below is an example of the privileges granted by these roles in Oracle Database 19c:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> CREATE USER usef IDENTIFIED BY a;
User created.

SQL> GRANT connect, resource TO usef;
Grant succeeded.

SQL> CONNECT usef/a@target:1521/PDBTARGET
Connected.

SQL> SELECT * FROM session_privs;
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
SET CONTAINER
10 rows selected.

Changes in Oracle 12c
In Oracle 12c, the RESOURCE role underwent a significant change with the removal of the UNLIMITED TABLESPACE privilege. Users with only the RESOURCE role cannot create segments. Additionally, the SET CONTAINER privilege was added to the CONNECT role, but it is irrelevant for local users.

Enhancements in Oracle 23ai
Until Oracle 23ai, there were no substantial changes to the privileges in these roles. However, in Oracle 23ai, the number of privileges granted by the RESOURCE role increased from 8 to 17. Here’s an example:

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL> CREATE USER usef IDENTIFIED BY a;
User created.

SQL> GRANT connect, resource TO usef;
Grant succeeded.

SQL> CONNECT usef/a@target:1521/pdb1
Connected.

SQL> SELECT * FROM session_privs;
CREATE PROPERTY GRAPH
CREATE ANALYTIC VIEW
CREATE HIERARCHY
CREATE ATTRIBUTE DIMENSION
SET CONTAINER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE TYPE
CREATE MATERIALIZED VIEW
CREATE TRIGGER
CREATE PROCEDURE
CREATE SEQUENCE
CREATE VIEW
CREATE SYNONYM
CREATE CLUSTER
CREATE TABLE
CREATE SESSION
17 rows selected.

Introduction of DB_DEVELOPER_ROLE
Oracle 23ai introduces a new role, DB_DEVELOPER_ROLE, designed as a replacement for CONNECT and RESOURCE. This role includes essential privileges required for developers and is tailored to meet their needs at the starting point.

If the DB_DEVELOPER_ROLE is granted, the user receives the following system privileges:

SQL> CREATE USER usef IDENTIFIED BY a;
User created.

SQL> GRANT DB_DEVELOPER_ROLE TO usef;
Grant succeeded.

SQL> CONNECT usef/a@target:1521/pdb1
Connected.

SQL> SELECT * FROM session_privs;
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.

Additional Notes
1.Quota Management
Users with DB_DEVELOPER_ROLE still need specific tablespace quotas or UNLIMITED TABLESPACE privileges to create segments:

SQL> CREATE TABLE tb AS SELECT * FROM dual;
ORA-01950: The object owner 'USEF' has insufficient quota on tablespace 'SYSTEM'.

2. Privilege Modifications

Privileges granted to DB_DEVELOPER_ROLE cannot be revoked at the PDB level. To revoke a privilege, you must connect to the root container:

SQL> SHOW CON_NAME
CON_NAME
------------------
TEHRANPDB
SQL> REVOKE CREATE VIEW FROM DB_DEVELOPER_ROLE;
ORA-65092: system privilege granted with a different scope to 'DB_DEVELOPER_ROLE'

SQL> SHOW CON_NAME
CON_NAME
-----------------
CDB$ROOT
SQL> REVOKE CREATE VIEW FROM DB_DEVELOPER_ROLE CONTAINER=ALL;
Revoke succeeded.

Additional privileges can be granted or revoked at the PDB level if necessary

SQL> GRANT SELECT ANY TABLE TO DB_DEVELOPER_ROLE;
Grant succeeded.

SQL> SELECT PRIVILEGE FROM DBA_SYS_PRIVS 
     WHERE grantee='DB_DEVELOPER_ROLE' AND PRIVILEGE LIKE 'SELECT%';
PRIVILEGE
--------------------
SELECT ANY TABLE

SQL> REVOKE SELECT ANY TABLE FROM DB_DEVELOPER_ROLE;
Revoke succeeded.

This new role simplifies privilege management for developers, offering a balanced and efficient starting point for application development.