Oracle Virtual Private Database (VPD) - 1

The Virtual Private Database (VPD) is a concept from Fine Grain Auditing feature of Oracle. Implementing this feature enables security policies to control database access at the row or column or combination of both levels. Since this works inside the database there is no way to bypass the security irrespective of front end application/utility.

This article is a brief introduction and explains the fundamental VPD mechanism. The upcoming continuation of this series will explore more details about VPD feature.

How it works: 

When a user accesses a table, view, or synonym that is being protected by VPD policy, Oracle Database dynamically modifies the statement intruding from that users session. If the user is eligible to view/modify the data then he will be allowed to do so, if not then the data will be hidden.

We can implement VPD Policies to any of SELECT, INSERT, UPDATE, INDEX, and DELETE as per security compliances. However, In this brief article about VPD, I am explaining how to implement VPD against SELECT statements on a table called FIN.

Notes:

If you are planned for the actual implementation of this feature, you should be having finalized set of Objects eligible as per application owners.  Also, make sure no direct implementation on Production without proper UAT validation. Misconfiguration of this feature may impact business continuity. 

VPD Implementation.

Two steps involved in implementing this feature, first a suitable VPD function should be created where you will be mentioning which database user should be allowed to see the sensitive data. Second, a VPD Policy on the eligible table.

Create a VPD Function

Create the below function as sysdba (which is having the exempt policy by default), this function will validate the user, if the user is FIN then it will except the SELECT privilege on the sensitive columns mentioned the upcoming VPD policies, if any user other than these can’t see the sensitive data mentioned in VPD policies.

create or replace FUNCTION  "FIN_FUN_VPD" (p_schema in varchar2,p_object_name in varchar2)

  return varchar2

  as

  v_ouser  VARCHAR2(30);

  begin

  v_ouser := SYS_CONTEXT('USERENV','SESSION_USER');

  -- dbms_output.put_line('p_schema = ' || p_schema );

     if ( v_ouser = 'FIN' ) then

             return '1=1';

     else

             return '1=0';

     end if;

  end;

Create a VPD Policy:

begin

dbms_rls.add_policy(

object_schema =>'FIN',

object_name =>'FIN_BNK_ACCNTS',

policy_name => 'VPD_FIN_BNK_ACCNTS',

policy_function =>'FIN_FUN_VPD',

statement_types =>'SELECT',

sec_relevant_cols => 'BANK_ACCOUNT_NUM',

sec_relevant_cols_opt => dbms_rls.all_rows

);

end;

/

Once the above FUNCTION & POLICY is enabled then make sure the output of your statements is as expected. 

From the above example, the users other than FIN should not see the column called BANK_ACCOUNT_NUM in FIN_BNK_ACCNTS table.

View the list of VPD Policies and Function against proposed tables:

COL OBJECT_OWNER FORMAT A10

COL OBJECT_NAME FORMAT A20

COL POLICY_NAME FORMAT A25

COL FUNCTION FORMAT A25

SET LINESIZE 250

SELECT OBJECT_OWNER,OBJECT_NAME,POLICY_NAME,FUNCTION,SEL,INS,UPD,DEL,IDX,ENABLE FROM DBA_POLICIES WHERE POLICY_NAME LIKE '%VPD%';

OBJECT_OWN OBJECT_NAME          POLICY_NAME               FUNCTION                  SEL INS UPD DEL IDX ENA                                                                                                                                               

---------- -------------------- ------------------------- ------------------------- --- --- --- --- --- ---                                                                                                                                               

FIN         FIN_BNK_ACCNTS         VPD_FIN_BNK_ACCNTS_1        FIN_FUN_VPD             YES NO  NO  NO  NO  YES                                                                                                                                               

View the list of VPD enabled columns:

COL OBJECT_OWNER FORMAT A10

COL OBJECT_NAME FORMAT A30

COL POLICY_GROUP FORMAT A30

COL POLICY_NAME FORMAT A30

COL SEC_REL_COLUMN FORMAT A30

SELECT * FROM DBA_SEC_RELEVANT_COLS;

OBJECT_OWN OBJECT_NAME                    POLICY_GROUP                   POLICY_NAME                    SEC_REL_COLUMN                 COLUMN_O

---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ --------

FIN         FIN_BNK_ACCNTS          SYS_DEFAULT                    FIN_FUN_VPD    BANK_ACCOUNT_NUM                   ALL_ROWS

Roll back Plan:

In case if there is a mandate to roll back the VPD functionality that got implemented same as above then the below policies can be dropped or If we need to just disable the VPD functionality on a particular table on a temporary basis then it is enough to ‘disable’ the related policy instead of dropping it.

--Drop VPD POLICIES:-

begin

dbms_rls.drop_policy(

'FIN','FIN_BNK_ACCNTS','VPD_FIN_BNK_ACCNTS');

end;

/

--Drop the VPD functions.

Drop function  FIN_FUN_VPD;

--Make sure the VPD got decommissioned completely.

COL OBJECT_OWNER FORMAT A10

COL OBJECT_NAME FORMAT A20

COL POLICY_NAME FORMAT A25

COL FUNCTION FORMAT A25

SET LINESIZE 250

SELECT OBJECT_OWNER,OBJECT_NAME,POLICY_NAME,FUNCTION,SEL,INS,UPD,DEL,IDX,ENABLE FROM DBA_POLICIES WHERE POLICY_NAME LIKE '%VPD%';