Wednesday, 27 September 2017

Oracle's Newest PL/SQL Feature 'ACCESSIBLE BY'

Oracle has offered packages for a while now, but until recently it's been difficult, if not impossible, to prevent 'out of context' execution of functions and procedures; in many cases package code is designed to be used within the context of the package, not as stand-alone procedures and functions. In release 12.2.0.1, Oracle has addressed this issue with the ACCESSIBLE BY clause. This clause allows developers to refine the scope of a procedure or function call and generate an error message when that procedure or function is called outside of the package context. Let's build an example to illustrate how this is written and what results are obtained when the context-restricted code is executed in context and out.

ACCESSIBLE BY can restrict specific types of program units or named program units of an unspecified type. The syntax for the clause is:

ACCESSIBLE BY ([unit_kind] [schema.]unit_name)

...where [unit_kind] is an optional parameter indicating PROCEDURE, FUNCTION, PACKAGE, TRIGGER or TYPE, [schema] is the schema where the unit is located and unit_name is the name of the procedure, function or package having restricted access. When [unit_kind] is supplied then a particular package, procedure, function, trigger or type can only be called or used from within the defined context. When [unit_kind] is NOT supplied then any of the listed types can be referenced as long as they have the declared unit_name. We'll start with an access by name example:

SQL> --
SQL> -- Create a procedure only accessible
SQL> -- by a named object
SQL> --
SQL> -- Object can have any supported type
SQL> --
SQL> create or replace procedure my_restrict_proc
  2    accessible by (restrict_fc)
  3  as
  4  begin
  5    dbms_output.put_line('Executed my_restrict_proc.');
  6  end;
  7  /

Procedure created.

SQL>
SQL> --
SQL> -- Create a function only accessible
SQL> -- by a trigger
SQL> --
SQL> create or replace function top_protected_f return number
  2  accessible by (trigger restrict_fc ) as
  3  begiN
  4     return 1.27349;
  5  end top_protected_f;
  6  /

Function created.

SQL>
SQL> --
SQL> -- Create a function
SQL> --
SQL> -- Function calls the procedure that can call
SQL> -- the unit having the restricted name
SQL> --
SQL> create or replace function restrict_fc RETURN NUMBER AUTHID DEFINER IS
  2    FUNCTION g RETURN NUMBER DETERMINISTIC IS
  3    BEGIN
  4       RETURN 1.27349;
  5    END g;
  6  BEGIN
  7    my_restrict_proc;
  8    RETURN g() - DBMS_RANDOM.VALUE();
  9  END restrict_fc;
 10  /

Function created.

SQL>
SQL> --
SQL> -- Execute the function
SQL> --
SQL> SELECT restrict_fc FROM DUAL;

RESTRICT_FC
-----------
  .78662621

Executed my_restrict_proc.

SQL>

Since we didn't try to bypass the 'security' we worked to implement the restricted code executed without error. Notice that we weren't required to declare any unit_kind in the initial ACCESSIBLE BY clause, which allowed us to declare the unit_kind in another PL/SQL program unit 'down the line'; this makes it easier in packages to write an ACCESSIBLE BY clause that can be reused in other packages or stand-alone procedures that use objects of the same name but having different unit_kind definitions.

Let's now look at a package that contains both public (executable by anyone in any context) and private (executable only by the declared program unit) procedures and see what happens when an attempt is made to execute the 'private' procedure outside of its declared context:

SQL>
SQL> set echo on linesize 150 serveroutput on size 1000000
SQL>
SQL> --
SQL> --  Create a package with public and private procedures
SQL> --
SQL> create or replace package my_top_lvl_pkg as
  2       procedure my_first_lvl_proc;
  3       procedure my_next_lvl_proc accessible by (procedure my_top_lvl_proc);
  4  end my_top_lvl_pkg;
  5  /

Package created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> --
SQL> -- Define those procedures and who/what can access them
SQL> --
SQL> create or replace package body my_top_lvl_pkg
  2  as
  3    procedure my_first_lvl_proc as
  4    begin
  5  dbms_output.put_line('Executed my_top_lvl_pkg.my_first_lvl_proc');
  6    end;
  7    procedure my_next_lvl_proc accessible by (procedure my_top_lvl_proc) as
  8    begin
  9  dbms_output.put_line('Executed my_top_lvl_pkg.my_next_lvl_proc');
 10    end;
 11  end;
 12  /

Package body created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> --
SQL> -- Create procedure to execute both package procedures
SQL> --
SQL> create or replace procedure my_top_lvl_proc
  2  as
  3    begin
  4    dbms_output.put_line('my_top_lvl_proc calls my_top_lvl_pkg.my_next_lvl_proc ');
  5    my_top_lvl_pkg.my_next_lvl_proc;
  6    end;
  7  /

Procedure created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> --
SQL> -- Execute procedures as intended, from within the package
SQL> --
SQL> exec my_top_lvl_proc;
my_top_lvl_proc calls my_top_lvl_pkg.my_next_lvl_proc                                                                                               
Executed my_top_lvl_pkg.my_next_lvl_proc           

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> --
SQL> -- Try to call protected procedure stand-alone
SQL> --
SQL> exec my_top_lvl_pkg.my_next_lvl_proc;
BEGIN my_top_lvl_pkg.my_next_lvl_proc; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object MY_NEXT_LVL_PROC

SQL>

We see that any attempt to execute MY_NEXT_LVL_PROC by itself generates an error, which keeps it in the desired context so that undesired results cannot be generated. [As mentioned before some procedures and functions in a package can rely on variables and intermediate results generated by program units within that package; executing program units with such dependencies as 'stand-alone' program units can produce incorrect results.]

Being able to restrict the execution environment on PL/SQL program units that rely on certain conditions to be met is a long overdue feature that should be investigated and used by developers. No longer will programmers have to explain why a procedure or function doesn't work properly when called outside of its intended environment; it simply won't execute outside of the required context. And, to quote Martha Stewart, "That's a good thing."