LEARN. PRACTICE. GROW.Reproduce the operation in the right context
In a fictional incident, a batch fails to read a table the DBA can query. Start by identifying session user, current user, current schema, PDB, object and exact operation. A query run as SYS does not reproduce application privileges. The lab has three local identities in a disposable PDB: data owner, code owner and code caller. The original table contains only two synthetic amounts, 125 and 175. The aim is to observe authorization, not process banking data. Capture the complete error and call context before proposing grants. The same object name can resolve to a different schema or PDB, so the name visible in an isolated message may be insufficient.
Changing schema does not grant access
ALTER SESSION SET CURRENT_SCHEMA changes resolution of unqualified references. It does not change SESSION_USER or CURRENT_USER and grants no privileges. In the exercise, the code caller changes its current schema to the PAYMENTS owner. The context query shows the new schema, but both user identifiers remain those of the caller. Querying PAYMENTS still fails with ORA-00942 because no applicable SELECT path exists. This distinguishes “the name resolves” from “the operation is authorized.” In L3 support, adding the correct prefix may solve a resolution error but is not a universal fix for missing access. A synonym also organizes names; it should not be treated as an access grant in itself.
Distinguish a granted role from an enabled role
A role can group privileges and be granted to a user without being enabled in the failing session. SESSION_ROLES shows currently enabled roles; grant records answer a different question. The lab grants a read role and explicitly enables it with SET ROLE. The query then returns 300. SET ROLE NONE removes that path from the session, and the same operation is no longer authorized when there is no direct grant. When comparing an interactive console with a pooled connection, also record session initialization. Oracle documentation distinguishes when object grants take effect from when role grants take effect. Do not assume an old connection automatically reproduces a new connection after every authorization change.
Look for alternative paths when revoking
The exercise grants SELECT directly to the caller while its read role is enabled. It then revokes only the direct grant. The query still returns 300 through the role. When that role is disabled, the operation fails. The first result does not establish that REVOKE was ignored; it demonstrates that authorization had more than one path. In an access review, examine direct grants, roles and other applicable grants in the correct container scope. Removing one role does not revoke all its privileges through other routes. Record which path was removed and which test confirms residual access. The lab uses a simple role; it does not demonstrate every case of nested roles, common grants or additional policies in an actual installation.
Choose a correction with minimal privilege
If an application needs to read one object, a broad grant such as SELECT ANY TABLE can remove the error while expanding access unnecessarily. The correction should match the agreed object, operation, identity and execution context. Also establish whether direct data access or only execution of a controlled interface is required. In the lab, the caller executes an authorized function without acquiring direct SELECT on the table; those permissions differ. Before accepting the change, test the needed operation and an operation that should remain prohibited, retain evidence and define reversal. The administrative session creates, inspects and removes synthetic identities; the application receives no DBA role, ANY privileges or unlimited quota. This is a diagnostic example, not an institutional access policy.
SELECT SYS_CONTEXT('USERENV','SESSION_USER'),
SYS_CONTEXT('USERENV','CURRENT_USER'),
SYS_CONTEXT('USERENV','CURRENT_SCHEMA'),
SYS_CONTEXT('USERENV','CON_NAME') FROM dual;
SELECT role FROM session_roles;
-- Run only the supplied exercise in its labelled disposable container.Revoking direct SELECT did not block reading while the role remained enabled. Changing CURRENT_SCHEMA granted no access.
Common pitfalls
Testing as SYS; confusing schema with user; a granted role as an enabled role; one revocation as removal of every path.
Related topics: Multitenant context · Production handover · Access diagnosis
Access diagnosis requires identifying the operation and every effective path in its execution context.
Reference: ALTER SESSION · 1Z0-183 public objectives inspected 2026-09-30; revision date not published