12c - Whitelists...

Starting in Oracle Database 12c - you can limit down to the package/procedure or function level what bits of code may invoke other bits of code in the database.  This process is called "white listing" and can be used to implement the concept of least privileges in your database.

In the past - if a given schema A had packages P1, P2, P3, ... Pn - then any of P1 .. Pn could invoke any function or procedure exposed in the specification of any of P1 .. Pn.  There would be no way to stop one bit of code from invoking any other bit of code.  This could have implications in the area of SQL Injection.  If one of the packages was subject to a SQL Injection bug - then that package could be used to execute ANY of the existing bits of code in that schema.  Additionally - even if none of the packages in that schema had a SQL Injection bug - but the application connected to the database itself did, an attacker could use that bug to execute any bit of code in that schema.

With the white list approach, the only way to execute a given piece of code would be to run it from a specific set of compiled units.  You cannot execute a white listed unit from the top level, it must be called by some specific set of units.  Now a SQL injection bug in the application cannot execute this code (it would have to call it as a top level call - but a white listed unit cannot be called that way).  And even further - a SQL injection bug in the code stored in the database will not be able to execute this white listed code (unless of course it was on the white list).

This is all accomplished with the new "accessible by" clause.  The use of this clause on a unit will restrict the calling set of units to be those in the accessible by clause and the unit itself (a units code is always accessible to itself).

For example, I'll create a package that is to be used only by procedure P1 (and itself) in some schema:

ops$tkyte%ORA12CR1> create or replace package my_pkg
  2  accessible by (p1)
  3  as
  4      procedure p;
  5      function f return number;
  6  end;
  7  /
Package created.

ops$tkyte%ORA12CR1> create or replace package body my_pkg
  2  as
  3
  4  procedure p
  5  is
  6  begin
  7      dbms_output.put_line( 'hello world' );
  8  end;
  9
 10  function f return number
 11  is
 12  begin
 13          p;
 14          return 42;
 15  end;
 16
 17  end;
 18  /
Package body created.

as you can see - I've used the accessible by clause in the package specification.  This will restrict this package to be invoked only by procedure P1 or the package MY_PKG in this same schema. We can see that MY_PKG can invoke itself since function F calls procedure P in that same package.  Additionally - we can see that only procedure P1 outside of MY_PKG can invoke the functionality of this package. For example:

ops$tkyte%ORA12CR1> create or replace procedure p1
  2  as
  3  begin
  4      my_pkg.p;
  5  end;
  6  /

Procedure created.

ops$tkyte%ORA12CR1> create or replace procedure p2
  2  as
  3  begin
  4      my_pkg.p;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

ops$tkyte%ORA12CR1> show errors
Errors for PROCEDURE P2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/5      PL/SQL: Statement ignored
4/5      PLS-00904: insufficient privilege to access object MY_PKG

Procedure P1 successfully compiles and would be able to invoke MY_PKG.P but P2 cannot.  Furthermore, an attempt to execute MY_PKG as a top level call will fail:

ops$tkyte%ORA12CR1> exec my_pkg.p
BEGIN my_pkg.p; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object MY_PKG
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

It should be noted that the acccessible by clause list is not evaluated at compile time for the unit being protected.  In the above example - we stated MY_PKG would be accessible by P1, before P1 was created.  That means you can put just about anything you want in the accessible by clause without raising an error (so be careful).

The accessible clause can be used across schemas as well.  If we recreate the package specification as:

ops$tkyte%ORA12CR1> create or replace package my_pkg
  2  accessible by (p1,scott.p)
  3  as
  4      procedure p;
  5      function f return number;
  6  end;
  7  /

Package created.

ops$tkyte%ORA12CR1> grant execute on my_pkg to scott;
Grant succeeded.

we'll be able to successfully compile and execute the code from SCOTT.P:

ops$tkyte%ORA12CR1> connect scott/tiger
Connected.

scott%ORA12CR1> create or replace procedure p
  2  as
  3  begin
  4          ops$tkyte.my_pkg.p;
  5  end;
  6  /
Procedure created.

scott%ORA12CR1> exec p
hello world

PL/SQL procedure successfully completed.

scott%ORA12CR1> exec ops$tkyte.my_pkg.p
BEGIN ops$tkyte.my_pkg.p; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object MY_PKG
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

but note that once again - SCOTT cannot invoke this package from the top level either - meaning if the SCOTT schema has some SQL injection issues - we've removed the ability for an attacker to invoke OPS$TKYTE.MY_PKG from that schema.

Next time I'll be taking a look at another new PL/SQL security feature - code based access control, the ability to grant ROLES to code...