04-04-2024
1 november 2023
When you start working with an Oracle Database, one of the first steps you (or your DBA) need to take, is create a schema or user. A normal user gets minimal privileges, according to the Least Privileges Principle. However, during application development, the developer often require more privileges than just the connect and resource roles. This often sparkst arguments between DBAs and developers. The developer wants to get full privileges on the database so they can perform the development work with as least trouble as possible. The DBA fears that the developers might inadvertently break their database.
To put an end to (most of) these discussions, Oracle Database 23c provides us with the db_developer_role which contains enough privileges to do 95% of the development without the chance of the developer breaking the database. Normally the developers get the connect and resource roles. These contain the most basic privileges:
PRIVILEGE (Connect)
----------------------------------------
CREATE SESSION
SET CONTAINER
PRIVILEGE (Resource)
----------------------------------------
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
The db_developer_role contains a different set of privileges. The privileges provided are:
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
In addition to these privileges, you are also granted the roles CTXAPP and RESOURCE, and some direct grants, including EXECUTE DBMS_REDACT, EXECUTE DBMS_RLS, EXECUTE DBMS_TSDP_MANAGE, EXECUTE DBMS_TSDP_PROTECT, EXECUTE JAVASCRIPT, READ V_$PARAMETER, READ V_$STATNAME and select DBA_PENDING_TRANSACTIONS.
Schema Level Privileges
Besides the new db_developer_role Oracle Database 23c also provides a new way of granting privileges on objects in a schema to another schema. Instead of granting, for instance, select rights on a table to the other schema, you can now for instance grant select any table on schema <owner> to <grantee> which means the grantee can select from any table in the owner schema. You could always grant select any table to <grantee> but that means grantee could select from any table in the entire database. This privilege just grants select on the specified schema and nothing else.
Another advantage of granting schema level privileges is that when you create a new object, the same privileges automatically apply to these new objects.
Also read my 'Introduction to Oracle Database 23c' and my next blog: Group by Alias.
Auteur: Patrick Barel
