VPD part 2; The added functionality in 10g

In my prior post about VPD I stated that VPD is not a new functionality Oracle added to 10g Enterprise Edition. In fact it was already available in the early Oracle 8 release. Column sensitive VPD however is a new functionality available in 10g which allows you to additionally secure your data.

SEC_RELEVANT_COLS parameter

Consider the following scenario:
You only allow a user to retrieve his own salary from the EMP table but you still want this user to be able to see other columns from other records.
This scenario can be reached by adding a policy to the EMP table. The policy could use the following example:

SQL> Create or replace function my_sal(p_schema in varchar2 default null,
2  P_object in varchar2 default null) return varchar2 is
3  Begin
4  return ('ename=SYS_CONTEXT('userenv','session_user'));
5  end;
6  /

Add the policy to the table:

SQL> execute dbms_rls.add_policy(
2  object_schema => 'SCOTT',
3  object_name =>'EMP',
4  policy_name=>'onlyOwnSal',
5  function_schema => 'Sec_manager',
6  policy_function => 'my_sal',
7  statement_types =>'SELECT',
8  sec_relevant_cols =>'SAL');

Adding the parameter SEC_RELEVANT_COLS will allow you to specify the column the policy should apply on.
When the SAL column is queried the VPD policy is invoked which will lead to the fact that you will only see your own record. When you however do not query the SAL column you will see all other records.

SQL> connect MILLER/MILLER@ORCL
SQL> select name,sal from emp;
NAME       SAL
----------------
MILLER   2000
SQL> select name from emp;
NAME
-------------
MILLER
SMITH
WARD
JONES

SEC_RELEVANT_COLS_OPT parameter
Using this parameter when you add the VPD policy to a table will result in the display of the secured columns but the values are displayed as NULL values. Note that this parameter is only applied in select statements.

SQL> execute dbms_rls.add_policy(
2  object_schema => 'SCOTT',
3  object_name =>'EMP',
4  policy_name=>'onlyOwnSal',
5  function_schema => 'Sec_manager',
6  policy_function => 'my_sal',
7  statement_types =>'SELECT',
8  sec_relevant_cols =>'SAL',
9  sec_relevant_cols_opt =>dbms_rls.all_rows
10  );
SQL> select name,sal from emp;
NAME       SAL
-----------------
SMITH
MILLER   2000
WARD
JONES

Conclusion:
Column Sensitive VPD allows you to keep sensitive and no sensitive information together in one table since the VPD policy will take care of the masking of the sensitive data when it is needed. This makes it ideal for concealing data like credit card numbers, patient information etc.

A combination of column sensitive VPD together with RLS VPD is possible. This will however make it even more difficult for a developer to predict the outcome of a query when the sec_relevant_cols_opt parameter is not used.