New clause ACCESSIBLE BY  in Oracle 12C enables to extend standard security. It defines list of database objects (later called accessors) that can call your PL/SQL code.

Clause ACCESSIBLE BY can be defined for following statements:

• CREATE FUNCTION
• CREATE PACKAGE
• CREATE PROCEDURE
• CREATE TYPE

List of accessors that can call your code includes database objects like:

• TRIGGER
• FUNCTION
• PROCEDURE
• PACKAGE
• TYPE

Let’s first create PL/SQL objects with ACCESSIBLE BY clause then try to use them. You can notice that they will compile without problem even if database objects defined in ACCESSIBLE BY don’t exist.

PROCEDURE

PROCEDURE private_prc can be called only by following accessors:

• PROCEDURE public_prc
• TRIGGER public_trg

create or replace procedure private_prc
ACCESSIBLE BY (PROCEDURE public_prc, TRIGGER public_trg)
is
begin
  dbms_output.put_line('Private procedure started');
end;
/

PROCEDURE PRIVATE_PRC compiled

if you try to call it directly exception is raised

exec private_prc

PLS-00904: insufficient privilege to access object PRIVATE_PRC

you need to define PROCEDURE public_prc or TRIGGER public_trg to call it

create or replace procedure public_prc
is
begin
  private_prc;
end;
/

PROCEDURE PUBLIC_PRC compiled

set serveroutput on
exec public_prc

anonymous block completed
Private procedure started

create table test_tbl
(
  id number;
);

table TEST_TBL created.

create or replace trigger public_trg
before insert on test_tbl
begin
  public_prc;
end;
/

TRIGGER PUBLIC_TRG compiled

set serveroutput on
insert into test_tbl values(10);

1 rows inserted.
Private procedure started

FUNCTION

FUNCTION private_prc can be called only by following accessors:

• FUNCTION public_prc

create or replace function private_fnc
return number
ACCESSIBLE BY (FUNCTION public_fnc)
is
begin
  return 0;
end;
/

FUNCTION PRIVATE_FNC compiled

if you try to call it directly exception is raised

select private_fnc from dual;

PLS-00904: insufficient privilege to access object PRIVATE_FNC

you need to define FUNCTION public_fnc to call it

create or replace function public_fnc
return number
is
begin
  return private_fnc;
end;
/
FUNCTION PUBLIC_FNC compiled

select public_fnc from dual;

PUBLIC_FNC
----------
         0

PACKAGE

PACKAGE private_pkg can be called only by following accessors:

• PACKAGE public_pkg
• TYPE public_t

create or replace package private_pkg
ACCESSIBLE BY (PACKAGE public_pkg, TYPE public_t)
is
  g_name varchar2(30) := 'private package';
end;
/

PACKAGE PRIVATE_PKG compiled

if you try to call it directly exception is raised

begin
  dbms_output.put_line(private_pkg.g_name);
end;
/

PLS-00904: insufficient privilege to access object PRIVATE_PKG

you need to define PACKAGE public_pkg ot TYPE public_t to call it

create or replace package public_pkg
is
  g_name varchar2(30) := private_pkg.g_name;
end;
/

PACKAGE PUBLIC_PKG compiled

set serveroutput on
begin
  dbms_output.put_line(public_pkg.g_name);
end;
/

anonymous block completed
private package

TYPE

TYPE private_t can be called only by following accessors:

• TYPE public_t

create or replace type private_t 
ACCESSIBLE BY (TYPE public_t)
as object
(
  v_name varchar2(30)
)
/

TYPE PRIVATE_T compiled

if you try to call it directly exception is raised

declare
  v_id private_t;
begin
  null;
end;
/

PLS-00904: insufficient privilege to access object PRIVATE_T

you need to define TYPE public_t to call it

create or replace type public_t 
as object
(
  v_name private_t
)
/

TYPE PUBLIC_T compiled

declare
  v_id public_t;
begin
  null;
end;
/

anonymous block completed

Of course ACCESSIBLE BY can specify which objects can call our object from other schema as well by adding schema_name

In following example PROCEDURE private_other_user_prc can be called only from schema OTHER_USER by PROCEDURE public_other_user_prc.

Of course you need to remember to grant excute on the procedure to user OTHER_USER as well.

create or replace procedure private_other_user_prc
ACCESSIBLE BY (PROCEDURE other_user.public_other_user_prc)
is
begin
  dbms_output.put_line(user);
end;
/

PROCEDURE PRIVATE_OTHER_USER_PRC compiled

Have a fun 🙂

Tomasz