Oracle Table Privileges Granted to a Role via role_tab_privs

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

Oracle Table Privileges Granted to a Role via role_tab_privs

Purpose

ROLE_TAB_PRIVS holds the complete inventory of object privileges Oracle has packaged into each named role — every table, view, and sequence grant, the precise privilege type, whether the right covers the entire object or is scoped to a single column, and whether the role can pass the grant further down the privilege chain. 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 records privileges granted directly to users and roles by explicit GRANT statements. ROLE_TAB_PRIVS shows what a role carries once it has been assembled — the net set of object grants associated with that named role, 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
4     , default_role
5FROM   role_role_privs
6WHERE  role = 'SALES_READ_ONLY'
7ORDER  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 — YES if the role holds the privilege WITH GRANT OPTION; NO in the standard case. A YES value means the role can in turn grant this privilege to users or other roles, creating a privilege propagation risk in a least-privilege architecture.
  • 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.
  • GRANTABLE = YES is a privilege escalation vector. A role holding a privilege WITH GRANT OPTION can re-grant that privilege to accounts the original DBA never intended to cover. In an access-controlled environment, YES rows in ROLE_TAB_PRIVS warrant explicit sign-off.
  • 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, or through grants on synonyms that resolve to objects in other schemas. 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. SELECT * FROM role_tab_privs WHERE role = 'PUBLIC' returns every object privilege that any database user holds by default. 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

When grantable = 'YES' appears in ROLE_TAB_PRIVS, the role can pass that privilege to any user or role, bypassing the DBA as the authorisation gatekeeper. Revoke and re-issue the grant without WITH GRANT OPTION to close the escalation path without changing the role's functional behaviour. The re-grant removes the privilege and re-adds it without the grant option; the role continues to work, but can no longer propagate the right.

Use SESSION_PRIVS for the fully flattened active set

When a database session activates a role — either at logon through DEFAULT ROLE or explicitly with SET ROLE — the SESSION_PRIVS view returns every active privilege the session currently holds, flattened across all levels of role nesting. This is the view to query from within a test account to confirm that a chain of nested roles ultimately delivers the expected table access. It cannot be used for off-session audits but is 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

When ROLE_TAB_PRIVS shows a grant against an object you do not recognise, the object may be a public or private synonym. Query DBA_SYNONYMS on the object name to find where the synonym resolves, then verify that the underlying base object is the one the role should have access to. A grant on a synonym and a grant on the underlying table are separate; revoking one does not revoke the other.

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 all roles that carry WITH GRANT OPTION 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 access to every table in the database 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, the source was a GRANT ... WITH GRANT OPTION statement. Check DBA_TAB_PRIVS for the original grant record. To remove the grant option, revoke the privilege from the role and re-grant it without the WITH GRANT OPTION clause.

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