One of the nice new features in Oracle Database 23c is the new role called DB_DEVELOPER_ROLE.
Rather than grant and revoke individual privileges to an application developer, we can just grant the single DB_DEVELOPER_ROLE role which contains (most of) the common and necessary privileges to design, develop, and deploy applications.
Very often audits find that the developers have been granted the DBA role or more elevated privileges that what are required for application development.
So in Oracle Database 23c granting the single new role DB_DEVELOPER_ROLE adheres to the principle of least-privilege and ensures greater security for the development environment. It also facilitates the easier and more simpler management of role grants and revokes for application users.
Let us see an example of this new 23c feature.
Create the application development user and grant the DB_DEVELOPER_ROLE.
SQL> create user SAMI identified by Oracle_4U; User created. SQL> grant db_developer_role to SAMI; Grant succeeded. SQL> alter user SAMI quota unlimited on users; User altered.
We can connect as the application development user (even though CONNECT or CREATE SESSION has not been granted) and create objects like table, view and procedure.
The objects are created even though no explicit CREATE privilege has been granted to the user.
[oracle@localhost ~]$ sqlplus SAMI/Oracle_4U@freepdb1
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Wed May 20 22:22:22 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Last Successful login time: Wed May 20 2023 22:21:20 +00:00
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> create table mytab as select * from all_tables;
Table created.
SQL> create view myview as select owner,object_name from all_objects;
View created.
SQL> CREATE OR REPLACE PROCEDURE myproc
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
/
Procedure created.Note the privileges and roles which have been granted to the DB_DEVELOPER_ROLE.
SQL> SELECT PRIVILEGE FROM ROLE_SYS_PRIVS WHERE ROLE='DB_DEVELOPER_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> SELECT GRANTED_ROLE FROM ROLE_ROLE_PRIVS WHERE ROLE='DB_DEVELOPER_ROLE'; GRANTED_ROLE ----------------------- SODA_APP CTXAPP SQL> SELECT TABLE_NAME FROM ROLE_TAB_PRIVS WHERE ROLE='DB_DEVELOPER_ROLE'; TABLE_NAME ----------------------- JAVASCRIPT V_$STATNAME V_$PARAMETER DBA_PENDING_TRANSACTIONS
Although the documentation states that we cannot grant or revoke privileges to the DB_DEVELOPER_ROLE (“It can only be modified during an Oracle Database upgrade or downgrade”) – I found that we are able to grant additional privileges to the role.
SQL> grant create user to db_developer_role; Grant succeeded. SQL> SELECT PRIVILEGE FROM ROLE_SYS_PRIVS WHERE ROLE='DB_DEVELOPER_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 USER CREATE VIEW DEBUG CONNECT SESSION EXECUTE DYNAMIC MLE FORCE TRANSACTION ON COMMIT REFRESH 25 rows selected.
