Search

Top 60 Oracle Blogs

Recent comments

Oracle 12c – PL/SQL “White List” via ACCESSIBLE BY

Oracle 12c now provides a “white list” capability for procedure/function execution by means of a new PL/SQL ACCESSIBLE BY clause used to control access. ACCESSIBLE by specifies which objects are able to reference a PL/SQL object directly (sometimes called a “white list”). The ACCESSIBLE BY clause may be added to packages, procedures, functions, and types allowing an additional layer of security. Even if by some accident an intruder obtains permissions to use stored PL/SQL; the ACCESSIBLE BY rules will not allow any access that violates the specified rules.

Example Coding

Here’s an Example of ACCESSIBLE BY in a simple function:

create or replace function TIMES_10 (inval in number)
 return number
 accessible by (CALLER1,CALLER3)
 is
begin
 return inval * 10;
end;
/

Note: The “TIMES_10” routine may only be accessed by PL/SQL objects with the name “CALLER1” or “CALLER2” – it may not even be called from the SQL*Plus command line or SQL Developer.

Example Calls

Here are three example calls to the “times_10” function:

 create or replace procedure CALLER1 as
   anumber number := 1;
 begin
   anumber := times_10(anumber);
 end;
 /
 create or replace procedure CALLER2 as
   anumber number := 1;
 begin
   anumber := times_10(anumber);
 end;
 /
 create or replace procedure CALLER3 as
   anumber number := 1;
 begin
   anumber := times_10(anumber);
 end;
 /
  • The first execution from “CALLER1” runs fine.
  • The second execution from “CALLER2” aborts and raises an error.
    PLS-00905: object JOHN.CALLER2 is invalid
  • The third execution from “CALLER3” also runs fine.
    exec caller3; — runs fine

Clearly, ACCESSIBLE BY adds a useful layer of security to what is already in place with Oracle’s normal permission. You should consider using it where applicable in your shop.