Data Security and Virtual Private Database (VPD)

Virtual Private Database (VPD)

Column masking is a simple way to hide your valuable data from certain users without having to apply encrypt/decrypt techniques and increase the column width to accommodate the new string like the old times. Through some simple configuration you can create policies to show your important columns as null without rewriting a single line of code on your application side.

There are 3 steps for accomplish column masking:

1.     Function to be used by the policy (function policy) created in next step.

2.     Use dbms_rls package to create the policy.

3.     Assign “exempt access policy” to users to be excluded from the policy. 

      

These users can see all data with no masking.

Step 1: Create Function Policy

This function will be called be the policy to create the column masking. Function name can be any name you select. In my case I called vpd_function. If predicate evaluated to true, then data will to show to all users. In my case I made sure that function will always evaluate to false by looking for (rowid = 0) which will never be true. I suggest creating the function under system account.

CREATE OR REPLACE

FUNCTION vpd_function (obj_owner IN VARCHAR2, obj_name IN VARCHAR2)

RETURN VARCHAR2

AS

BEGIN

RETURN 'rowid = ''0''';

END vpd_function;

Step 2: Create Policy

BEGIN

DBMS_RLS.ADD_POLICY(object_schema=> 'SCOTT',

object_name=> 'EMP',

policy_name=> 'scott_emp_policy',

function_schema=> 'SYSTEM',

policy_function=> 'vpd_function',

sec_relevant_cols=> 'JOB',

policy_type => DBMS_RLS.SHARED_STATIC,

sec_relevant_cols_opt=> dbms_rls.ALL_ROWS);

END;

/

Step 3: Exclude Some Users from Policy

Users who need to see all the data without any masking need to be granted “exempt access policy”

Notes:

Dropping a table that has a policy will drop the policy but not the function policy.

Renaming the table will NOT drop the policy or disable it. The policy will remain active.

Export/Import will also export/import the policy along with the table, but will not export/import the function policy.

When exporting a table under a policy, make sure the user exporting the table has “exempt access policy” grant, otherwise, the column under the policy will always be null and column data will be lost.

/* grant and revoke the policy to/from individuals*/

grant EXEMPT ACCESS POLICY to username;
revoke EXEMPT ACCESS POLICY from username;

Some Important Views

dba_policies
v$vpd_policy
user_policies

Column masking is only applied to SELECT statements, but it is enforced regardless of which client is used to access the database, whether it's SQL*Plus, a .NET application, or some other tool.