Wednesday, April 19, 2023

DB_DEVELOPER_ROLE Role in Oracle Database 23c

Oracle Database 23c, Oracle Database Certification, Oracle Database Prep, Oracle Database Preparation, Oracle Database Learning, Oracle Database Guides, Oracle Database Skills, Oracle Database Jobs

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

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.

Related Posts

0 comments:

Post a Comment