DBA_ROLE_PRIVS - Query Role Grants to Users & Roles

DBA_ROLE_PRIVS

Overview

DBA_ROLE_PRIVS records every role-to-user and role-to-role grant in the database. Each row captures the grantee (a user account or another role), the role that was granted, whether the grantee holds the ADMIN OPTION (allowing them to re-grant the role), whether the role is set as the grantee’s default (automatically enabled at login), and whether the grant was made with DELEGATE OPTION (Oracle 12.2+). DBAs use this view to audit privilege escalation paths, understand why a user has unexpected access, and map role hierarchies before modifying or revoking grants.

View Type: Data Dictionary View Available Since: Oracle 7 Required Privileges: SELECT on DBA_ROLE_PRIVS or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
GRANTEE	VARCHAR2(128)	User account or role that received the grant
GRANTED_ROLE	VARCHAR2(128)	Name of the role that was granted
ADMIN_OPTION	VARCHAR2(3)	YES if the grantee can grant this role to others; NO otherwise
DEFAULT_ROLE	VARCHAR2(3)	YES if the role is automatically enabled when the grantee logs in; NO if it must be enabled with SET ROLE
COMMON	VARCHAR2(3)	YES if the grant was made with CONTAINER=ALL (multitenant); NO if local
INHERITED	VARCHAR2(3)	YES if the grant was inherited from the root CDB container

Essential Queries

Basic Usage

List all roles granted to a specific user, including whether they are enabled by default:

SELECT
    grantee,
    granted_role,
    admin_option,
    default_role,
    common
FROM
    dba_role_privs
WHERE
    grantee = 'APP_USER'
ORDER BY
    granted_role;

Monitoring Query

Find all users who hold powerful Oracle-supplied roles — a key security audit check:

SELECT
    rp.grantee,
    rp.granted_role,
    rp.admin_option,
    rp.default_role,
    u.account_status,
    u.oracle_maintained                         AS is_oracle_account
FROM
    dba_role_privs  rp
    JOIN dba_users  u  ON u.username = rp.grantee
WHERE
    rp.granted_role IN (
        'DBA', 'SYSDBA', 'SYSOPER', 'SYSKM', 'SYSDG',
        'EXECUTE_CATALOG_ROLE', 'SELECT_CATALOG_ROLE',
        'IMP_FULL_DATABASE', 'EXP_FULL_DATABASE',
        'DATAPUMP_IMP_FULL_DATABASE', 'DATAPUMP_EXP_FULL_DATABASE'
    )
    AND u.oracle_maintained = 'N'
ORDER BY
    rp.granted_role,
    rp.grantee;

Combined with Other Views

Produce a complete user-to-role matrix showing every role each non-Oracle user holds, including roles-within-roles expanded one level:

SELECT
    u.username,
    rp.granted_role                             AS direct_role,
    rp2.granted_role                            AS indirect_role,
    rp.admin_option                             AS direct_admin,
    rp2.admin_option                            AS indirect_admin
FROM
    dba_users       u
    JOIN dba_role_privs  rp  ON rp.grantee = u.username
    LEFT JOIN dba_role_privs rp2 ON rp2.grantee = rp.granted_role
WHERE
    u.oracle_maintained = 'N'
ORDER BY
    u.username,
    rp.granted_role,
    rp2.granted_role NULLS LAST;

Admin Option Audit

Find all grantees who hold a role WITH ADMIN OPTION — these accounts can re-grant roles to others, which represents a privilege escalation risk:

SELECT
    rp.grantee,
    rp.granted_role,
    rp.default_role,
    rp.common,
    CASE
        WHEN u.username IS NOT NULL THEN 'USER'
        ELSE 'ROLE'
    END                                         AS grantee_type,
    u.account_status
FROM
    dba_role_privs  rp
    LEFT JOIN dba_users u ON u.username = rp.grantee
WHERE
    rp.admin_option = 'YES'
ORDER BY
    rp.granted_role,
    grantee_type,
    rp.grantee;

Role Grant Change Report

Compare current role grants to a baseline stored in a custom audit table to detect unauthorised changes since the last review:

-- Roles granted since a specific date, detected via DBA_AUDIT_TRAIL
SELECT
    obj_name                                    AS granted_role,
    grantee,
    timestamp                                   AS grant_time,
    os_username,
    userhost
FROM
    dba_audit_trail
WHERE
    action_name = 'GRANT ROLE'
    AND timestamp > TRUNC(SYSDATE) - 30
ORDER BY
    timestamp DESC;

Common Use Cases

• Privilege escalation investigation — Trace the role hierarchy to determine why a user has unexpected access to objects; expand DBA_ROLE_PRIVS recursively to see all nested grants
• DBA role audit — Confirm that only named DBA accounts hold the DBA role, and that no service accounts or application schemas have it
• ADMIN OPTION governance — Identify all grantees with ADMIN OPTION as these accounts can silently propagate roles without further DBA involvement
• Default role review — Find roles that are NOT set as DEFAULT_ROLE for a user, which means the user must explicitly SET ROLE to activate them — sometimes a sign of misconfigured application setup
• Pre-revocation impact analysis — Before revoking a role from a user or dropping a role entirely, enumerate all grantees in DBA_ROLE_PRIVS to assess the blast radius
• Multitenant common grant verification — In CDB environments, confirm that common roles granted with CONTAINER=ALL appear with COMMON=YES in every PDB

Related Views

• DBA_ROLES — Definitions of all roles; join on GRANTED_ROLE to get authentication type and Oracle-maintained flag
• DBA_SYS_PRIVS — System privileges granted to roles; combine with DBA_ROLE_PRIVS to see the full privilege set a user receives through roles
• DBA_TAB_PRIVS — Object privileges granted to roles; same combination approach as DBA_SYS_PRIVS
• DBA_USERS — Account status and profile for each GRANTEE that is a user rather than a role
• SESSION_ROLES — Shows roles currently enabled in the active session; useful for confirming SET ROLE behaviour matches expectations from DBA_ROLE_PRIVS
• DBA_AUDIT_TRAIL — Records GRANT ROLE and REVOKE ROLE DDL events when auditing is enabled

Version Notes

• Oracle 8i: ADMIN_OPTION and DEFAULT_ROLE columns present from early releases
• Oracle 12c: COMMON and INHERITED columns added for multitenant role grant visibility
• Oracle 12.2: DELEGATE OPTION introduced for fine-grained role delegation in application containers; visible through the DELEGATE_OPTION column added in some patch levels
• Oracle 19c: No structural changes; Oracle-supplied role usage reviewed in the context of least-privilege recommendations
• Oracle 23ai: COMMON grants via CONTAINER=ALL are now enforced more strictly in application PDBs, changing how some rows appear in this view