How to Use AUTHID in PL/SQL

Whose privileges does a stored procedure use: those of its owner, or those of whoever calls it? The AUTHID clause decides. Definer's rights, the default, let the owner give controlled access to its tables through procedures. Invoker's rights make a procedure work on the caller's own objects, which suits shared utilities.

Code for This Guide

The main examples are in the examples/subprograms folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

create [or replace] {procedure | function | package} name ...
  authid {definer | current_user}
is ...

An Invoker's Rights Function

COUNT_MY_TABLES counts the tables in USER_TABLES. With AUTHID CURRENT_USER, it counts the caller's tables; with the default, it would count the owner's tables for every caller.

Example:

create or replace function count_my_tables return number
  authid current_user                -- runs with the caller's privileges
is
  v_count number;
begin
  select count(*) into v_count from user_tables;
  return v_count;
end;
/
select object_name, authid from user_procedures where object_name = 'COUNT_MY_TABLES';
select count_my_tables as tables from dual;

Output:

Function COUNT_MY_TABLES compiled

OBJECT_NAME        AUTHID
__________________ _______________
COUNT_MY_TABLES    CURRENT_USER

   TABLES
_________
       14

USER_PROCEDURES shows the AUTHID of each unit. Called by NIMBUS, the owner, it returns 14. The example drops the function afterward.

Choosing

• Use definer's rights for APIs: grant EXECUTE on a package, not privileges on the tables, so callers can change data only through your rules.
• Use invoker's rights for utilities that should act on whichever schema calls them, such as a generic export or a dictionary report.
• In definer's rights code, privileges granted through roles do not count: grant them directly to the owner.

Related Guides

• How to Grant Object Privileges in Oracle
• Oracle SYS_CONTEXT Function

Conclusion

AUTHID DEFINER runs a unit with its owner's privileges and names, the basis of controlled APIs; AUTHID CURRENT_USER runs it with the caller's, for shared utilities. Remember that roles are disabled in definer's rights code.