When you have developers, sometimes it’s challenging to determine just what roles they need. If you’re not careful, you’ll over privilege an account and just as anoying you may wind up under privilege an account and need to go back and keep adjusting it until you get it right.
In Oracle 23c we now have the DB_DEVELOPER_ROLE that will give developers all the privileges they need.
In the following example, we’re going to create a user test1 and grant they DB_DEVELOPER_ROLE and see just what all they got.
SQL> create user test1 identified by test1;
User TEST1 created.
SQL> grant unlimited tablespace to test1;
Grant succeeded.
SQL> grant db_developer_role to test1;
Grant succeeded.
SQL> conn test1/test1
Connected.
SQL> select granted_role from user_Role_privs;
GRANTED_ROLE
____________________
DB_DEVELOPER_ROLE
SQL> SELECT PRIVILEGE FROM ROLE_SYS_PRIVS WHERE ROLE='DB_DEVELOPER_ROLE';
PRIVILEGE
_____________________________
CREATE DOMAIN
CREATE MLE
CREATE ANALYTIC VIEW
CREATE HIERARCHY
CREATE ATTRIBUTE DIMENSION
EXECUTE DYNAMIC MLE
CREATE CUBE BUILD PROCESS
CREATE CUBE
CREATE CUBE DIMENSION
CREATE MINING MODEL
CREATE JOB
DEBUG CONNECT SESSION
ON COMMIT REFRESH
CREATE DIMENSION
CREATE TYPE
CREATE MATERIALIZED VIEW
CREATE TRIGGER
CREATE PROCEDURE
FORCE TRANSACTION
CREATE SEQUENCE
CREATE VIEW
CREATE SYNONYM
CREATE TABLE
CREATE SESSION
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
DBA_PENDING_TRANSACTIONS
V_$STATNAME
V_$PARAMETER