How to Use DB_DEVELOPER_ROLE in Oracle

For years, developers were given CONNECT and RESOURCE, or worse DBA, because no role matched what a developer needs. DB_DEVELOPER_ROLE, new in Oracle AI Database 26ai, contains the privileges an application developer needs to build in their own schema, such as tables, views, sequences, procedures, types, domains, assertions, jobs, and JavaScript code, and nothing more.

Code for This Guide

The main examples are in the examples/security folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Grant It

Example:

grant db_developer_role to developer_user;

An administrator grants the role once, and the developer can create the usual application objects in their own schema.

What It Contains

The system privileges granted directly to the role:

Example:

select privilege from dba_sys_privs where grantee = 'DB_DEVELOPER_ROLE' order by privilege;

Output:

PRIVILEGE
____________________________
CREATE ASSERTION
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

14 rows selected.

It also includes other roles, which bring the classic object-creation privileges:

Example:

-- roles granted to DB_DEVELOPER_ROLE, and whether creating tables comes with it
select granted_role from dba_role_privs where grantee = 'DB_DEVELOPER_ROLE' order by granted_role;

select privilege from dba_sys_privs
where  grantee in (select granted_role from dba_role_privs where grantee = 'DB_DEVELOPER_ROLE')
and    privilege like 'CREATE %'
order  by privilege
fetch  first 12 rows only;

Output:

GRANTED_ROLE
_______________
CTXAPP
RESOURCE

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 SEQUENCE
CREATE SYNONYM

12 rows selected.

Through RESOURCE come CREATE TABLE, CREATE PROCEDURE, CREATE SEQUENCE, and the other object privileges; CTXAPP adds Oracle Text. The two queries run as SYS.

Check Your Own Privileges

SESSION_ROLES and SESSION_PRIVS show the roles and system privileges in effect for the current session, without administrator rights.

Example:

select role from session_roles order by role;

select count(*) as system_privileges from session_privs;

Output:

ROLE
_______________________
CTXAPP
DB_DEVELOPER_ROLE
HS_ADMIN_SELECT_ROLE
RESOURCE
SELECT_CATALOG_ROLE
SODA_APP

6 rows selected.

   SYSTEM_PRIVILEGES
____________________
                  38

The NIMBUS user has DB_DEVELOPER_ROLE, the roles it includes, and a couple of others, for 38 system privileges in all.

Things to Know

• DB_DEVELOPER_ROLE does not grant ANY privileges on other schemas, nor administration privileges such as CREATE USER.
• Quota on a tablespace is still needed to create tables with data: ALTER USER ... QUOTA.
• Roles are not active inside definer's rights PL/SQL; objects referenced there need direct grants.

Related Guides

• How to Create Users with Profiles in Oracle
• How to Grant Schema Privileges in Oracle

Conclusion

DB_DEVELOPER_ROLE gives developers the privileges to build application objects in their own schema, and no more. Grant it instead of CONNECT, RESOURCE, or DBA, and check what a session holds with SESSION_ROLES and SESSION_PRIVS.