DBA_TAB_PRIVS - Query Object-Level Privilege Grants

DBA_TAB_PRIVS

Overview

DBA_TAB_PRIVS records every object-level privilege grant in the database, despite its name covering more than just tables. It includes grants on tables, views, sequences, procedures, functions, packages, types, directories, and Java objects. Each row records who granted the privilege, who received it, the object involved, the specific privilege (SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, etc.), and whether the grantee holds the GRANT OPTION allowing them to re-grant to others. It is the primary view for auditing data access controls at the object level.

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

Key Columns

Column	Datatype	Description
GRANTEE	VARCHAR2(128)	User account or role that received the privilege
OWNER	VARCHAR2(128)	Schema that owns the object on which the privilege was granted
TABLE_NAME	VARCHAR2(128)	Name of the object (table, view, sequence, procedure, etc.)
GRANTOR	VARCHAR2(128)	Account that performed the GRANT statement
PRIVILEGE	VARCHAR2(40)	The privilege granted: SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, ALTER, INDEX, READ, WRITE, or DEBUG
GRANTABLE	VARCHAR2(3)	YES if the grantee can grant this privilege to others (WITH GRANT OPTION); NO otherwise
HIERARCHY	VARCHAR2(3)	YES if the grant applies to the full object hierarchy (used with WITH HIERARCHY OPTION)
COMMON	VARCHAR2(3)	YES if the grant was made with CONTAINER=ALL in a multitenant environment
TYPE	VARCHAR2(24)	Object type: TABLE, VIEW, PROCEDURE, FUNCTION, PACKAGE, SEQUENCE, etc.
INHERITED	VARCHAR2(3)	YES if the grant was inherited from the root CDB container

Essential Queries

Basic Usage

List all object privileges on tables and views in a specific schema:

SELECT
    grantee,
    owner,
    table_name,
    type,
    privilege,
    grantable,
    grantor
FROM
    dba_tab_privs
WHERE
    owner = 'APP_OWNER'
    AND type IN ('TABLE', 'VIEW')
ORDER BY
    table_name,
    privilege,
    grantee;

Monitoring Query

Find all PUBLIC grants — privileges granted to the PUBLIC pseudo-user are accessible by every database account and are a common security finding:

SELECT
    owner,
    table_name,
    type,
    privilege,
    grantable,
    grantor
FROM
    dba_tab_privs
WHERE
    grantee = 'PUBLIC'
    AND owner NOT IN (
        SELECT username FROM dba_users WHERE oracle_maintained = 'Y'
    )
ORDER BY
    owner,
    table_name,
    privilege;

Combined with Other Views

Show all object privileges a specific user holds, both directly and through granted roles, for a comprehensive access review:

SELECT
    'DIRECT'                                    AS grant_path,
    tp.owner,
    tp.table_name,
    tp.type,
    tp.privilege,
    tp.grantable
FROM
    dba_tab_privs tp
WHERE
    tp.grantee = 'APP_USER'
UNION ALL
SELECT
    'VIA ROLE: ' || rp.granted_role             AS grant_path,
    tp.owner,
    tp.table_name,
    tp.type,
    tp.privilege,
    tp.grantable
FROM
    dba_role_privs  rp
    JOIN dba_tab_privs tp ON tp.grantee = rp.granted_role
WHERE
    rp.grantee = 'APP_USER'
ORDER BY
    owner,
    table_name,
    privilege,
    grant_path;

Execute Privilege Audit

List all EXECUTE privileges on stored code objects — procedures, functions, and packages — granted to non-Oracle user accounts:

SELECT
    tp.grantee,
    tp.owner,
    tp.table_name                               AS object_name,
    tp.type,
    tp.grantable,
    tp.grantor,
    u.account_status
FROM
    dba_tab_privs   tp
    LEFT JOIN dba_users u ON u.username = tp.grantee
WHERE
    tp.privilege = 'EXECUTE'
    AND tp.type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TYPE')
    AND tp.owner NOT IN (
        SELECT username FROM dba_users WHERE oracle_maintained = 'Y'
    )
    AND tp.grantee != 'PUBLIC'
ORDER BY
    tp.owner,
    tp.table_name,
    tp.grantee;

Grant Option Exposure

Find all grants made WITH GRANT OPTION — these grantees can extend access to other accounts without DBA involvement:

SELECT
    tp.grantee,
    tp.owner,
    tp.table_name,
    tp.type,
    tp.privilege,
    tp.grantor,
    u.account_status
FROM
    dba_tab_privs   tp
    LEFT JOIN dba_users u ON u.username = tp.grantee
WHERE
    tp.grantable = 'YES'
    AND tp.grantee NOT IN (
        SELECT username FROM dba_users WHERE oracle_maintained = 'Y'
    )
ORDER BY
    tp.privilege,
    tp.owner,
    tp.table_name,
    tp.grantee;

Common Use Cases

• Data access audit — Enumerate every account that can SELECT from a sensitive table (e.g., a table containing PII or financial data) by filtering on TABLE_NAME and PRIVILEGE = ‘SELECT’
• PUBLIC grant remediation — Security hardening projects routinely query for grants to PUBLIC and revoke those not required by Oracle internals; this is a standard CIS Benchmark finding
• Application schema review — Before going live, verify that the application’s read-only reporting role holds only SELECT, and its write role holds only INSERT/UPDATE/DELETE — with no EXECUTE on DBA packages
• Grant option control — GRANTABLE = YES means an account can quietly extend access; these should be limited to schema owners and DBA accounts
• Cross-schema dependency mapping — When planning a schema rename or drop, find all grants from that schema to other accounts to understand what will break
• Privilege cleanup after developer offboarding — Filter by GRANTOR = ‘DEPARTED_DEV’ to find all grants they issued that may now be unowned and undocumented

Related Views

• DBA_SYS_PRIVS — System-wide privileges; complement to the object-level view DBA_TAB_PRIVS
• DBA_ROLE_PRIVS — Role assignments; combine with DBA_TAB_PRIVS to trace object access granted through roles
• DBA_COL_PRIVS — Column-level privileges (UPDATE, REFERENCES on individual columns) which do not appear in DBA_TAB_PRIVS
• DBA_USERS — Account metadata for GRANTEE or GRANTOR accounts
• ALL_TAB_PRIVS — Session-scoped version; shows object grants where the current user is grantee, grantor, or the object owner
• DBA_AUDIT_TRAIL — Records GRANT OBJECT and REVOKE OBJECT events when standard auditing is enabled

Version Notes

• Oracle 7: Core columns GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE, GRANTABLE present from initial release
• Oracle 8i: TYPE column added, extending the view beyond tables to cover PL/SQL objects, types, and Java objects
• Oracle 12c: COMMON, INHERITED columns added for multitenant visibility
• Oracle 12.2: READ privilege added as a read-only alternative to SELECT that does not allow SELECT FOR UPDATE locking
• Oracle 19c: No structural changes; Oracle recommends replacing SELECT grants with READ grants for read-only application accounts
• Oracle 23ai: No structural changes; privilege analysis tooling (DBMS_PRIVILEGE_CAPTURE) can be used alongside DBA_TAB_PRIVS to identify unused object privileges