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 188.8.131.52, 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.”