0 
0 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.
Related posts:
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 Mr. Thomas Kyte goes Dutch – Guru4Pro
It appears that the predicate that is “added” to a query is AND (..vpd policy here…). Is there any way to use OR instead of having the predicate be AND….
Thanks
is it possible to mask column data with “########” in case of string and “NaN” in case of float/double.
Well first I would never use VPD on column level without the sec_revelant_cols_opt. It would be very hard for a developer to write SQL or PL/SQL against it. One moment you get x number of columns back the other moment x+(number)depending upon the nr of column policies.So from this perspective I agree. On the other hand perhaps you only want to display salary information of the person querying the salary table. You however do want this person to have access to other information in this table. One is able to solve this using the column policy option an othet possibility would be to create views with case or decode statememts masking the information that needs to be secured.
As for the second question: I could image that it uses a case statement. The outcome definitly looks the same compaired with the use of the sec_relevant_cols_opt option. I am however not sure.
Regards the first column sensitive VPD facility you describe (column level VPD; as separate to column masking VPD), can you think of a use of this facility? Column masking makes obvious sense; you can hide credit card values in a table for instance. But column level?… we’re not so sure.
Do you think column level was Oracle’s first attempt at column masking?
Also behind the scenes, any idea how the column sensitive VPD changes the SQL? I’m guessing column masking wraps each secure column in a case statement…. probably why the feature was implemented around the same time as the case-keyword was supported in SQL.
Cheers,
CM.