8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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...