DBA_SYS_PRIVS - Query Oracle System Privilege Grants

DBA_SYS_PRIVS

Overview

DBA_SYS_PRIVS records every system privilege grant in the database — one row per grantee-privilege combination. System privileges are database-wide permissions such as CREATE TABLE, ALTER SYSTEM, DROP ANY TABLE, and EXECUTE ANY PROCEDURE. Unlike object privileges, they are not tied to a specific object; they apply across the entire database or all objects of a given type. DBAs query DBA_SYS_PRIVS to audit who holds powerful ANY privileges, to investigate why a user can perform an unexpected action, and to verify that application accounts follow the principle of least privilege.

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

Key Columns

Column	Datatype	Description
GRANTEE	VARCHAR2(128)	User account or role that holds the privilege
PRIVILEGE	VARCHAR2(40)	Name of the system privilege (e.g., CREATE SESSION, ALTER DATABASE, DROP ANY TABLE)
ADMIN_OPTION	VARCHAR2(3)	YES if the grantee can grant this privilege to others; NO otherwise
COMMON	VARCHAR2(3)	YES if the grant was made with CONTAINER=ALL in a multitenant environment
INHERITED	VARCHAR2(3)	YES if the privilege was inherited from the root CDB container

Essential Queries

Basic Usage

List all system privileges granted directly to a specific user (not through roles):

SELECT
    grantee,
    privilege,
    admin_option,
    common
FROM
    dba_sys_privs
WHERE
    grantee = 'APP_OWNER'
ORDER BY
    privilege;

Monitoring Query

Identify all non-Oracle accounts or roles that hold dangerous “ANY” system privileges — the highest-risk grants in any database:

SELECT
    sp.grantee,
    sp.privilege,
    sp.admin_option,
    sp.common,
    CASE
        WHEN u.username IS NOT NULL THEN 'USER'
        ELSE 'ROLE'
    END                                         AS grantee_type,
    u.account_status
FROM
    dba_sys_privs   sp
    LEFT JOIN dba_users u ON u.username = sp.grantee
WHERE
    sp.privilege LIKE '%ANY%'
    AND sp.grantee NOT IN (
        SELECT role FROM dba_roles WHERE oracle_maintained = 'Y'
    )
    AND (u.oracle_maintained = 'N' OR u.oracle_maintained IS NULL)
ORDER BY
    sp.privilege,
    grantee_type,
    sp.grantee;

Combined with Other Views

Show the effective system privileges of a user, including those received indirectly through granted roles (one level of role expansion):

SELECT
    u.username,
    'DIRECT'                                    AS grant_path,
    sp.privilege,
    sp.admin_option
FROM
    dba_users       u
    JOIN dba_sys_privs sp ON sp.grantee = u.username
WHERE
    u.oracle_maintained = 'N'
UNION ALL
SELECT
    u.username,
    'VIA ROLE: ' || rp.granted_role             AS grant_path,
    sp.privilege,
    sp.admin_option
FROM
    dba_users       u
    JOIN dba_role_privs rp ON rp.grantee = u.username
    JOIN dba_sys_privs  sp ON sp.grantee = rp.granted_role
WHERE
    u.oracle_maintained = 'N'
ORDER BY
    username,
    privilege,
    grant_path;

Admin Option Audit

Find every grantee holding a system privilege WITH ADMIN OPTION — these accounts can silently grant powerful privileges to others:

SELECT
    sp.grantee,
    sp.privilege,
    sp.common,
    CASE
        WHEN u.username IS NOT NULL THEN 'USER'
        ELSE 'ROLE'
    END                                         AS grantee_type,
    u.account_status,
    u.oracle_maintained
FROM
    dba_sys_privs   sp
    LEFT JOIN dba_users u ON u.username = sp.grantee
WHERE
    sp.admin_option = 'YES'
    AND (u.oracle_maintained = 'N' OR u.oracle_maintained IS NULL)
ORDER BY
    sp.privilege,
    sp.grantee;

Privilege Distribution Summary

Count how many grantees hold each system privilege, ranked by prevalence — useful for identifying over-granted privileges:

SELECT
    privilege,
    COUNT(*)                                    AS total_grantees,
    SUM(CASE WHEN admin_option = 'YES' THEN 1 ELSE 0 END) AS with_admin_option,
    SUM(CASE WHEN grantee IN (
        SELECT username FROM dba_users WHERE oracle_maintained = 'N'
    ) THEN 1 ELSE 0 END)                        AS user_grantees,
    SUM(CASE WHEN grantee IN (
        SELECT role FROM dba_roles WHERE oracle_maintained = 'N'
    ) THEN 1 ELSE 0 END)                        AS role_grantees
FROM
    dba_sys_privs
GROUP BY
    privilege
ORDER BY
    total_grantees DESC,
    privilege;

Common Use Cases

• Least privilege enforcement — Identify application schemas that have been granted CREATE ANY, DROP ANY, or ALTER ANY privileges that are not required for application operation
• ADMIN OPTION governance — Any account holding a system privilege WITH ADMIN OPTION is a privilege escalation risk; confirm these are limited to trusted DBA accounts
• Pre-production security gate — Before promoting an application to production, verify that the application owner account holds only the specific CREATE TABLE, CREATE INDEX, CREATE SEQUENCE privileges needed — nothing more
• Post-breach forensics — When investigating suspicious activity, enumerate all system privileges of the suspect account directly and through roles to understand the full scope of access
• Separation of duties verification — Confirm that application accounts do not also hold DBA, SYSDBA, or ALTER SYSTEM, which would allow them to bypass application-layer controls
• Compliance evidence — Generate a privilege matrix mapping every non-Oracle user to their direct system privileges for PCI-DSS, SOX, or HIPAA audit evidence packages

Related Views

• DBA_ROLE_PRIVS — Roles granted to each user; combine with DBA_SYS_PRIVS to see privileges received indirectly through roles
• DBA_ROLES — Role definitions; use when GRANTEE is a role rather than a user account
• DBA_TAB_PRIVS — Object-level privileges; the complement to system-level privileges
• DBA_USERS — Account metadata for each GRANTEE that is a user; provides account status and profile
• SESSION_PRIVS — Shows system privileges currently enabled in the connected session, including those received through currently active roles
• DBA_AUDIT_TRAIL — Records GRANT PRIVILEGE and REVOKE PRIVILEGE events when standard auditing is enabled

Version Notes

• Oracle 7: View exists from the earliest releases with GRANTEE, PRIVILEGE, and ADMIN_OPTION
• Oracle 12c: COMMON and INHERITED columns added to support multitenant CONTAINER=ALL grants
• Oracle 12.2: Oracle began publishing the Privilege Analysis feature (DBMS_PRIVILEGE_CAPTURE) to complement DBA_SYS_PRIVS with actual privilege usage data
• Oracle 19c: Least-privilege documentation updated; Oracle supplies a Privilege Analysis report in Database Vault to identify unused system privileges
• Oracle 23ai: The new DB_DEVELOPER_ROLE consolidates several development-oriented system privileges, reducing the need to grant individual CREATE * privileges to developer accounts