DBA Hub

📋Steps in this guide1/6

DB_DEVELOPER_ROLE Role in Oracle Database 23ai/26ai

Oracle database 23ai/26ai introduced a new DB_DEVELOPER_ROLE role to provide the basic roles and privileges Oracle believe are necessary for a database developer.

oracle 23configurationintermediate
by OracleDba
21 views
1

DB_DEVELOPER_ROLE Role Basics

Oracle database 23ai/26ai introduced a new 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 and roles as a starting point for most developers, but the role is more than the sum of these two roles, as we will see below. The role is granted and revoked in the normal way.

Code/Command (click line numbers to comment):

1
2
3
4
5
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

grant db_developer_role to testuser1;

revoke db_developer_role from testuser1;
2

Comparing CONNECT, RESOURCE and DEVELOPER

The following three sections display the system, role and object privileges associated with each role.
3

CONNECT Role

Here are the system, role and object privileges associated with the role.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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>
4

RESOURCE Role

Here are the system, role and object privileges associated with the role. There are seven more system privileges granted to the role in 23ai/26ai compared to 19c.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
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>
5

DB_DEVELOPER_ROLE Role

Here are the system, role and object privileges associated with the role.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
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 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

13 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
RESOURCE

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                        DBMS_REDACT
EXECUTE                        DBMS_RLS
EXECUTE                        DBMS_TSDP_MANAGE
EXECUTE                        DBMS_TSDP_PROTECT
EXECUTE                        JAVASCRIPT
READ                           V_$PARAMETER
READ                           V_$STATNAME
SELECT                         DBA_PENDING_TRANSACTIONS

SQL>
6

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 role has more privileges than the and the 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 role means people who are in the habit of granting the plus roles are getting something approaching the level of privilege granted by the . It is worth considering the impact of this. For more information see: - Use of the DB_DEVELOPER_ROLE Role for Application Developers Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!