Oracle Virtual Private Databases (VPD)

Oracle Virtual Private Databases!
Oracle VPD is a great free feature in implementing data masking or data privacy at zero cost. Data privacy can be implemented with simple database policies. Database policies are implemented using simple Oracle build-in PL/SQL packages and are not hard to use.
Oracle VPD can provide us only one masking: NULL values. VPD does not provide us with other masking options. Masking can be implemented for either a subset of rows or certain specific columns and does not require any program change from the end user client application. VPDs work both for databases and applications in the same way. DBMS_RLS is the main package to be used for implementing the database policy!

Sample Scripts

The below sample function can be  used to identified the users WHO DO NEED visibility the standard data. Everybody else will be defaulted with NULL values.


CREATE OR REPLACE FUNCTION ggate.pf_ssn (oowner IN VARCHAR2, ojname IN VARCHAR2)
RETURN VARCHAR2 AS
d_predicate varchar2(2000);
begin
  --d_predicate := '1=2';
  IF (SYS_CONTEXT('USERENV','SESSION_USER') = upper('GGATE')) or
  (SYS_CONTEXT('USERENV','SESSION_USER') = upper('SYSTEM')) or
  (SYS_CONTEXT('USERENV','SESSION_USER') = upper('SYS'))  THEN
  d_predicate := NULL;
ELSE
  --d_predicate := 'colvpd=sys_context(''work'',''pf_ssn'')';
  d_predicate := '1=2';

END IF;
RETURN d_predicate;
end pf_ssn;
/

Enabling the policy! You are done implementing simple masking in a very simple way!


BEGIN
  DBMS_RLS.ADD_POLICY (object_schema         => 'GGATE',
                       object_name           => 'WORK',
                       policy_name           => 'sp_ssn',
                       function_schema       => 'GGATE',
                       policy_function       => 'pf_ssn',
      statement_types     => 'select',
                       sec_relevant_cols     => 'ssn',
                       sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
END;
/


If the policy needs to be dropped....

begin
dbms_rls.drop_policy(object_schema         => 'GGATE',
                       policy_name           => 'pf_ssn',
                       object_name           => 'WORK');
end;
/