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
