Reporting Oracle 26ai Roles and Privileges

Lesson 6 explained how to restore approved access with GRANT, withdraw an authorization path with REVOKE, and change a SQL*Plus client restriction by relaunching the process. After any security change, the administrator must report what Oracle Database is configured to grant and verify what the relevant session can actually use.

Earlier releases could report SQL*Plus command restrictions from the Product User Profile. Oracle desupported that mechanism beginning with Oracle Database 19c. Oracle AI Database 26ai reporting therefore examines database-enforced roles, privileges, policies, and session state instead. A residual PUP object or reference in an upgraded environment does not make its product-security functionality supported or available.

Authorization reporting is not a single query. A complete investigation may need to distinguish direct grants from role grants, granted roles from enabled roles, role membership from role contents, and local grants from common grants inherited into a PDB. The report must also identify the actual database account and container used by the application.

Identify the Session and Container

Begin by recording the identity and scope of the session that runs the report. The following query distinguishes the authenticated session user from the current schema and identifies the service and container:

SELECT SYS_CONTEXT('USERENV', 'SESSION_USER')   AS session_user,
       SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') AS current_schema,
       SYS_CONTEXT('USERENV', 'SERVICE_NAME')   AS service_name,
       SYS_CONTEXT('USERENV', 'CON_NAME')       AS container_name
FROM   dual;

This context matters in a multitenant database. A local user or role belongs to one PDB, while a common user or role can exist across containers. The same grantee name can therefore produce different results depending on the service and current container. Connection pools, proxy authentication, and shared application schemas can also make the database identity different from the person using the application.

Compare Granted Roles with Enabled Roles

USER_ROLE_PRIVS describes roles granted to the current user. It shows whether each role is a default role and whether its grant includes administrative or delegation authority:

SELECT granted_role, default_role,
       admin_option, delegate_option,
       os_granted, common, inherited
FROM   user_role_privs
ORDER  BY granted_role;

A row establishes that the role has been granted; it does not prove that the role is enabled in this session. DEFAULT_ROLE='YES' means the role is designated for automatic enablement when an eligible session begins. A role can be granted but nondefault, and a session can change its enabled role set with SET ROLE when the role's authorization method permits.

SESSION_ROLES answers the runtime question by listing roles currently enabled for the user:

SELECT role
FROM   session_roles
ORDER  BY role;

Compare the two result sets when investigating an authorization failure. A role can appear in USER_ROLE_PRIVS but not in SESSION_ROLES because it is nondefault, was disabled in this session, or requires a different activation path. Conversely, enabled nested roles can affect the session even when the administrator began the investigation with only the user's direct role grant.

Report Role Grants Administratively

An administrator with appropriate dictionary access can use DBA_ROLE_PRIVS to report roles granted to users and other roles throughout the current database container. This query reports Brian's role membership:

SELECT grantee, granted_role, default_role,
       admin_option, delegate_option,
       common, inherited
FROM   dba_role_privs
WHERE  grantee = 'BRIAN'
ORDER  BY granted_role;

ADMIN_OPTION='YES' indicates authority associated with administering the role grant. DELEGATE_OPTION='YES' records delegation authority under Oracle's role-grant rules. These values are security findings, not decorative metadata; include them in access reviews because they can allow a grantee to extend authorization beyond ordinary use of the role.

COMMON='YES' means that the role was granted commonly with CONTAINER=ALL. COMMON='NO' identifies a local grant. INHERITED='YES' means the grant was inherited from another container. Do not interpret INHERITED as a nested role relationship; nested role membership is reported separately.

Inspect the Contents of a Role

A role name does not reveal its privilege domain. To explain what ORDER_WRITER supplies, inspect its object privileges, system privileges, and nested roles. ROLE_TAB_PRIVS reports object privileges granted to roles:

SELECT role, owner, table_name,
       privilege, grantable,
       common, inherited
FROM   role_tab_privs
WHERE  role = 'ORDER_WRITER'
ORDER  BY owner, table_name, privilege;

Despite its name, ROLE_TAB_PRIVS can describe privileges on supported schema objects represented by its owner and object-name columns. The view provides information only for roles to which the querying user has access. An administrator who needs database-wide object-grant information should use the appropriately authorized DBA_TAB_PRIVS view.

ROLE_SYS_PRIVS reports system privileges granted to accessible roles:

SELECT role, privilege, admin_option,
       common, inherited
FROM   role_sys_privs
WHERE  role = 'ORDER_WRITER'
ORDER  BY privilege;

System privileges can be much broader than an object privilege such as INSERT ON APP_OWNER.ORDERS. Review privileges containing ANY particularly carefully because they can authorize operations across many schemas.

ROLE_ROLE_PRIVS identifies roles granted to other roles and exposes the role hierarchy:

SELECT role, granted_role, admin_option,
       common, inherited
FROM   role_role_privs
WHERE  role = 'ORDER_WRITER'
ORDER  BY granted_role;

Here, ROLE is the receiving role and GRANTED_ROLE is the nested role it receives. Follow the hierarchy when calculating effective access. Inspecting only the first role can omit privileges inherited through one or more nested roles.

Report Direct Object and System Privileges

A user may possess authority independently of roles. DBA_TAB_PRIVS describes object grants in the current database. The following query compares direct grants to Brian with grants to the two Lesson 5 roles:

SELECT grantee, owner, table_name,
       privilege, grantor, grantable,
       common, inherited
FROM   dba_tab_privs
WHERE  grantee IN ('BRIAN', 'ORDER_READER', 'ORDER_WRITER')
AND    owner = 'APP_OWNER'
AND    table_name = 'ORDERS'
ORDER  BY grantee, privilege;

A direct grant to Brian remains available even if an administrator revokes a role that supplies the same privilege. GRANTABLE='YES' means the object privilege was granted with grant authority, which can create dependent grants that must be considered before a revoke.

DBA_SYS_PRIVS reports system privileges granted directly to users and roles:

SELECT grantee, privilege, admin_option,
       common, inherited
FROM   dba_sys_privs
WHERE  grantee IN ('BRIAN', 'ORDER_READER', 'ORDER_WRITER')
ORDER  BY grantee, privilege;

These administrative views require appropriate dictionary privileges. Do not grant broad dictionary access merely to make a report convenient. Provide a protected reporting view, procedure, or approved administrative workflow when operational staff need a narrower result.

Include Oracle 26ai Schema Privileges

Oracle AI Database 26ai adds schema privileges as a separate authorization category. ROLE_SCHEMA_PRIVS reports schema privileges granted to accessible roles. The new ROLE_SYS_PRIVS_ALL view combines system and schema privileges granted to roles, while DBA_SYS_PRIVS_ALL combines them for users and roles. A non-null SCHEMA value identifies the target schema; it is null for a system privilege.

SELECT grantee, privilege, schema,
       admin_option, common, inherited
FROM   dba_sys_privs_all
WHERE  grantee IN ('BRIAN', 'ORDER_READER', 'ORDER_WRITER')
ORDER  BY grantee, schema, privilege;

Do not add results from DBA_SYS_PRIVS and DBA_SYS_PRIVS_ALL without understanding their overlap. Use the combined 26ai view when the report must cover both system and schema privileges; use the traditional view when the report is intentionally limited to system privileges.

Understand Why One Query Is Not Enough

No single query in this lesson proves the complete authorization available for every operation. Effective access can depend on direct object, schema, or system privileges; enabled roles and nested roles; grants to PUBLIC; local and common grants; secure application role activation; and the actual account and container used by the connection.

Stored code adds another boundary. Definer's-rights and invoker's-rights program units evaluate privileges differently, and roles are not treated the same way in every execution context. A user who cannot modify a table directly might be intentionally authorized to execute a protected package that performs a controlled transaction.

SQL Firewall is also separate from ordinary role reporting. It uses account-level allowlists of expected SQL and trusted connection paths and has its own DBA_SQL_FIREWALL_* views. It can complement privileges and roles, but it is not a replacement name for the old Product User Profile and should be reported as a distinct policy layer when deployed.

Verify Configured Authorization with Tests

Dictionary views report configuration and session state. They do not replace behavioral testing. After a grant, revoke, or role change, establish a new session through the actual application path and run positive and negative tests:

1. Confirm that the approved identity can perform the required operation.
2. Confirm that a reader or unrelated identity cannot perform the write operation.
3. Confirm that operations outside the approved responsibility remain unavailable.
4. Test the application, connection pool, proxy, service, PDB, and direct-client paths that exist in the deployment.
5. Compare the observed result with the role, privilege, and enabled-session reports.

Use a newly authenticated or deliberately refreshed session because a role revoked while already enabled can remain usable in that current session. Capture the report time, database identity, container, statements, results, and change-ticket reference so another reviewer can reproduce the finding.

Summary

1. Record the session user, current schema, service, and container before interpreting authorization data.
2. Use USER_ROLE_PRIVS for granted roles and SESSION_ROLES for roles enabled now.
3. Use DBA_ROLE_PRIVS for administrative role-membership reporting.
4. Inspect role contents through ROLE_TAB_PRIVS, ROLE_SYS_PRIVS, and ROLE_ROLE_PRIVS.
5. Use DBA_TAB_PRIVS and DBA_SYS_PRIVS to find direct object and system grants.
6. Include schema privileges and container metadata when evaluating Oracle AI Database 26ai.
7. Verify the configured authorization with positive and negative tests through the real connection path.

The module conclusion now brings together the supported profile, role, privilege, client-hardening, change-control, and reporting practices used throughout Oracle AI Database 26ai.