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.
0 comments:
Post a Comment