Explain invoker rights vs. definer rights (AUTHID) in PL/SQL

How it works

By default (definer rights — AUTHID DEFINER, implicit if AUTHID is omitted), a stored subprogram runs with the privileges and schema resolution of the user who owns it, regardless of who calls it, so a shared utility package always sees the definer's own tables. AUTHID CURRENT_USER (invoker rights) instead runs with the calling user's privileges and resolves unqualified object references against the caller's own schema — the pattern used for shared library packages meant to operate on each caller's own data.

CREATE OR REPLACE PACKAGE shared_util AUTHID CURRENT_USER AS
  PROCEDURE archive_old_records;
END shared_util;
/
CREATE OR REPLACE PACKAGE BODY shared_util AS
  PROCEDURE archive_old_records IS
  BEGIN
    -- Resolves 'my_records' against whichever schema is CALLING this
    -- procedure, not the schema that owns shared_util.
    INSERT INTO my_records_archive SELECT * FROM my_records WHERE created_date < ADD_MONTHS(SYSDATE, -12);
    DELETE FROM my_records WHERE created_date < ADD_MONTHS(SYSDATE, -12);
  END;
END shared_util;
/

Related Questions