Definer's Rights Procedure Fails with ORA-00942 Despite a Role Grant
APP owns BONUS and CALC_BONUS, a definer's-rights procedure that reads HR.EMPLOYEES and inserts into BONUS. APP was granted SELECT on HR.EMPLOYEES only through the role HR_READER, which is enabled by default for the APP account. CALC_BONUS compiled successfully in a SQL*Plus session logged in as APP. When REPORT_USER, who holds EXECUTE on APP.CALC_BONUS, runs it, the procedure fails with ORA-00942 on the statement that selects HR.EMPLOYEES. One DBA proposes GRANT SELECT ANY TABLE TO APP; another proposes GRANT SELECT ON hr.employees TO app. REPORT_USER also has SELECT on APP.BONUS through role R_REPORT and can query the table interactively, but an UPDATE against APP.BONUS fails with ORA-01031.
1. Diagnose the ORA-00942, explain why compilation succeeded, and evaluate the two proposed grants.
Read the worked answer
Diagnosis. CALC_BONUS is a definer's-rights unit: at run time it executes with the privileges of APP, its owner. Inside definer's-rights PL/SQL, roles are not enabled - Oracle checks only privileges granted directly to APP, plus PUBLIC. APP holds SELECT on HR.EMPLOYEES only through HR_READER, so the check fails and the reference to HR.EMPLOYEES raises ORA-00942.
Why it compiled. Compilation happened in a SQL*Plus session logged in as APP, where HR_READER was enabled. The compiler could resolve HR.EMPLOYEES, so no error was reported. The failure is at execution time, which is why the procedure appears fine to its owner but broken for REPORT_USER.
Fix: direct grant.
GRANT SELECT ON hr.employees TO app;
No recompilation is strictly required, because privileges are checked at execution, but recompiling is harmless and clears any stale state. This preserves least privilege: APP gains read access to exactly one table.
Why not SELECT ANY TABLE. It would work, but it gives APP read access to every table in the database, including other schemas' sensitive data, and it survives future mistakes. It also shows up as a finding in privilege audits. Reject it unless there is a documented, narrowly scoped reason.
Checklist. Before shipping a definer's-rights procedure that touches another schema, log in as the owner, run SET ROLE NONE (or check SESSION_ROLES), and test the SQL. That reproduces the runtime privilege environment.
2. The team asks whether declaring CALC_BONUS AUTHID CURRENT_USER is better. Explain what changes and the trade-offs.
Read the worked answer
AUTHID CURRENT_USER changes the privilege model, not the SQL.
CREATE OR REPLACE PROCEDURE app.calc_bonus (p_dept NUMBER)
AUTHID CURRENT_USER IS
BEGIN
INSERT INTO app.bonus (employee_id, amount)
SELECT e.employee_id, e.salary * 0.1
FROM hr.employees e
WHERE e.department_id = p_dept;
END;
/
What changes.
- Privileges are those of the invoker, REPORT_USER, and roles are enabled. REPORT_USER therefore needs SELECT on HR.EMPLOYEES and INSERT on APP.BONUS, granted directly or through a role.
- Unqualified names resolve in the invoker's schema, not the owner's. Always qualify schema.object in invoker's-rights code; relying on synonyms or the invoker's objects is a classic source of runtime surprises.
- Definer's rights concentrates privilege in one place: grant EXECUTE widely and the owner's rights do the work. Invoker's rights pushes the grant requirement to every caller, which is more granular but harder to administer.
Security trade-off. With definer's rights, dynamic SQL built from user input runs with the owner's privileges, which can be devastating if it is injectable. With invoker's rights, injected SQL can only do what the caller could already do. For procedures that build dynamic SQL, invoker's rights is the safer default; for tightly scoped batch jobs, definer's rights plus a direct grant is usually simpler.
Do not solve it by granting APP DBA or any ANY-privilege. That converts a grant-design problem into an audit finding.
3. Explain why REPORT_USER can SELECT but not UPDATE APP.BONUS, and describe the two distinct ways a role can fail to authorise an operation.
Read the worked answer
Two separate reasons a role can fail to authorise an operation.
Wrong privilege type. If R_REPORT carries only SELECT on APP.BONUS, an UPDATE needs the UPDATE object privilege on that table (or UPDATE ANY TABLE, which you should not grant). The error is ORA-01031: insufficient privileges. The fix is
GRANT UPDATE ON app.bonus TO r_report;, not a re-login.Roles are disabled in definer's-rights PL/SQL. The same user who can UPDATE interactively will get ORA-01031 if the identical statement lives in a definer's-rights procedure owned by another schema, because the role is not enabled inside that unit. Contrast this with the earlier SELECT case: there the missing direct grant produced ORA-00942 because the object could not be resolved, whereas here the object resolves but the privilege is absent, so Oracle reports ORA-01031. Both trace back to the same rule.
Diagnostic queries.
SELECT * FROM session_privs; -- system privileges
SELECT * FROM session_roles; -- roles enabled right now
SELECT * FROM user_tab_privs_recd; -- direct object grants
SELECT * FROM user_role_privs;
Note that these views reflect the session, not an enclosing definer's-rights unit, so reproduce the failure from inside the procedure or approximate it with SET ROLE NONE.
Judgement. Grant the object privilege to the role for interactive use, and, if a definer's-rights unit needs it, also grant it directly to the owning schema. Never reach for UPDATE ANY TABLE as a shortcut.