8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 23c » Here

DB_DEVELOPER_ROLE Role in Oracle Database 23c

Oracle database 23c 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 23c 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 DEVELOPER

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 23c 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 ANALYTIC VIEW
CREATE ATTRIBUTE DIMENSION
CREATE CUBE
CREATE CUBE BUILD PROCESS
CREATE CUBE DIMENSION
CREATE DIMENSION
CREATE DOMAIN
CREATE HIERARCHY
CREATE JOB
CREATE MATERIALIZED VIEW
CREATE MINING MODEL
CREATE MLE
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
DEBUG CONNECT SESSION
EXECUTE DYNAMIC MLE
FORCE TRANSACTION
ON COMMIT REFRESH

24 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
SODA_APP

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                        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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.