Oracle Table Privileges Granted to a Role via role_tab_privs

calendar Jan 4, 2024 / · 11 min read · role privileges show_table_role_privileges oracle role_tab_privs Oracle DBA Security Audit Data Dictionary Oracle Administration dba_roles dba_role_privs  ·
Share on: linkedin copy

Oracle Table Privileges Granted to a Role via role_tab_privs

Purpose

ROLE_TAB_PRIVS lists the object privileges granted to roles — every table, view, and sequence grant, the precise privilege type, and whether the right covers the entire object or is scoped to a single column. Oracle's reference notes that "Information is provided only about roles to which the user has access", so run it from an account that holds the roles you are auditing, or use DBA_TAB_PRIVS for every role. It is the correct starting point when you need to answer the question that surfaces in every access review: what table-level access does this role actually allow?

This view is distinct from DBA_TAB_PRIVS, which describes all object grants in the database, to users and roles alike. ROLE_TAB_PRIVS shows only the grants made to roles — the object grants associated with each named role the querying user has access to, regardless of which DBA issued them or when.

The query below takes a role name as a SQL*Plus substitution variable and returns every table-level privilege attached to it. Two extended variants follow: an exact-match form suitable for scripts where interactive prompts are unavailable, and a three-table join that maps which users hold the role and what table access they inherit as a result. All three queries are read-only, touch only data dictionary views, and require no elevated object privileges beyond standard dictionary access.

Code

1-- Core query: all table privileges for a named role
2SELECT owner || '.' || table_name "TABLE"
3     , column_name
4     , privilege
5     , grantable
6FROM   role_tab_privs
7WHERE  role LIKE '&role'
8/

Exact-match form — suitable for scripts and monitoring queries:

1SELECT owner || '.' || table_name  AS table_object
2     , column_name
3     , privilege
4     , grantable
5FROM   role_tab_privs
6WHERE  role = 'SALES_READ_ONLY'
7ORDER  BY owner, table_name, privilege;

Extended audit: users who hold a role and the table privileges they inherit from it:

 1SELECT rp.grantee                        AS db_user
 2     , rp.granted_role
 3     , tp.owner || '.' || tp.table_name  AS table_object
 4     , tp.column_name
 5     , tp.privilege
 6     , tp.grantable
 7FROM   dba_role_privs  rp
 8       JOIN role_tab_privs tp
 9         ON tp.role = rp.granted_role
10WHERE  rp.grantee NOT IN (
11           SELECT role FROM dba_roles
12       )
13ORDER  BY rp.grantee, rp.granted_role, tp.owner, tp.table_name;

Nested role chain: roles granted to a role, one level deep:

1SELECT role
2     , granted_role
3     , admin_option
4FROM   role_role_privs
5WHERE  role = 'SALES_READ_ONLY'
6ORDER  BY granted_role;

Code Breakdown

The core ROLE_TAB_PRIVS query

  • owner || '.' || table_name "TABLE" — concatenates the schema owner and the object name into a fully qualified identifier. Without the owner prefix, a table named EMPLOYEES could belong to HR, SCOTT, or any application schema; the qualified form removes that ambiguity.
  • column_name — the column to which the privilege applies. For table-level grants — the common case — this column is NULL. A non-null value indicates a column-level privilege: the role has access only to that specific column, not the full row. Auditors must explicitly check this column; a scan of the privilege column alone cannot distinguish a table-wide UPDATE grant from an UPDATE(SALARY) column grant.
  • privilege — the specific right: SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or EXECUTE for stored objects such as packages and procedures. The same view covers both table and stored-object grants, so an EXECUTE row indicates a procedural grant to the role, not a table grant.
  • grantable — Oracle's reference defines it as "YES if the role was granted with ADMIN OPTION; otherwise NO". It does not mean WITH GRANT OPTION: Oracle allows that option "only when granting to a user or to PUBLIC, not when granting to a role."
  • WHERE role LIKE '&role' — the &role substitution variable prompts for input in SQL*Plus. LIKE allows wildcard searches: SALES% matches every role whose name starts with SALES. Use = instead of LIKE for exact-match semantics and to avoid accidental wildcard expansion in scripts.
  • / — the forward slash tells SQL*Plus to execute the statement currently in the buffer. It is not a SQL statement terminator; the semicolon in the exact-match variant below serves that role.

The exact-match variant

Replacing LIKE '&role' with = 'SALES_READ_ONLY' removes the substitution variable prompt. ORDER BY owner, table_name, privilege produces deterministic output that can be compared between audit runs with a line-by-line diff — useful for change control and compliance evidence.

The user-to-privilege join

The three-table query connects three privilege layers in one result set: the database user (DBA_ROLE_PRIVS.grantee), the role that user holds (granted_role), and the table privileges that role carries (ROLE_TAB_PRIVS). The NOT IN (SELECT role FROM DBA_ROLES) filter excludes role-to-role grants so the output covers only human accounts. Removing that filter expands the scope to include role-to-role nesting, which is useful when you need the full role inheritance picture.

The nested role chain query

ROLE_ROLE_PRIVS exposes the roles-within-roles structure that ROLE_TAB_PRIVS does not flatten. When ROLE_A is granted to ROLE_B, querying ROLE_TAB_PRIVS for ROLE_B returns only ROLE_B's direct object grants. The ROLE_ROLE_PRIVS query above returns the child roles one level down, so you know which additional roles to query in turn to get the complete privilege picture. For deeply nested structures, iterate through each returned granted_role until no further nesting remains.

Key Points

  • ROLE_TAB_PRIVS does not flatten nested roles. If ROLE_A is granted to ROLE_B, querying ROLE_B returns ROLE_B's direct table privileges only — not the privileges ROLE_B inherits from ROLE_A. To trace nested grants, query ROLE_ROLE_PRIVS to find the role-to-role chain, then query ROLE_TAB_PRIVS for each role in the chain.
  • Column-level grants produce individual rows. A grant of UPDATE(SALARY) on HR.EMPLOYEES generates one row with column_name = 'SALARY'. There is no summary row for the table; every column grant stands alone. A role can hold a full-table SELECT (one row, column_name NULL) alongside a column-level UPDATE (a second row, column_name = 'SALARY') on the same table at the same time.
  • Roles cannot hold WITH GRANT OPTION. Oracle permits that option only when granting to a user or to PUBLIC, so the grant-option escalation risk sits with users in DBA_TAB_PRIVS (grantable = 'YES'), not with roles.
  • No rows returned does not mean no access. A role with no table grants can still carry broad access through system privileges such as SELECT ANY TABLE visible in ROLE_SYS_PRIVS. Always check all three privilege tiers — system, object, and column — when auditing a role.
  • ROLE_TAB_PRIVS covers stored objects too. An EXECUTE grant on a package, procedure, or function granted to a role appears here with privilege = 'EXECUTE', not in a separate view. The same query returns both table grants and executable grants in a single result set.
  • The PUBLIC role is a special case. Grants to PUBLIC are held by every database user. Query them with SELECT * FROM dba_tab_privs WHERE grantee = 'PUBLIC', the same source this post uses for the PUBLIC path below, because ROLE_TAB_PRIVS shows only roles to which the querying user has access. On many Oracle installations this set is unexpectedly large and is the first thing to review in a privilege-reduction exercise.

Insights and Best Practices

Audit all three privilege tiers for a complete picture

An Oracle role can carry access through three independent channels: table-level object privileges (ROLE_TAB_PRIVS), system privileges such as CREATE TABLE or SELECT ANY TABLE (ROLE_SYS_PRIVS), and nested role grants (ROLE_ROLE_PRIVS). An audit that reads only ROLE_TAB_PRIVS will miss both a SELECT ANY TABLE system privilege and a nested role that carries table grants. A complete role audit queries all three views and merges the results.

Trace the full user-to-table path

A user's effective table access comes from three sources that must be checked independently: privileges granted directly to the user (DBA_TAB_PRIVS WHERE grantee = :user), privileges carried by roles the user holds (ROLE_TAB_PRIVS joined through DBA_ROLE_PRIVS), and the PUBLIC pseudo-role (DBA_TAB_PRIVS WHERE grantee = 'PUBLIC'). The three-table join in the Code section covers the role-based path. Pair it with a separate query against DBA_TAB_PRIVS for the direct and public grants to produce the full effective privilege set.

Revoke WITH GRANT OPTION wherever it is not required

The grant option cannot sit on a role, but it can sit on users who hold the same objects directly. Check DBA_TAB_PRIVS for grantable = 'YES' rows: each one lets that user pass the privilege on to other users and roles. Oracle's GRANT reference says that to remove the option you revoke the privilege from the user altogether and then grant it again without the grant option.

Use SESSION_ROLES and SESSION_PRIVS to check an active session

When a database session activates a role — either at logon through DEFAULT ROLE or explicitly with SET ROLE — SESSION_ROLES describes the roles that are currently enabled to the user, and SESSION_PRIVS describes the system privileges currently available. Neither lists object privileges, so query them from within a test account to confirm that the expected roles are enabled, then check those roles in ROLE_TAB_PRIVS. They cannot be used for off-session audits but are the fastest way to validate a role configuration during setup.

Handle CDB and PDB scope in multitenant databases

In a multitenant Oracle database (12c and later), ROLE_TAB_PRIVS is container-local: connecting to a PDB returns that PDB's local role grants. Common roles with the C## prefix exist in the CDB root and propagate to all PDBs, but their grants on local PDB objects appear only in that PDB's dictionary, not in the root. An enterprise-wide audit must connect to each PDB in turn and run the query in each container. Grants on common CDB objects appear in the root's dictionary when you connect to CDB$ROOT.

Build a standing audit query with role drift detection

Store a baseline snapshot of ROLE_TAB_PRIVS output for each sensitive role in a comparison table. Schedule a weekly job that re-queries the view, compares the results to the snapshot, and logs any differences as a role-privilege drift event. Newly added rows indicate a privilege was granted since the baseline; missing rows indicate a revoke. This gives a cheap, lightweight change-control record without a third-party security product.

Watch for synonyms masking the real object owner

Oracle's GRANT reference is explicit: "Granting a privilege on a synonym is equivalent to granting the privilege on the base object." So when an application reaches a table through a public or private synonym, look for the base object, not the synonym name, in ROLE_TAB_PRIVS. Query DBA_SYNONYMS on the synonym name to find where it resolves, then verify that the underlying base object is the one the role should have access to.

When to Use This Check

  • Baseline role audit before a production deployment or periodic access review.
  • Investigating unexpected data access or compliance findings tied to a named role.
  • Before modifying or dropping a role, identifying which application functionality depends on its current grants.
  • Generating a privilege matrix for SOX, PCI-DSS, or GDPR access-control documentation.
  • Comparing role privilege sets across development, test, and production instances to detect environment drift.
  • Confirming that a newly created role has been given only the minimal table access it requires.
  • Identifying roles granted WITH ADMIN OPTION (grantable = 'YES') as part of a privilege-reduction initiative.

Troubleshooting Common Issues

If the query returns no rows for a role that clearly exists, the role carries no direct table-level object grants. Run SELECT * FROM role_sys_privs WHERE role = :role to check for system privileges, and SELECT * FROM role_role_privs WHERE role = :role to see whether the role is a container for other roles that hold the actual table grants. A role holding SELECT ANY TABLE would return nothing from ROLE_TAB_PRIVS while still allowing queries on tables, views, and materialized views in any schema except SYS and AUDSYS through a system privilege.

If the substitution variable prompt does not appear, the SQL client may have & substitution disabled or using a different prefix character. Replace LIKE '&role' with = 'ROLENAME' directly, or use a bind variable (:role) if the client supports it.

If grantable shows YES for a privilege you did not expect, Oracle's reference defines that value as "YES if the role was granted with ADMIN OPTION" — it is not a WITH GRANT OPTION grant, which Oracle does not allow on a role. Check DBA_TAB_PRIVS for the original grant record.

If the user-to-privilege join produces duplicate rows, the same user may hold the target role through more than one inheritance path. Add DISTINCT or use GROUP BY (grantee, granted_role, table_object, column_name, privilege) to collapse duplicates while retaining one row per unique access right.

References

Posts in this series