How to deal with DBMS_RLS

These last weeks I am trying to get the VPD option (Virtual Private Database) enabled for my application. This time as a developer with no DBA or SYS privileges. I am used to being a DBA for the databases I work on and am used to being able to get the fullest out of the Oracle database. For one, because I think Oracle provides us with a great deal of built-in features that do things more efficiently than we can do ourselves (more efficient, built-in kernel code) and also especially because it seems useless and time-consuming to write code that is already supplied by built-in packages. Now these provided features may not all be as intuitive as we want to or lack some important features, but that is where we can spend some time extending them or making them more user-friendly by creating “wrapper” packages: packages you write that hide the complexity or unfriendliness of a built-in package or limit the features you can use. Because there are quite some packages out there that contain functions or procedures or even parameters that you do not want to give to your developers or end users.

An example of such a package that is too powerful to grant to your end-users as a whole is DBMS_RLS. This package enables you to put Row Level Security (and even Column Level Security) on your schema objects. When you take a look at most tutorials available about the VPD option, all of them simply state that you should grant EXECUTE on DBMS_RLS to the schema owner so that it can add, drop, enable, disable policies on its objects to limit the data an end-user of the object can see or manipulate.

First, I love the feature. It is a way of declaratively filtering data based on business criteria dependent on the logged in user/location/terminal/anything that can be set or queried as a context variable. It is so much cleaner than having the same kind of predicates sitting around in all parts of the application, almost knowing for sure that:

  • something will have been missed somewhere and people see data they should not
  • or will change in the future and turns in to a maintenance nightmare
  • or can be circumvented

Why is DBMS_RLS too powerful in “my” opinion? At least I made it my opinion also after consulting with a DBA at the company where I need the VPD feature, who knows what it is like to manage several hundreds of databases and knows the challenge of maintaining some level of standardization without compromising security but on the other hand providing application developers with a toolset they can solve their problems with. I did not think of it at first when asking for EXECUTE privileges on DBMS_RLS for my application, but he mentioned that DBMS_RLS is not preventing me from hurting someone else’s policies:

If a database contains schema owners SCOTT and TOINE and both need VPD for their schema objects…the tutorials state that both should get execute privileges on DBMS_RLS. But that does not prevent SCOTT from dropping policies of user TOINE. Me as TOINE would hate the idea that someone else can touch my ‘rules’. As it turns out there is more about securing DBMS_RLS that goes beyond this simple article (SQL Injection and XMLDB or some other built-in packages can dig a hole into your beautiful VPD security, see for such an example http://www.oracle-dox.net/Wiley-The.Oracle.Hacker.s.Hand/final/BBL0043.html or Pete Finnigan’s presentation http://www.petefinnigan.com/Oracle_Security_VPD_2009_6slides.pdf). Also there is a lot of useful information to be found for a hacker in a number of data dictionary views.

So how would a DBA that needs to standardize hundreds of databases enable a developer to use DBMS_RLS? Several options, one better than the other:

  • He or she could execute any policy manipulation (enable/disable/add/drop) on behalf of the developer. This requires tickets to be raised and will frustrate the developer who has to wait for the DBA to find time to execute that task out of his growing queue of such tasks
  • I like the wrapper package idea: create a wrapper package on top of DBMS_RLS, it can be as simple as providing all subprograms, but then WITHOUT the object_schema parameter. Instead of having the developer provide the object_schema, YOU take control and allow that developer to only manipulate policies on objects of its own. Instead of a grant EXECUTE on DBMS_RLS, a grant EXECUTE on the wrapper package is provided.

So DBMS_RLS looks like (In Oracle 10R2):

CREATE OR REPLACE PACKAGE SYS.dbms_rls AS
  STATIC                     CONSTANT   BINARY_INTEGER := 1;
  SHARED_STATIC              CONSTANT   BINARY_INTEGER := 2;
  CONTEXT_SENSITIVE          CONSTANT   BINARY_INTEGER := 3;
  SHARED_CONTEXT_SENSITIVE   CONSTANT   BINARY_INTEGER := 4;
  DYNAMIC                    CONSTANT   BINARY_INTEGER := 5;
  DV_INTERNAL                CONSTANT   BINARY_INTEGER := 10;
  ALL_ROWS                   CONSTANT   BINARY_INTEGER := 1;

  PROCEDURE add_policy(object_schema   IN VARCHAR2 := NULL,
                       object_name     IN VARCHAR2,
                       policy_name     IN VARCHAR2,
                       function_schema IN VARCHAR2 := NULL,
                       policy_function IN VARCHAR2,
                       statement_types IN VARCHAR2 := NULL,
                       update_check    IN BOOLEAN  := FALSE,
                       enable          IN BOOLEAN  := TRUE,
                       static_policy   IN BOOLEAN  := FALSE,
                       policy_type     IN BINARY_INTEGER := NULL,
                       long_predicate BOOLEAN  := FALSE,
                       sec_relevant_cols IN VARCHAR2  := NULL,
                       sec_relevant_cols_opt IN BINARY_INTEGER := NULL); 

  PROCEDURE drop_policy(object_schema IN VARCHAR2 := NULL,
                        object_name   IN VARCHAR2,
                        policy_name   IN VARCHAR2);

[intentionally left out the rest of subprograms)
END dbms_rls;
/

A wrapper package, which can be created in any DBA managed schema (it might be a dedicated VPD admin schema) could look like:

CREATE OR REPLACE PACKAGE VPDADMIN.secure_dbms_rls AS
  STATIC                     CONSTANT   BINARY_INTEGER := 1;
  SHARED_STATIC              CONSTANT   BINARY_INTEGER := 2;
  CONTEXT_SENSITIVE          CONSTANT   BINARY_INTEGER := 3;
  SHARED_CONTEXT_SENSITIVE   CONSTANT   BINARY_INTEGER := 4;
  DYNAMIC                    CONSTANT   BINARY_INTEGER := 5;
  DV_INTERNAL                CONSTANT   BINARY_INTEGER := 10; 
  ALL_ROWS                   CONSTANT   BINARY_INTEGER := 1;

  PROCEDURE add_policy(object_name   IN VARCHAR2,
                       policy_name     IN VARCHAR2,
                       function_schema IN VARCHAR2 := NULL,
                       policy_function IN VARCHAR2,
                       statement_types IN VARCHAR2 := NULL,
                       update_check    IN BOOLEAN  := FALSE,
                       enable          IN BOOLEAN  := TRUE,
                       static_policy   IN BOOLEAN  := FALSE,
                       policy_type     IN BINARY_INTEGER := NULL,
                       long_predicate BOOLEAN  := FALSE,
                       sec_relevant_cols IN VARCHAR2  := NULL,
                       sec_relevant_cols_opt IN BINARY_INTEGER := NULL);

  PROCEDURE drop_policy(object_name   IN VARCHAR2,
                        policy_name   IN VARCHAR2);

  [intentionally left out the rest of subprograms)
END dbms_rls;
/ 

In the body of this package the subprograms can simply call DBMS_RLS. Add_policy and drop_policy like this:

CREATE OR REPLACE PACKAGE BODY VPDADMIN.secure_dbms_rls AS
     PROCEDURE add_policy(object_name   IN VARCHAR2,
                          policy_name     IN VARCHAR2,
                          function_schema IN VARCHAR2 := NULL,
                          policy_function IN VARCHAR2,
                          statement_types IN VARCHAR2 := NULL,
                          update_check    IN BOOLEAN  := FALSE,
                          enable          IN BOOLEAN  := TRUE,
                          static_policy   IN BOOLEAN  := FALSE,
                          policy_type     IN BINARY_INTEGER := NULL,
                          long_predicate BOOLEAN  := FALSE,
                          sec_relevant_cols IN VARCHAR2  := NULL,
                          sec_relevant_cols_opt IN BINARY_INTEGER := NULL)
  IS
   BEGIN
     sys.dbms_rls.add_policy(USER,
                             object_name,
                             policy_name,
                             function_schema,
                             policy_function,
                             statement_types,
                             update_check,
                             enable,
                             static_policy,
                             policy_type,
                             long_predicate,
                             sec_relevant_cols,
                             sec_relevant_cols_opt);
   END; 
  [intentionally left out the rest of subprograms)
END dbms_rls;
/ 

The only disadvantage I can think of is portability of the code: if the target database is not following the same principle, you would have to alter your package name. But if you call your add_policy program using named parameters instead of positional…then the package name may be the only thing you would have to change…since DBMS_RLS takes the current user as the default if none is provided:

BEGIN
   VPDADMIN.secure_dbms_rls.add_policy
                             (object_name          => 'mytable',
                              policy_name          => 'mypolicy',
                              function_schema      => 'myschema',
                              policy_function      => 'mypackage.myfunction',
                              statement_types      => 'select, update, delete'
                             );
END;

I am currently in the middle of discussing this wrapper option…I hope to be able to use VPD as wanted soon. Anyone feeling differently on grants on DBMS_RLS?

About these ads

3 thoughts on “How to deal with DBMS_RLS

  1. Auditing the privilege may be a suitable compromise in some situations. Yes, SCOTT might still be able to play with TOINE’s policies, but it would be traceable.

    • Hi Gary, I guess there may be situations where it is not as big a deal, nevertheless I would prefer preventing a situation from happening than trace who did something wrong. But there are environments thinkable where it may be a good and quick setup, especially when VPD is not used primarily to guard very sensitive data, but mostly as a centralized filtering mechanism.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s