How to Restrict Callers with ACCESSIBLE BY in PL/SQL

Some program units are helpers that only one package or procedure should ever call: a routine that writes audit records, or one that bypasses checks the public API performs. Granting EXECUTE cannot restrict calls within the same schema. ACCESSIBLE BY can: it lists the units allowed to call a subprogram, and refuses everyone else, even the owner's anonymous blocks.

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 name (params)
  accessible by ([procedure | function | package | trigger | type] unit [, ...])
is ...

The check happens at compile time: a unit that is not on the list cannot even compile a call to the restricted one.

Refused for Others

AUDIT_WRITE may be called only by the procedure RAISE_SALARY. An anonymous block of the owner tries to call it.

Example:

create or replace procedure audit_write (p_text varchar2)
  accessible by (procedure raise_salary)
is
begin
  null;
end;
/
begin
  audit_write('called from an anonymous block');
end;
/

Output:

Procedure AUDIT_WRITE compiled

  audit_write('called from an anonymous block');
  *
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00904: insufficient privilege to access object AUDIT_WRITE

The call fails with PLS-00904, insufficient privilege to access object. The example drops the procedure afterward.

Allowed for the Listed Caller

Example:

create or replace procedure audit_write2 (p_text varchar2)
  accessible by (procedure raise_salary2)
is
begin
  dbms_output.put_line('audit: ' || p_text);
end;
/
create or replace procedure raise_salary2 (p_emp number) is
begin
  audit_write2('raise for employee ' || p_emp);   -- the allowed caller
end;
/
exec raise_salary2(161)

Output:

Procedure AUDIT_WRITE2 compiled

Procedure RAISE_SALARY2 compiled

audit: raise for employee 161

PL/SQL procedure successfully completed.

The listed procedure calls the helper normally. The example creates and drops its own pair of procedures.

Things to Know

• The listed units do not have to exist when the restricted unit is created.
• ACCESSIBLE BY can be set on a whole package, or on individual subprograms in a package specification.
• It complements privileges: privileges control which users may call a unit, ACCESSIBLE BY which code may.

Related Guides

• How to Use AUTHID in PL/SQL

Conclusion

ACCESSIBLE BY whitelists the units that may call a subprogram, enforced at compile time, so internal helpers cannot be called from anywhere else. Use it to protect code that must only run behind your package's public API.