Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted September 14, 2017

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

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

By David Fitzjarrell

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."

See all articles by David Fitzjarrell



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM