0 
0 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.
Related posts:
VPD part 2; The added functionality in 10g 
Poor man's VPD – Virtual Private Database before 8i and in Standard Edition Databases 
Dynamic SQL Pivoting – Stealing Anton’s Thunder Putting Analytical Functions to good use – find tables with multiple foreign keys to the same referenced table Used port numbers by the Oracle OEM agent
The idea of VPD is that one is able to implement RLS with the use of application_context.
To have more control over the application_context one could extend the package setting this
context with additional test procedures.
Since VPD uses policies written in PL/SQL it is up to the person writing the policy to make sure
that the returning clause will not “muck” up the performance. I admit that it is difficult when
more complex clauses are returned or when several policies are registed to the table/view.
We use VPD for a long time and I don’t know about any limitation in the number of rows.
But other issue is, that the execution plan of a query may change if the session context changes. This may muck things up when the policy returns more complicated conditions. And the development tools (like PL/SQL Developer we use) sometimes don’t allow to set the context when explaining the plan.
Hi Brian,
Up till now I never ran into this problem. Perhaps the number of record I worked with were not big enough. I would however be very interested in an example that supports your statement.
I looked at VPD some time ago, and in my experimentation I discovered that there was a top limit to the number of rows that could be displayed. It’s been long enough ago that I don’t recall what that number was…it was pretty big, but unfortunately, not big enough to make VPD useful to my customer. Do you know if that upper range has been extended? For example, in a large corporation with thousands of employees per division, a division supervisor should only be able to access data on employees within his division, but the VPD couldn’t handle a row return that large. (Hopefully I’m remembering this right!)