Oracle 1Z0-084: The View That Broke When It Gained a Table

The View That Broke When It Gained a Table

Schema SALES owns V_MONTHLY, a reporting view over SALES.ORDERS and, since yesterday, SALES.SHIP_COSTS. The view was created without any AUTHID clause. User RPT has role REPORT_ROLE, which holds SELECT on SALES.V_MONTHLY and on SALES.ORDERS. SALES itself was granted SELECT on SHIP_COSTS only through the role DATA_READERS, which is enabled for SALES at login.

In SQL*Plus as SALES, SELECT COUNT(*) FROM ship_costs succeeds. As RPT, SELECT * FROM sales.v_monthly now fails with ORA-00942 'table or view does not exist' - although the same query worked before SHIP_COSTS was added. RPT can still query SALES.ORDERS directly. The DBA is reluctant to grant anything to RPT, since the whole point of the view is to hide the cost data, and asks for a diagnosis backed by dictionary evidence before anything is changed.

1. Diagnose the ORA-00942. Why does SALES's direct query succeed while the view fails, and why is RPT's own privilege set irrelevant?

Read the worked answer

The view has no AUTHID clause, so it is a definer's-rights object: when RPT selects it, the base-table access inside the view is checked against SALES's privileges, not RPT's. That is precisely why the view is a good hiding mechanism.

But definer's-rights resolution has a sharp edge: privileges acquired through a role are not used when Oracle resolves names inside a definer's-rights view or PL/SQL unit. Only privileges granted directly to SALES (or to PUBLIC), plus system privileges, count. SALES holds SELECT on SHIP_COSTS solely via DATA_READERS, so inside the view the reference to SHIP_COSTS is unresolvable and raises ORA-00942.

The confusing part is the asymmetry:

SALES in SQL*Plus has DATA_READERS enabled in the session, so roles apply and the query works.
Inside the view, role-derived privileges are switched off, so the same statement fails.

RPT's privileges are irrelevant to the failure - RPT may have SELECT directly on ORDERS, but that has no bearing on how the definer's-rights view resolves SHIP_COSTS. Similarly, a PUBLIC synonym on SHIP_COSTS would not help: name resolution is not the problem, privilege is. The error text is misleading; ORA-00942 is also what Oracle raises when an object exists but you have no privilege on it.

2. Give the minimal fix, and compare it with switching the view to invoker's rights.

Read the worked answer

Minimal fix - grant directly to the owner:

GRANT SELECT ON sales.ship_costs TO sales;   -- executed by SHIP_COSTS's owner or a DBA

Now SALES holds the privilege both through the role and directly; the direct grant is what the definer's-rights view uses. RPT's privileges are untouched, the cost data stays hidden, and no code changes. This is why the pattern 'grant base-table access to the schema that owns the view, directly, not through a role' is standard deployment practice - and why a deployment script that grants through a role works in testing (where a human is logged in) and fails in production under the view.

Alternative - invoker's rights:

CREATE OR REPLACE VIEW sales.v_monthly AUTHID CURRENT_USER AS ...;

The view now runs with RPT's privileges. Role privileges do apply for invoker's rights, so RPT's REPORT_ROLE would be enough - but only if RPT also has SELECT on SHIP_COSTS and ORDERS, whether directly or through a role. That defeats the purpose here: every consumer of the view must now be granted the underlying tables, and the cost data becomes reachable by anyone who can read the view. It also makes results security-dependent: two users querying the same view can see different rows if row-level security or column masking differs.

Choose definer's rights plus direct owner grants when the view is a security boundary; choose invoker's rights when the view is a convenience layer over tables the caller is already entitled to.

3. Which data dictionary queries would prove the diagnosis, and what can RPT legitimately see about a view it has privileges on?

Read the worked answer

As SALES (or a DBA), establish where the privilege comes from:

SELECT * FROM session_roles;                                  -- is DATA_READERS enabled?
SELECT * FROM role_tab_privs WHERE role = 'DATA_READERS';     -- SHIP_COSTS via the role
SELECT grantee, privilege, grantable
FROM   all_tab_privs
WHERE  table_name = 'SHIP_COSTS';                             -- shows SALES, DATA_READERS, PUBLIC
SELECT referenced_owner, referenced_name, referenced_type
FROM   all_dependencies
WHERE  owner = 'SALES' AND name = 'V_MONTHLY';                -- confirms SHIP_COSTS is referenced

The decisive evidence is the pair: ROLE_TAB_PRIVS shows the grantee is the role, and ALL_TAB_PRIVS shows no row with GRANTEE = 'SALES' for SHIP_COSTS. That absence is the bug.

As RPT, after the fix:

USER_TAB_PRIVS lists only grants made directly to RPT.
ROLE_TAB_PRIVS lists grants to RPT's enabled roles.
ALL_VIEWS / ALL_OBJECTS show V_MONTHLY because RPT holds an object privilege on it - a user sees an object in the ALL_* views only if some privilege on it reaches them.
ALL_DEPENDENCIES is restricted to objects the user can access, and ALL_SOURCE-style text requires SELECT_CATALOG_ROLE or ownership - RPT cannot read the view's defining text unless granted DBMS_METADATA/SELECT_CATALOG_ROLE. That is why the DBA, not RPT, must run the dependency query.

One operational note: after the grant, the view may still be marked INVALID; the next reference recompiles it automatically. No ALTER VIEW ... COMPILE is strictly required, but it is a cheap way to confirm the fix without waiting for a user.