Oracle VPD - Virtual Personal Database by Example

==============================
General
==============================
This is a short example of implementation of Oracle VPD - Virtual Personal Database.

==============================
VPD Overview
==============================
From Oracle docs:
Virtual private database (VPD) enables to enforce security, to a fine level of granularity, directly on tables, views, or synonyms. 
Because security policies are attached directly to tables, views, or synonyms and automatically applied whenever a user accesses data, there is no way to bypass security.
When a user directly or indirectly accesses a table, view, or synonym protected with a VPD policy, the server dynamically modifies the SQL statement of the user. 
The modification creates a WHERE predicate returned by a function implementing the security policy. 
The statement is modified dynamically, transparently to the user, using any condition that can be expressed in or returned by a function. 
VPD policies can be applied to SELECT, INSERT, UPDATE, INDEX, and DELETE statements.

The VPD is implemented via DBMS_RLS package.

==============================
Implementation Example
==============================
Per each table, there is a dedicated function, which returns a predicate.

The implementation of the function is below.

In this function, there is a reference to privileged users, that are listed inside an applicative table DB_PRIV_USERS.
For these privileged users the returned predicate is NULL.

For other users, the predicate is being built from another applicative table, DB_PRODUCT_USERS.

DBA_POLICIES
SELECT object_name, 
       policy_group, 
       policy_name, 
       pf_owner, package, 
       function, 
       sel, ins, upd, enable
FROM DBA_POLICIES
WHERE rownum < 11;

OBJECT_NAME         POLICY_GROUP POLICY_NAME PF_OWNER PACKAGE FUNCTION             SEL INS UPD ENABLE 
------------------- ------------ ----------- -------- ------- -------------------- --- --- --- ------ 
DAILY_PROD_AGG       SYS_DEFAULT  PROD_POL01  DWNG             BUILD_PRD1_PREDICATE YES YES YES YES   
REP_DAILY_VISITORS   SYS_DEFAULT  PROD_POL01  DWNG             BUILD_PRD1_PREDICATE YES YES YES YES   
DAILY_PROD_TRAFFIC   SYS_DEFAULT  PROD_POL02  DWNG             BUILD_PRD2_PREDICATE YES YES YES YES   
REP_DAILY_PROD       SYS_DEFAULT  PROD_POL02  DWNG             BUILD_PRD2_PREDICATE YES YES YES YES   
DAILY_PROD_ABANDONED SYS_DEFAULT  PROD_POL03  DWNG             BUILD_PRD3_PREDICATE YES YES YES YES   
DAILY_USER_AGG       SYS_DEFAULT  PROD_POL03  DWNG             BUILD_PRD3_PREDICATE YES YES YES YES   
DAILY_CONCAT_PROD    SYS_DEFAULT  PROD_POL03  DWNG             BUILD_PRD3_PREDICATE YES YES YES YES   
DWH_PROD             SYS_DEFAULT  PROD_POL04  DWNG             BUILD_PRD4_PREDICATE YES YES YES YES   
DAILY_PROD_ORIG      SYS_DEFAULT  PROD_POL04  DWNG             BUILD_PRD4_PREDICATE YES YES YES YES   
PROD_COUNTER_DATA    SYS_DEFAULT  PROD_POL04  DWNG             BUILD_PRD4_PREDICATE YES YES YES YES   

FUNCTION BUILD_PRD1_PREDICATE
CREATE OR REPLACE FUNCTION BUILD_PRD1_PREDICATE (obj_schema VARCHAR2, 
                                                 obj_name   VARCHAR2)
RETURN VARCHAR2
IS
   v_predicate   VARCHAR2(2000);
   v_userName    VARCHAR2(30);
   v_cnt         PLS_INTEGER;
   v_productList VARCHAR2(200);

   CURSOR prd_cur IS
          SELECT PRODUCT_ID 
    FROM DB_PRODUCT_USERS
   WHERE USERNAME = SYS_CONTEXT('USERENV', 'SESSION_USER');
BEGIN

  SELECT COUNT(*)
    INTO v_cnt
    FROM DB_PRIV_USERS
   WHERE USERNAME = SYS_CONTEXT('USERENV', 'SESSION_USER');

  IF (v_cnt > 0) THEN
    v_predicate := NULL;
  ELSE
    v_cnt := 0;
    FOR i IN prd_cur LOOP
      v_cnt := v_cnt + 1;
      IF (v_cnt > 1) THEN
v_productList := v_productList ||',';
      END IF;
      v_productList := v_productList || ''''||i.product_id||'''';
    END LOOP;

    IF (v_cnt = 0) THEN
      v_predicate := '1=2';

    ELSIF (v_cnt = 1) THEN
      v_predicate := 'PRODUCT_ID = ' || v_productList;

    ELSE
      v_predicate := 'PRODUCT_ID IN (' || v_productList || ')';

    END IF;

  END IF;
  
  RETURN v_predicate;

END BUILD_PRD1_PREDICATE ;


DB_PRODUCT_USERS 
SELECT * FROM DB_PRODUCT_USERS WHERE ROWNUM < 11;

USERNAME          PRODUCT_ID
----------------- ------------
USER_A            008
USER_B            057
USER_C            030
USER_D            001
USER_E            557
USER_F            053
USER_G            062
USER_H            562
USER_K            011
USER_L            064

DB_PRIV_USERS
SELECT * FROM DB_PRIV_USERS;

USERNAME          DESCRIPTION
----------------- -------------------------
SYSTEM            DBA User
MANAGER           DBA User
ADMIN_PORTAL      User for internal portal