Oracle DB: invoker's vs definer's rights for least-privilege code

Problem

PL/SQL runs with one of two sets of privileges: the owner's (definer's rights) or the caller's (invoker's rights). Choosing the wrong one is how a harmless helper procedure ends up with far more reach than anyone intended.

I wanted a decision path I can apply in seconds and defend in a code review.

Options and trade-offs

• Definer's rights (AUTHID DEFINER, the default): privileges come from the owner. Callers need only EXECUTE, and the risk is an owner with broad privileges.
• Invoker's rights (AUTHID CURRENT_USER): the code works within the caller's own privileges, which suits shared code across schemas or tenants. Callers need access to every object it touches.

My recommendation

Make shared, reusable code invoker's rights, so it can never do more than the person calling it. Use definer's rights for a small number of deliberate entry points that expose one action without handing out table privileges, and restrict who may call them.

Worked example

An invoker's rights procedure resolves unqualified names at run time in the caller's schema, so it reads the caller's own orders table.

CREATE OR REPLACE PROCEDURE app_util.list_my_orders
  AUTHID CURRENT_USER
AS
BEGIN
  FOR r IN (SELECT order_id, status FROM orders) LOOP
    DBMS_OUTPUT.PUT_LINE(r.order_id || ' ' || r.status);
  END LOOP;
END;
/

A definer's rights entry point is the opposite. It performs one privileged action on the owner's data, and ACCESSIBLE BY restricts which program units may call it.

CREATE OR REPLACE PROCEDURE app_admin.close_period (p_period IN NUMBER)
  AUTHID DEFINER
  ACCESSIBLE BY (PACKAGE app_api.billing)
AS
BEGIN
  UPDATE fiscal_periods SET status = 'CLOSED' WHERE period_id = p_period;
END;
/
GRANT EXECUTE ON app_admin.close_period TO billing_role;

A few facts that help in practice. Roles are disabled inside definer's rights code, so the owner needs direct grants. Since Oracle Database 12c, an invoker's rights unit can use the caller's privileges only if its owner has the INHERIT PRIVILEGES privilege on the caller, or INHERIT ANY PRIVILEGES. Anonymous blocks run with the privileges of whoever executes them.

Checklist

1. Decide who should own the effective privileges before writing the code.
2. For invoker's rights, confirm callers have the object privileges the code needs.
3. Keep definer's rights units small, and keep the owner's privileges minimal.
4. Use ACCESSIBLE BY to limit callers of sensitive procedures.
5. Document entry points and who calls them, and audit executions.
6. Review dynamic SQL, because it resolves names at run time.

Where this doesn't apply

Anonymous blocks already behave like invoker's rights, so the choice does not apply to them.

One honest limitation

Invoker's rights with static SQL expects matching objects to exist for the owner at compile time and for the caller at run time. Plan schema design so those differences do not produce subtle errors.

Sources

• Oracle Database PL/SQL Language Reference: Invoker's Rights and Definer's Rights (AUTHID Property)
• Oracle Database Security Guide: Managing Security for Definer's Rights and Invoker's Rights
• Oracle Database PL/SQL Language Reference: ACCESSIBLE BY Clause