Lesson 9	Granular Object and System Privileges
Objective	Role based access control (RBAC), Database Vault in Oracle 26ai

Alternatives to the Desupported SQL*Plus Product User Profile

The SQL*Plus Product User Profile (PRODUCT_USER_PROFILE / SQLPLUS_PRODUCT_PROFILE) was desupported starting in Oracle Database 19c and should not be relied on for security in Oracle 23c, 23ai, or 26ai. The legacy pupbld.sql script still exists in $ORACLE_HOME/sqlplus/admin for compatibility, and the PRODUCT_USER_PROFILE table can still technically disable commands like SET ROLE inside a SQL*Plus session — but the enforcement happens entirely on the client side, in SQL*Plus itself, not inside the database. A user restricted in SQL*Plus can simply connect with a different tool and exercise every privilege they were actually granted.

Oracle's guidance instead points toward three native, server-side mechanisms that enforce least privilege consistently, no matter which client connects: SQL*Plus, SQLcl, JDBC, or anything else. This lesson covers all three — granular privileges, role-based access control, and Oracle Database Vault — and, along the way, finally defines a concept this course has been leaning on since Lesson 6 without ever stating outright: what a CDB actually is.

A Quick CDB Primer

A CDB is a Container Database — more fully, a Multitenant Container Database — the architecture Oracle introduced in Database 12c and, since Database 21c, the only supported architecture. Earlier releases supported a traditional single-database structure (a "non-CDB"); that option no longer exists starting with 21c.

A CDB is made up of three kinds of containers:

One root container (CDB$ROOT) — holds Oracle-maintained objects, data structures, and common users. This is what earlier lessons meant by "the CDB root."
One seed PDB (PDB$SEED) — a system-supplied template used whenever you create a new PDB. It's never used directly for application data.
Zero or more pluggable databases (PDBs) — each one a portable collection of schemas and objects that appears to an application as an entirely separate database, even though it's sharing the CDB's underlying resources.

This distinction — root vs. seed vs. PDB — is exactly why Lessons 6 through 8 kept asking "which container does this apply to?" for password changes, catalog scripts, and PL/SQL packages. It resurfaces again in this lesson, in two different forms, when we get to Database Vault's realms and factors.

1. Granular Object and System Privileges

Oracle provides fine-grained control through two privilege categories: object privileges (SELECT, INSERT, UPDATE, DELETE, EXECUTE on specific tables, views, procedures, or packages) and system privileges (CREATE TABLE, CREATE SESSION, ALTER SYSTEM, DROP ANY TABLE).

Grant only the exact privileges a user needs — never broad ones such as SELECT ANY TABLE or DBA unless there's a genuine, specific reason.
Privileges can be granted directly to users or, preferably, to roles (covered next).
Starting with Oracle AI Database 26ai, schema privileges let you grant a system privilege scoped to a single schema instead of database-wide. Grant CREATE ANY TABLE restricted to just the HR schema, for instance, rather than every schema in the database. Five new views support this: DBA_SCHEMA_PRIVS, ROLE_SCHEMA_PRIVS, USER_SCHEMA_PRIVS, SESSION_SCHEMA_PRIVS, and V$ENABLEDSCHEMAPRIVS.
This approach is fundamentally more reliable than Product User Profile because enforcement happens inside the database kernel, not in a client tool. A user cannot bypass a missing privilege simply by switching applications.

Best practice: regularly review and revoke excess privileges using privilege analysis features or a tool such as Oracle Data Safe.

2. Role-Based Access Control (RBAC)

Roles group privileges into logical, named sets that can be granted to users — or to other roles. This is the foundation of manageable least-privilege security, and it's what actually replaces Product User Profile's crude ability to disable a role or command inside SQL*Plus.

Create application-specific roles (APP_READ_ONLY, APP_DATA_ENTRY, APP_ADMIN) that contain only the privileges each group of users actually needs.
Grant roles to users instead of granting privileges directly. Auditing, revocation, and changes all become far easier when you're managing a handful of roles instead of hundreds of individual grants.
Use secure application roles for anything sensitive. A secure application role can only be enabled by an authorized PL/SQL package — when the role is enabled, Oracle checks that the authorized package is actually on the calling stack before allowing it. That package can validate connection details (IP address, whether the session came through a specific middle tier, and so on) before granting the role. Two restrictions worth knowing: a secure application role cannot be enabled through a logon trigger, and it cannot be made a default role.
Password-protected roles are a related but distinct mechanism — here, an application supplies a password when it enables the role, so a user connecting through an ad hoc tool who doesn't know the password can't enable it themselves.
In a multitenant environment, roles are either local (created in, and confined to, a single PDB) or common (created in the CDB root or an application root, and automatically visible in every PDB underneath it).

Between granular privileges and well-designed roles, you get an authorization model that's auditable, consistent across every connection method, and enforced by the database itself rather than by whichever client happens to be connecting. For most day-to-day access control, this is as far as you need to go. Oracle Database Vault, covered next, is what you reach for when "most day-to-day access control" isn't strong enough — specifically, when you need to restrict even your most privileged users.

3. Oracle Database Vault

Database Vault is a separately licensed Enterprise Edition option, and it exists to solve a problem granular privileges and roles can't: restricting privileged users themselves, including DBAs and anyone holding powerful system privileges like SELECT ANY TABLE or SYSDBA. It operates inside the database kernel, after normal privilege checks, which means it cannot be bypassed by connecting through a different client tool — the same property that makes granular privileges superior to Product User Profile, applied one level higher, against your own administrators.

Realms: Two Types, Not One

A realm is a protection boundary around a group of schemas, objects, or roles — think of it as a zone that requires explicit authorization to enter, regardless of what other privileges a user holds. There are two kinds, and the difference matters:

Regular realm — an object owner, or a user with a direct object grant, can still run queries and DML without realm authorization. Only DDL, and access based on a system privilege like SELECT ANY TABLE, requires it.
Mandatory realm — blocks everything without explicit realm authorization, full stop. Even the object's own owner cannot query it without being an authorized participant in the realm.

Mandatory realms are what people usually picture when they hear "Database Vault protects data from the DBA" — and they're right, but only for that stronger mode. Creating one looks like this:

BEGIN
  DBMS_MACADM.CREATE_REALM(
    realm_name    => 'HR Realm',
    description   => 'Realm to protect the HR schema',
    enabled       => DBMS_MACUTL.G_YES,
    audit_options => DBMS_MACUTL.G_REALM_AUDIT_OFF,
    realm_type    => DBMS_MACADM.MANDATORY_REALM,
    realm_scope   => DBMS_MACUTL.G_SCOPE_LOCAL,
    pl_sql_stack  => TRUE);
END;
/

At this point the realm exists but protects nothing — a separate call to DBMS_MACADM.ADD_OBJECT_TO_REALM adds the actual tables, views, or roles you want covered.

The CDB-Scope Rule for Realms and Factors

Here's where the CDB primer from earlier pays off. Database Vault has its own container restrictions, and they run in opposite directions for two different features:

A common realm can only be created in an application root, never in the CDB root. This lets you protect common objects for every PDB under that application root from one central place, without having to build the same realm separately in each PDB. Configuring one requires the DV_OWNER or DV_ADMIN role, granted commonly.
A factor (covered below) can only be created in a PDB — never in the CDB root or an application root.

Same underlying CDB architecture, two mirror-image rules. It's worth pausing on this rather than treating it as trivia: it's a direct, practical consequence of the container model this course introduced back in Lesson 6, showing up in a security context now.

Command Rules

A command rule protects a specific SQL statement — SELECT, DDL such as DROP TABLE or TRUNCATE TABLE, ALTER SYSTEM, CREATE USER, and more. Rather than embedding logic directly in the command rule, you associate it with a rule set: a named collection of one or more rules that gets evaluated at run time.

The evaluation order matters: Database Vault checks realm authorization first. Only if there's no realm violation does it move on to evaluate the command rule's rule set. If every rule in the set evaluates to TRUE, the statement is allowed to proceed; if any rule evaluates to FALSE, a command rule violation is raised and the statement is blocked. Command rules themselves fall into three scopes: system-wide (CONNECT or ALTER SYSTEM, typically one per database instance), schema-specific (a DROP TABLE rule for one particular schema), or object-specific (DROP TABLE for one named table).

A simplified example, restricting ALTER SYSTEM based on a rule set:

BEGIN
  DBMS_MACADM.CREATE_COMMAND_RULE(
    command        => 'ALTER SYSTEM',
    rule_set_name  => 'Check RESTRICTED SESSION for TRUE',
    object_owner   => '%',
    object_name    => '%',
    enabled        => DBMS_MACUTL.G_YES,
    clause_name    => 'SECURITY',
    parameter_name => 'RESTRICTED SESSION',
    scope          => DBMS_MACUTL.G_SCOPE_LOCAL);
END;
/

Factors

A factor is a named variable or attribute — a database IP address, for example — that Database Vault can recognize and use in rule sets, either to authorize connections or to build filtering logic. Oracle supplies a set of default factors out of the box (domain, IP address, database name, and others). For the most commonly needed values — Session_User, Proxy_User, Network_Protocol, Module — Oracle actually recommends using the SYS_CONTEXT function directly in your rule definitions rather than creating a new factor that duplicates something already available. Custom factors, built with your own PL/SQL retrieval logic, are reserved for cases SYS_CONTEXT doesn't already cover.

BEGIN
  DBMS_MACADM.CREATE_FACTOR(
    factor_name      => 'Sector2_DB',
    factor_type_name => 'Instance',
    description      => 'Factor to restrict DBA access',
    rule_set_name    => 'Limit_DBA_Access',
    get_expr         => 'UPPER(SYS_CONTEXT(''USERENV'',''DB_NAME''))',
    validate_expr    => 'dbavowner.check_db_access',
    identify_by      => DBMS_MACUTL.G_IDENTIFY_BY_METHOD,
    labeled_by       => DBMS_MACUTL.G_LABELED_BY_SELF,
    eval_options     => DBMS_MACUTL.G_EVAL_ON_SESSION,
    audit_options    => DBMS_MACUTL.G_AUDIT_OFF,
    fail_options     => DBMS_MACUTL.G_FAIL_SILENTLY);
END;
/

Remember the scope rule from above: this only runs inside a PDB — attempting it from the CDB root or an application root will fail.

Trusted Path: A Pattern, Not a Feature

"Trusted path" doesn't refer to a distinct Database Vault component with its own configuration screen. It's the name for a pattern: combining out-of-the-box factors — IP address, authentication method, program name — with rule sets to authorize connections and deter attacks built on stolen credentials. If a login looks legitimate on username and password alone but arrives from an unexpected program or network path, a trusted-path rule set built from these factors is what catches it.

Separation of Duties

Database Vault ships with a family of built-in roles that split administrative power across different people, so no single account can both configure security policy and audit it. The two the legacy version of this lesson already named are accurate:

DV_OWNER — manages Database Vault's own roles and configuration.
DV_ACCTMGR — manages user accounts within a Database Vault environment.

Several more exist worth knowing about: DV_ADMIN (day-to-day use of the Database Vault PL/SQL packages), DV_MONITOR (lets Enterprise Manager Cloud Control watch for realm or command rule violations), DV_SECANALYST (report analysis), DV_POLICY_OWNER, and DV_PATCH_ADMIN, among others. The point isn't to memorize the full list — it's that Database Vault treats "security administrator," "account manager," and "auditor" as genuinely separate jobs, enforced by separate roles, rather than trusting one all-powerful DBA account to self-police.

Simulation Mode: Testing Before Enforcing

Both realms and command rules support a simulation mode: violations get logged, not blocked, so you can see exactly what a new policy would have stopped before you actually turn it on. This is the recommended way to roll out anything Database Vault-related in production, and Oracle's own workflow for it is concrete enough to walk through directly:

Put the new realm (in mandatory mode) or command rule into production in simulation mode.
Let normal application and development traffic run against it for a representative period.

Query the simulation log for what would have been blocked:

SELECT USERNAME, COMMAND, SQLTEXT, VIOLATION_TYPE
FROM DBA_DV_SIMULATION_LOG
WHERE REALM_NAME = 'HR Apps';

USERNAME   COMMAND   SQLTEXT                            VIOLATION_TYPE
--------   -------   ---------------------------------  ---------------
DGRANT     SELECT    SELECT SALARY FROM HR.EMPLOYEES;    Realm Violation

Use what the log reveals — both unexpected violations and the system context values you'll need for trusted-path rule sets — to adjust the realm or command rule and add authorized users.
A user with the DV_ADMIN or DV_OWNER role clears the log (by deleting from the underlying DVSYS.SIMULATION_LOG$ table) and re-runs traffic to confirm the adjustments hold.
Only then is the realm or command rule switched from simulation mode to fully enabled.

Note that realm names are case-sensitive when querying DBA_DV_SIMULATION_LOG — 'HR Apps' and 'hr apps' are not the same filter.

Summary Recommendation

Use granular privileges and well-designed roles as your everyday baseline — that combination alone already outperforms Product User Profile on every axis that matters, because enforcement lives in the database kernel rather than in a client tool. Layer Oracle Database Vault on top whenever you need to go further: protecting data from privileged users themselves, enforcing separation of duties, or tightly controlling specific SQL commands. When you do, remember that realms come in two strengths (only mandatory realms lock out the object owner), that common realms and factors follow opposite CDB-container rules, and that simulation mode is how you find that out safely, before enforcement is switched on for real.

Create Product User Profile-Exercises

Create Product User Profile-Exercise
Now, those of you who are running Oracle should go on to this exercise, to create the product user profile in your database.