Role Privileges in a Definer's-Rights Procedure and a Moving Report Snapshot

This is privilege resolution, not a missing view. In a definer's-rights procedure, the default AUTHID DEFINER, name resolution and privilege checks run as the owner, ETL. Roles are disabled during definer's-rights PL/SQL execution; only direct grants to ETL count. ETL has SELECT on APP.V_ORDERS only through ETL_ROLE, so the procedure cannot see the view. RPT's interactive SELECT works because a normal SQL session has roles enabled.

Options:

1. GRANT SELECT ON app.v_orders TO etl; This is the smallest change and keeps definer's rights, so all callers share ETL's access.
2. Recreate the procedure with AUTHID CURRENT_USER. Then RPT's privileges are checked at runtime. Roles are enabled for invoker's-rights execution, but relying on a role grant is fragile if the procedure is later called from another definer's-rights program; a direct grant to RPT is safer.
3. Move the view into ETL or create a controlled interface object owned by ETL. Do not grant SELECT ANY TABLE. Verify with ALL_TAB_PRIVS and SESSION_PRIVS, and confirm the view owner has privileges on the underlying tables. After fixing, test as RPT, not as ETL, because the failure only appears for callers.

Oracle provides statement-level read consistency. Each query sees the database as of its own start time, so a load committing between query two and query three becomes visible to query three. The report is mixing snapshots. A read-only transaction extends consistency to the whole transaction:

SET TRANSACTION READ ONLY;
-- run all four SELECTs
COMMIT;  -- or ROLLBACK

It must be the first statement in the transaction. It sees only changes committed before it began and ignores later commits. Any DML raises ORA-01456, so use a dedicated reporting session, not a pooled connection that might later run updates. A serializable transaction gives the same snapshot semantics but permits DML and can raise ORA-08177 on conflict. If the report cannot hold a transaction open, use Flashback Query with the same timestamp or SCN on every query:

SELECT ... FROM app.v_orders AS OF TIMESTAMP :snapshot_ts;

That requires undo retention long enough to cover the 25-minute window. Also confirm the four queries actually run on the same session; a pool that reclaims the connection between queries starts a new transaction and defeats either approach.

Role grants are evaluated at logon and when roles are explicitly enabled, so revoking RPT_ROLE does not strip the already-enabled role from an existing session. RPT can continue to exercise privileges obtained through that role until reconnecting or until a SET ROLE statement changes the enabled roles. A new session after 09:00 will not have the role. If immediate removal is required, the DBA can alter the user's default roles, issue SET ROLE NONE in the session, or kill the session; do not assume the revoke is instantaneous for connected users.

A direct object grant behaves differently. Revoking a directly granted object privilege takes effect for subsequent privilege checks in existing sessions; a statement that has not yet parsed fails, although an already-open cursor may continue until it is reprepared. This asymmetry matters for incident response: revoking a role is not a reliable kill switch for a live session, while revoking a direct grant is closer to one. For the procedure problem in this scenario, prefer a direct grant to ETL or an invoker's-rights design, and document that role-based grants are unavailable inside definer's-rights PL/SQL. Test both the connected-session and reconnect paths before declaring the security change complete.