Wednesday, July 11, 2012

Data Masking In Oracle/Column Masking

Or We Can Call it VPD : Virtual Private Database

What is Data Masking Mean ? 

simple way to hide you 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. A 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.
Step1 : Create Function Policy 

CREATE OR REPLACE
FUNCTION vpd_function (obj_owner IN VARCHAR2, obj_name IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN 'rowid = ''0''';
END vpd_function;
/

The Above Function is Used for Column Masking , If you set this function to True All User will be able to see the correct Data , But the above function Is to False (rowid=0).

Step2: 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;
/
exempt access policy : Use to Exclude Some Users to See All the Correct Data .

Important Views :

dba_policies
v$vpd_policy

Enjoy with Security

Osama Mustafa

1 comment:

  1. It's awesome I didn't know about all this until the moment I read this blog article of yours. And I got a question for you. Do you happen to know how to defend your personal intellectual property from being used without you knowing it?

    ReplyDelete