Controlling Role Access in Oracle AI Database 26ai

Roles provide a practical way to group related privileges and assign them to users or applications. A reporting role might contain permission to read approved views, while an order-processing role might contain permission to execute a package that implements business rules. Changing the privileges in the role changes the authorization supplied by that role without requiring the administrator to repeat the same grants for every user.

Limiting access to a role requires more precision than simply saying that the role is disabled. An administrator must determine whether the role should be removed from the user, excluded from the roles enabled at logon, disabled temporarily in one session, or activated only after trusted application checks. Oracle AI Database 26ai provides a different control for each requirement.

Earlier versions of SQL*Plus offered a product user profile that attempted to suppress roles and commands in that client. That product-level security feature was desupported beginning with Oracle Database 19c. It was never equivalent to removing a database authorization because SQL*Plus, rather than Oracle Database, enforced the restriction. A modern design therefore controls roles in the database, where the rules apply to SQL*Plus, SQLcl, SQL Developer, JDBC applications, connection pools, and other access paths.

Roles Group Related Privileges

A database role is a named collection of privileges and, where permitted, other roles. Creating a role does not give it any privileges. A security administrator first creates the role, grants it only the privileges required for its purpose, and then grants the role to the identities that need it. The following example creates a local reporting role in the current pluggable database:

CREATE ROLE order_reporter;

GRANT SELECT ON app_owner.order_summary
  TO order_reporter;

GRANT order_reporter
  TO reporting_user;

The example assumes that app_owner.order_summary already exists and that the administrator issuing the statements has the necessary grant authority. The role supplies only the privileges placed inside it. It does not automatically confer ownership of the object, a broad SELECT ANY TABLE privilege, or access to unrelated application data.

Roles should correspond to responsibilities such as reporting, order entry, or application administration rather than to individual people. A narrowly designed role is easier to understand, test, audit, and revoke. Avoid adding broad ANY privileges merely for convenience because one such privilege can expose objects in many schemas.

Grant or Revoke the Role Entitlement

A role grant establishes that the role is available to its grantee. If reporting_user must no longer possess the reporting role, revoke the grant:

REVOKE order_reporter
  FROM reporting_user;

This statement removes one authorization path; it does not create a deny rule. The user may still be able to query the same view through a direct object grant, another enabled role, a role hierarchy, a grant to PUBLIC, or another applicable privilege path. Before declaring the access removed, inspect every path that could supply the equivalent privilege.

Grant and revoke timing also matters. Grants and revokes of system or object privileges take effect immediately. A role granted to or revoked from a user affects a current session after the session uses SET ROLE to refresh its enabled roles, or when the user creates a new session. Test role changes with a newly authenticated session instead of assuming that an existing pooled or long-running session immediately represents the new design.

Choose Which Roles Are Enabled at Logon

When a user logs on, Oracle enables privileges granted directly to the user and privileges supplied through the user's default roles. After order_reporter has been granted directly to reporting_user, an administrator can make it the role enabled automatically at logon:

ALTER USER reporting_user
  DEFAULT ROLE order_reporter;

An administrator can instead begin new sessions with no default roles or enable all ordinary granted roles except a specified role:

ALTER USER reporting_user DEFAULT ROLE NONE;

ALTER USER reporting_user
  DEFAULT ROLE ALL EXCEPT maintenance_role;

Changing the default-role list is not the same as revoking a role. With DEFAULT ROLE NONE, ordinary roles can remain granted even though a new session does not enable them automatically. The user or an application may enable an available role later when its authorization method permits. Use a revoke when the user must not possess the entitlement at all.

The DEFAULT ROLE clause can designate only roles granted directly to the user or roles created by that user. It cannot make an indirectly granted role, an externally managed role, a password-authenticated role, or a secure application role into a default role. Oracle permits no more than 148 user-defined roles to be enabled in a session, including roles reached through role hierarchies, but least privilege should guide role design long before that limit becomes the main concern.

Change Enabled Roles for the Current Session

During a session, a user or application can use SET ROLE to change which available roles are enabled. These statements affect the current session; they do not grant roles, revoke roles, or change the user's default-role configuration:

SET ROLE NONE;
SET ROLE order_reporter;
SET ROLE ALL EXCEPT maintenance_role;

SET ROLE NONE disables every role, including default roles, for that session. Direct privileges remain available because they are not supplied by a role. Naming order_reporter enables that role and disables other roles not named in the statement. SET ROLE ALL EXCEPT maintenance_role enables all eligible roles except the listed role.

The ALL EXCEPT form has an important role-hierarchy boundary. A role listed after EXCEPT must have been granted directly to the user. If the same role is also inherited through another enabled role, excluding the direct grant does not eliminate that indirect path. Furthermore, SET ROLE ALL cannot enable password-authenticated roles or secure application roles.

For these reasons, SET ROLE ALL EXCEPT is a session-selection tool rather than a durable access restriction. If an ordinary granted role must never be available to the user, revoke it and inspect the remaining privilege paths. If the privileges should become available only after policy checks, use a secure application role.

Use Secure Application Roles for Conditional Activation

A secure application role is appropriate when an application must validate trusted session conditions before enabling a set of privileges. Instead of placing a shared role password in application code, the administrator associates the role with an authorized PL/SQL package or procedure:

CREATE ROLE order_approver
  IDENTIFIED USING security_admin.role_guard;

The role should receive only the privileges required for order approval. The application invokes the authorized policy code, which evaluates the security conditions and uses the documented role-management interface to enable the role. Oracle verifies that the authorized package or procedure is on the calling stack. The secure application role cannot be a default role, cannot be enabled through SET ROLE ALL, and must not be enabled from a logon trigger.

The package is part of the security boundary and requires careful design. A client-supplied program name, module name, IP address, or client identifier does not prove identity merely because the application can set it. A production design must establish trusted identity through appropriate authentication, proxy or middle-tier configuration, protected application context, and controlled package ownership. Connection pools must also clear or reestablish security context when a physical connection is reused for another application user.

Password-authenticated roles still exist, but distributing a shared role password creates handling and maintenance concerns. When activation depends on application policy, Oracle recommends secure application roles instead of embedding role passwords in applications or scripts.

Account for PDB and CDB Scope

Oracle AI Database 26ai uses the multitenant architecture, so the container in which a role is created and granted affects its scope. A local role exists only in one PDB. The order_reporter example is local when it is created while connected to a PDB and the default CONTAINER=CURRENT behavior is used.

A common role is created in a root and is known throughout the applicable CDB or application container. Common roles follow additional naming, grant-scope, and CONTAINER rules. A common role can contain privileges that are common across containers as well as privileges that are local to particular containers. Therefore, seeing the same role name in multiple PDBs does not by itself prove that the same privileges are available in each PDB.

Before changing a role, identify the current container, whether the role and user are local or common, and whether the original grant was made with CONTAINER=CURRENT or CONTAINER=ALL. A locally issued revoke does not automatically remove a separate common grant.

Verify Granted and Enabled Roles

Verification must distinguish roles configured for the identity from roles enabled in the current session. A user can examine directly granted roles and their default status with USER_ROLE_PRIVS, then compare that list with SESSION_ROLES:

SELECT granted_role, default_role
FROM   user_role_privs
ORDER  BY granted_role;

SELECT role
FROM   session_roles
ORDER  BY role;

USER_ROLE_PRIVS answers which roles are granted directly and which are designated as defaults. SESSION_ROLES answers which roles are currently enabled. After SET ROLE NONE, for example, the first query can still list granted roles while the second query returns no enabled roles.

Administrators with appropriate data dictionary access can use DBA_ROLE_PRIVS to inspect direct role grants and DBA_ROLES to inspect roles and their authentication types. ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, and ROLE_TAB_PRIVS help reveal the privileges and nested roles contained in roles visible to the querying user. SESSION_PRIVS lists system privileges currently available to the session through direct grants and enabled roles.

Dictionary results must be interpreted in the correct container and with awareness of the querying identity's visibility. One row in DBA_ROLE_PRIVS does not establish the complete effective privilege domain. Test the permitted and prohibited operations through every relevant application and direct-client path. Privilege analysis can help identify used and unused privileges during representative workloads, while unified auditing can record configured security activity. These tools provide evidence; they do not themselves prevent a role from being enabled.

Database Roles and Deep Data Security Data Roles

Oracle AI Database 26ai also introduces data roles as part of Oracle Deep Data Security. A Deep Data Security data role is not another name for an ordinary database role. Data roles organize fine-grained data grants for Deep Data Security end users and application identities, and they can receive standard database roles. Data grants can control access at row, column, and cell level.

Ordinary database users and database roles cannot be named directly as grantees of a Deep Data Security data grant. Externally mapped data roles also require provider-specific mappings, such as a Microsoft Entra ID role or an OCI IAM group. Use that model only after the application identity, end-user context, provider mapping, and data-grant design have been established. It is a separate authorization architecture, not a mechanical replacement for GRANT order_reporter TO reporting_user.

Select the Control That Matches the Requirement

Effective role control begins by defining the required state: granted, enabled by default, enabled temporarily, or enabled conditionally. Applying the corresponding database control creates consistent authorization across clients and avoids relying on obsolete SQL*Plus product-profile restrictions. In the next lesson, you will examine how supported authorization rules can be applied consistently across multiple users, roles, schemas, and containers.

Product Profiles - Quiz

The product profiles quiz is retained as separate non-workflow material for this module.

Product Profiles - Quiz