DBA_ROLES - List All Oracle Database Roles

DBA_ROLES

Overview

DBA_ROLES lists every role that exists in the database, including Oracle-supplied roles such as DBA, CONNECT, and RESOURCE, as well as any application-defined roles created by DBAs or developers. For each role it records whether the role requires a password to be enabled, whether it is authenticated externally or globally, and — in multitenant environments — whether the role is common across all PDBs or local to a single PDB. DBA_ROLES is the starting point for a role audit: you enumerate roles here, then join to DBA_ROLE_PRIVS and DBA_SYS_PRIVS to understand what each role grants.

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

Key Columns

Column	Datatype	Description
ROLE	VARCHAR2(128)	Name of the role
PASSWORD_REQUIRED	VARCHAR2(8)	YES if a password is required to enable the role, NO otherwise
AUTHENTICATION_TYPE	VARCHAR2(11)	NONE (no password), PASSWORD (role password set), EXTERNAL (OS/directory), GLOBAL (Oracle Internet Directory), APPLICATION (Oracle Database Vault)
COMMON	VARCHAR2(3)	YES if the role is a common role visible across all PDBs in a CDB; NO if local to one PDB
ORACLE_MAINTAINED	VARCHAR2(1)	Y if the role was created by Oracle-supplied scripts; N if user-defined
INHERITED	VARCHAR2(3)	YES if the role definition is inherited from the root container
IMPLICIT	VARCHAR2(3)	YES if the role was implicitly created (e.g., for application containers)

Essential Queries

Basic Usage

List all user-defined (non-Oracle) roles with their authentication type:

SELECT
    role,
    password_required,
    authentication_type,
    common,
    oracle_maintained
FROM
    dba_roles
WHERE
    oracle_maintained = 'N'
ORDER BY
    role;

Monitoring Query

Show a full inventory of all roles, counting how many users and roles have each role granted, to identify unused or over-assigned roles:

SELECT
    r.role,
    r.authentication_type,
    r.common,
    r.oracle_maintained,
    COUNT(DISTINCT rp.grantee)                  AS grantee_count,
    SUM(CASE WHEN rp.admin_option = 'YES' THEN 1 ELSE 0 END) AS admin_option_count
FROM
    dba_roles       r
    LEFT JOIN dba_role_privs rp ON rp.granted_role = r.role
WHERE
    r.oracle_maintained = 'N'
GROUP BY
    r.role,
    r.authentication_type,
    r.common,
    r.oracle_maintained
ORDER BY
    grantee_count DESC,
    r.role;

Combined with Other Views

Show all system privileges and roles included within each user-defined role, producing a flattened privilege matrix:

SELECT
    r.role                                      AS role_name,
    'SYSTEM PRIVILEGE'                          AS grant_type,
    sp.privilege                                AS granted_item,
    sp.admin_option
FROM
    dba_roles    r
    JOIN dba_sys_privs sp ON sp.grantee = r.role
WHERE
    r.oracle_maintained = 'N'
UNION ALL
SELECT
    r.role,
    'ROLE',
    rp.granted_role,
    rp.admin_option
FROM
    dba_roles    r
    JOIN dba_role_privs rp ON rp.grantee = r.role
WHERE
    r.oracle_maintained = 'N'
ORDER BY
    role_name,
    grant_type,
    granted_item;

Role Hierarchy Analysis

Recursively expand role-within-role grants to show the full privilege footprint of top-level roles granted to users:

SELECT
    LPAD(' ', 4 * (LEVEL - 1)) || granted_role AS role_tree,
    grantee,
    admin_option,
    LEVEL                                       AS depth
FROM
    dba_role_privs
START WITH
    grantee IN (SELECT role FROM dba_roles WHERE oracle_maintained = 'N')
CONNECT BY NOCYCLE PRIOR
    granted_role = grantee
ORDER SIBLINGS BY
    granted_role;

Roles with No Current Grantees

Find user-defined roles that have no users or roles assigned — candidates for review or cleanup:

SELECT
    r.role,
    r.authentication_type,
    r.common,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD')              AS audit_date
FROM
    dba_roles r
WHERE
    r.oracle_maintained = 'N'
    AND NOT EXISTS (
        SELECT 1
        FROM   dba_role_privs rp
        WHERE  rp.granted_role = r.role
    )
ORDER BY
    r.role;

Common Use Cases

• Role inventory for security audits — Enumerate all non-Oracle roles with their authentication type to confirm no roles use a role password (which can be bypassed) in production
• Privilege sprawl analysis — Join to DBA_ROLE_PRIVS and DBA_SYS_PRIVS to map how many users receive powerful privileges indirectly through roles
• Unused role cleanup — Identify roles with zero grantees that are accumulating in the data dictionary and should be reviewed or dropped
• Multitenant role governance — In CDB environments, distinguish common roles (scoped across PDBs) from local roles to understand where a GRANT must be executed
• Application deployment verification — After deploying a new schema, confirm that the expected application roles exist and have the correct authentication type
• Database Vault integration — Identify APPLICATION-authenticated roles that are managed by Oracle Database Vault and should not be modified outside of Vault policies

Related Views

• DBA_ROLE_PRIVS — Which users and roles have each role granted to them
• DBA_SYS_PRIVS — System privileges granted directly to each role
• DBA_TAB_PRIVS — Object-level privileges granted to each role
• DBA_USERS — All database accounts; join on PROFILE or USERNAME to correlate accounts with role assignments
• SESSION_ROLES — Shows which roles are currently enabled in the connected session, useful when debugging SET ROLE behaviour
• DBA_ENABLED_ROLES — Shows which roles are automatically enabled (not requiring SET ROLE) for each user

Version Notes

• Oracle 8i: Role passwords introduced; PASSWORD_REQUIRED column reflects this
• Oracle 10g: EXTERNAL and GLOBAL authentication types added
• Oracle 12c: COMMON, ORACLE_MAINTAINED, INHERITED, and IMPLICIT columns added for multitenant architecture; common roles can now be granted with CONTAINER=ALL
• Oracle 12.2: APPLICATION authentication type added for Oracle Database Vault application roles
• Oracle 19c: No structural changes; Oracle-supplied roles reviewed and some deprecated as part of the least-privilege initiative
• Oracle 23ai: New developer-oriented roles introduced (DB_DEVELOPER_ROLE) for typical schema development tasks without requiring DBA