Wednesday, July 17, 2013

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



POST A COMMENT

9 Comments:

Blogger Rahul Puttagunta said....

Since I do not have 12c installed to play around with (yet), I understand that you can even use this logic to just make sure only a certain packages are available to call by a client even though they are logged onto the schema? Although, in our implementations, we don't have Applications log onto the schema that owns the data and/or the code anyway. I can kind of guess (other than what is mentioned in the article) where this might be helpful. Thank you.

PS: I wish Oracle drops the character in it's DB versioning. /rant.

Wed Jul 17, 05:18:00 PM EDT  

Blogger Thomas Kyte said....

@Rahul

only packages that do not have an accessible by clause can be called by a client. So yes, you can make it so that a schema that has 100 procedures might have only one procedure that is callable by a client program.


I don't know, I've grown fond of the i's, g's and c's over time :)

Wed Jul 17, 05:21:00 PM EDT  

Blogger Rahul Puttagunta said....

Thank you sir.

Does this also mean that this concept is bringing pseudo-security (client cannot call the standalone procedure anyway) argument for people who would like to put standalone procedures instead of packages? Although that was not the intended use of it.

Yes, we can always argue that packages give you a lot more than SPs, of course.

Wed Jul 17, 05:41:00 PM EDT  

Blogger Thomas Kyte said....

@Rahul

I used the word "unit" in my writeup on purpose. This works with units which can be packages, functions, procedures.

I strongly believe in real life you only use packages (modular coding and all) and rarely standalone functions/procedures.

I would not call it pseudo-security. It is "real" security. You are protecting a range of units from being invoked by someone exploiting a SQL injection bug for example. there is nothing pseudo about that!

Wed Jul 17, 05:44:00 PM EDT  

Anonymous Gabe said....

"In the above example - we stated MY_PKG would be accessible by P, before P was created."

P1.

Wed Jul 17, 10:03:00 PM EDT  

Blogger Thomas Kyte said....

@Gabe,


thanks! I've updated that.

Thu Jul 18, 07:43:00 AM EDT  

Blogger Venkatalakshmi B said....

Hi Tom,
My understanding : By using 'ACCESSIBLE_BY' clause, we are only restricting the function from not being used by anyother function.

Question : Is it same as subfunction.??

If yes,then what is the improvement using ACCESSIBLE_BY clause..??

If No,Can u please explain the difference.???

Fri Jul 19, 03:47:00 AM EDT  

Blogger Thomas Kyte said....

@Venkatalakshmi

it is not at all like a nested function - not at all like a function in a function.

A function in a function has severely limited scope - the nested function can only be called by the parent function and nothing else.


With whitelists, I can expose a package to N other packages. Only those N other packages may use the features of my white listed package. If I tried to do this with nest subfunctions or even as private methods in a package body - I would have had to of copied that code N time.

Accessible by allows you to specific a list of units that may "see" and execute a given unit. Much more flexible than nesting a function in a function.

Fri Jul 19, 08:21:00 AM EDT  

Anonymous Anonymous said....

Nice!

Wed Sep 04, 03:40:00 PM EDT  

POST A COMMENT

<< Home