Virtual Private Database, securing your data

I was not that aware of the fact that there were other (better) ways to secure data without using functions in views until I was involved in a project where we needed to setup a better authorisation mechanism than the standard one using views.
“Virtual Private Database (VPD)” is not a new functionality Oracle provides his customers with the release of 10G enterprise edition. The VPD principle was already included in the early versions of Oracle 8. But how does it work?

VPD makes use of row-level security in combination with application context. In other words: it allows you to limit the displayed number of records based upon a security policy which in its turn is implemented using PL/SQL. In fact the PL/SQL function returns a string and is then registered against the table, view or synonym you want to protect using the DBMS_RLS package. So when you query the object Oracle automatically appends the string from the function to the where clause of the query statement resulting in a filtering of the data.

The advantage of using VPD is the fact that it is very flexible. You can add different policies for each DML type. It is transparent to your applications and will work no matter what application is interacting with the data. But the most important argument to using VPD is that the security is directly implemented on the data it protects, it is in the database and can not be bypassed.

How to set it up

Create a function that implements the filtering of your data:

SQL> CREATE OR REPLACE FUNCTION no_dept10 (
2         p_schema IN VARCHAR2,
3         p_object IN VARCHAR2)
4         RETURN VARCHAR2
5         IS
6         BEGIN
7            RETURN ('deptno!=10');
8         END;
9         /

To protect the Scott.Emp table from displaying department 10 the above created function is associated to the table using the DBMS_RLS.ADD_POLICY procedure

SQL> exec DBMS_RLS.add_policy
2         (Object_schema => 'SCOTT'
3          ,object_name => 'EMP'
4          ,policy_name => 'no_dept10'
5          ,policy_function =>'no_dept10');

And you are done. You will now not be able to see the records in the EMP table which have a deptno equal to 10.

When I issue:

SQL> Select distinct deptno from emp;

      DEPTNO
-------------
                  20
                  30

Department 10 is not displayed.
The RLS example above shows you that there is no requirement or dependency on the use of application context or the user issuing the statement. It is therefore trivial.
Adding application context to the policy will make it less trivial and allows you to implement a more “tailored” security.

Start by creating a “global” context – using the ACCESSED
GLOBALLY clause. Note that you will need to have the privilege Create any context to execute this command

SQL> create or replace context App_Ctx using My_pkg
  2  ACCESSED GLOBALLY
  3  /

Context created.
Create the package to use it. This package will have the application logic. Normally you would create the package in a special schema e.g. Sec_manager

SQL> create or replace package my_pkg
  2  as
  3          procedure set_deptno;
  4          procedure clear_deptno;
  5  end;
  6  /

Package created.

SQL> create or replace package body my_pkg
  2  as
  3
  4          procedure set_deptno
  5          is
  6           l_deptno Number;
  7          begin
  8                 Select deptno into l_deptno from emp where
  9                  ename=SYS_CONTEXT('userenv','session_user');
 10
 11                  dbms_session.set_context(namespace=> 'App_Ctx',
 12                                                                  attribute => 'deptno',
 13                                                                   value => l_deptno);
 14        end;
 15
 16          procedure clear_deptno
 17          is
 18          begin
 19                  dbms_session.clear_context(namespace=>'App_Ctx',
 20                                                                      attribute => 'deptno');
 21          end;
 22  end;
 23  /

Create the policy function:

SQL> create or replace function only_dept (
2         p_schema in varchar2 default null,
3         p_object in varchar2 default null)
4         return varchar2 is
5         begin
6         return ('deptno=sycontext(''App_Ctx'',''deptno'')');
7         end;
8        /

Apply the policy to the table

SQL> exec dbms_rls.add_policy(object_schema =>'SCOTT',
2         object_name=>'DEPT',
3         policy_name=>'CorrectDept',
4         function_schema =>'SEC_MANAGER',
5         policy_function => 'only_dept');
6         /

Add the deptno to the context by executing the package procedure my_pkg.set_deptno connected as a user which also exists in the emp table. This execution can be performed by calling the package from a database logon trigger which makes it transparent to the user.

Perform a select deptno from dept and you will see you will only get your own department record back.

SQL> Connect Miller/miller
SQL> select deptno  from dept;
           DEPTNO
---------------
                      10

As you can imagine the possibilities are huge. You can implement and change your policies without creating invalid objects this in contrast to changing view definitions which will result in an ‘INVALID’ status for the objects referencing them.

Conclusion

When you implement VPD on table level you do not have to worry how you approach your data, SQL*Plus, TOAD, PL/SQL Developer, Export or any other way, the VPD function is active and will protect your data (exception is when the user does a logon with the EXEMPT ACESS POLICY).

The performance is better compared to the where clause construction on a view since VPD adds the policy will be applied to the SQL statement before the query is executed and will not result in a SQL/PLSQL context switch.

It is more difficult for a developer to understand how the data is retrieved. The where clause just pops-up out of the blue but on the other hand this is also practical. The security is now in the hands of someone else than the developer who is purely writing the query.

Additional configuration is needed using VPD: policies need to be written and the DBA needs to take care that VPD is implemented in the correct way. You would not want a user to be able to change the policy himself.

VPD can only be used with the Enterprise Edition van de Oracle database.