LEARN. PRACTICE. GROW.Separate interactive success from compilation
The code owner receives SELECT on PAYMENTS only through a role. It can query the sum 300 and execute an anonymous block performing the same read. However, view creation fails and the stored function containing static SQL becomes INVALID. The session’s enabled role does not replace direct privileges required for compilation in this example. In the exercised Oracle Free 26ai version, CREATE VIEW returned ORA-41904, a specific missing-object-privilege message; do not generalize an older code to every version. USER_ERRORS helps explain the function failure. After granting SELECT directly to the code owner and recompiling, the function becomes VALID and the view can be created. The corrective action is bounded, rather than assigning an administrative role to hide the problem.
Understand an interface with definer rights
TOTAL_DR uses AUTHID DEFINER and a schema-qualified reference to the data owner’s table. The code owner has direct SELECT and grants EXECUTE to the caller. That caller obtains 300 by calling the function, but trying to query the table directly still fails. The interface can expose a bounded operation without granting arbitrary reading of all data. This requires careful design: parameter validation, authorized behavior and the exposed operation surface. The example accepts no free-form SQL and does not establish the security of a function concatenating externally supplied commands. Documentation describes context changes when a definer-rights unit enters the call stack. Roles granted to code itself are an additional mechanism not configured by this exercise.
Evaluate invoker rights and inheritance
TOTAL_IR performs the same query with AUTHID CURRENT_USER. In this direct call with the lab’s qualified names, the caller needs an applicable read path at runtime. EXECUTE alone is insufficient: without SELECT the call fails; with the read role enabled it returns 300; with SET ROLE NONE it fails again. There is also a separate INHERIT PRIVILEGES check. The exercise removes the default PUBLIC grant only for the synthetic identity and grants inheritance narrowly to this function’s owner. Removing that grant produces ORA-06598 even when the caller has read access. Restoring it resolves that specific error. Do not turn this example into a recommendation to grant INHERIT ANY PRIVILEGES; the needed relationship can be restricted to the authorized identity.
Test revocation on the dependent operation
When direct SELECT is removed from the code owner, interactive reading still works through the role, but both stored functions become INVALID. In the exercise, the view still appears VALID in the first metadata query. The next query against the view fails with ORA-04063. This sequence shows why a metadata row should not be treated as sufficient access evidence after a change. The report retains step order and actual errors. Restoring the direct grant and recompiling returns the operation to the expected behavior. For an actual change, identify dependencies before revocation, test necessary and prohibited paths, prepare recovery and observe application connections. The exercise does not establish every combination of caches, versions, dynamic code or grants to PL/SQL units.
Prepare a reproducible handover
Give APS a record containing engine version, PDB, identities, object, AUTHID, SQL type, direct grants, enabled roles, the operation that passes and the operation that fails. Distinguish resolution, compilation, execution and inheritance errors instead of summarizing everything as “permissions.” Include authorized diagnostic commands and expected results while avoiding passwords in logs. In this lab, two independent executions passed 38 checks each and removed their users and role. The disposable container and network were also removed. Evidence establishes those behaviors on synthetic data, not readiness of a banking application. This deepening is organized alongside multitenant context and code handover to production; it does not create a new official domain or security weighting for the exam.
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.TOTAL_DR returned 300 without caller SELECT. TOTAL_IR needed active read access and authorized inheritance. The view failed despite the VALID status observed immediately beforehand.
Common pitfalls
EXECUTE as direct SELECT; AUTHID as an automatic compilation fix; ignoring inheritance; VALID as proof of usability after revocation.
Related topics: Multitenant context · Production handover · Access diagnosis
Code authorization depends on phase and context; reproducing the relevant call is part of proving a privilege change.
Reference: Invoker's Rights and Definer's Rights (AUTHID Property) · 1Z0-183 public objectives inspected 2026-09-30; revision date not published