How To Set a Virtual Private Database (VPD).

This demonstration is based on Oracle 10G Release 2.

SQL> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0    Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

From documentation:
Virtual private database (VPD) enables you 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 condition (known as a 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.

Our VPD Requirement

QL> desc customers

 Name                       Null?    Type

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

 CUSTOMER_ID                   NOT NULL NUMBER(6)
 CUST_FIRST_NAME               NOT NULL VARCHAR2(20)
 CUST_LAST_NAME                NOT NULL VARCHAR2(20)
 CUST_ADDRESS                        CUST_ADDRESS_TYP
 PHONE_NUMBERS                        PHONE_LIST_TYP
 NLS_LANGUAGE                        VARCHAR2(3)
 NLS_TERRITORY                        VARCHAR2(30)
 CREDIT_LIMIT                        NUMBER(9,2)
 CUST_EMAIL                        VARCHAR2(30)
 ACCOUNT_MGR_ID                     NUMBER(6)
 CUST_GEO_LOCATION                    MDSYS.SDO_GEOMETRY
 DATE_OF_BIRTH                        DATE
 MARITAL_STATUS                     VARCHAR2(20)
 GENDER                         VARCHAR2(1)
 INCOME_LEVEL                        VARCHAR2(20)

SQL> SELECT account_mgr_id,COUNT(customer_id)
  2  FROM customers
  3  GROUP BY account_mgr_id;

ACCOUNT_MGR_ID COUNT(CUSTOMER_ID)

-------------- ------------------
       147               76
       149               74
       148               58
       145              111

So, there are four account manager under which, all the customers are assigned. Our requirement is to set a VPD, So that each account manager can
only see,modify his customers.

We assume each account manager will loginto the database using AM||account_mgr_id. That is, AM147, AM148, AM149 and AM145.


Steps for setting up a VPD.
******************************

Step 1: Set up a driving context. Connect database as OE.
--------

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE OR REPLACE CONTEXT saubhik_cust_context USING cust_vpd_pkg;

Context created.

SQL>

Step 2:  Creating the package.
-------- 

SQL> SHOW USER
USER is "OE"
SQL> CREATE OR REPLACE
PACKAGE cust_vpd_pkg
AS
  --This procedure sets the application context.
  --------------------------------------------
PROCEDURE set_app_context;
  --This function builds the predicate (the where caluse).
  --------------------------------------------------------
  --This where caluse filters the rows and determines which can be accessed.
  -----------------------------------------------------------------------
  FUNCTION the_predicate(
      pi_schema IN VARCHAR2,
      pi_name   IN VARCHAR2)
    RETURN VARCHAR2;
  END cust_vpd_pkg;  2    3    4    5    6    7    8    9   10   11   12   13   14   15 
 16  /

Package created.

SQL> CREATE OR REPLACE
PACKAGE BODY cust_vpd_pkg
AS
  c_context_name   VARCHAR2(30):= 'saubhik_cust_context';
  c_attribute_name VARCHAR2(30):= 'ACCOUNT_MGR';
PROCEDURE set_app_context
IS
  v_user_name VARCHAR2(30);
BEGIN
  SELECT USER INTO v_user_name FROM dual;
  DBMS_SESSION.SET_CONTEXT (namespace=>c_context_name, attribute=>c_attribute_name,value=>v_user_name);
END set_app_context;
FUNCTION the_predicate(
    pi_schema IN VARCHAR2,
    pi_name   IN VARCHAR2)
  RETURN VARCHAR2
IS
  v_context_value VARCHAR2(200):=SYS_CONTEXT(c_context_name,c_attribute_name);
  v_restriction   VARCHAR2(2000);
BEGIN
  IF v_context_value LIKE 'AM%' THEN
    v_restriction:= ' ACCOUNT_MGR_ID = SUBSTR('''||v_context_value||''',3)';
  ELSE
    v_restriction:= NULL;
  END IF;
  RETURN v_restriction;
END THE_PREDICATE;
END cust_vpd_pkg;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28 
 29  /

Package body created.

SQL>

Step 3: Defining the policy using DBMS_RLS.
---------

oracle@ubuntu-desktop:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 4 17:17:27 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> BEGIN
 DBMS_RLS.ADD_POLICY ( OBJECT_SCHEMA => 'OE'
                      ,OBJECT_NAME => 'CUSTOMERS'
                      ,POLICY_NAME => 'CUSTOMER_CREDIT_POLICY'
                      ,FUNCTION_SCHEMA => 'OE'
                      ,POLICY_FUNCTION => 'CUST_VPD_PKG.THE_PREDICATE'
                      ,STATEMENT_TYPES =>'SELECT, UPDATE, DELETE'
                      ,UPDATE_CHECK => FALSE
                      ,ENABLE => TRUE
                     );
END;  2    3    4    5    6    7    8    9   10   11 
 12  /

PL/SQL procedure successfully completed.

SQL>

Step 4: Setting a ON LOGON trigger (from SYSDBA) to set the context for each loged in user.

oracle@ubuntu-desktop:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 4 17:20:24 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE OR REPLACE TRIGGER cust_vpd_trig
 AFTER LOGON ON DATABASE
 BEGIN
  OE.cust_vpd_pkg.set_app_context;
 END;  2    3    4    5 
  6  /

Trigger created.

SQL>

Testing Of my VPD

oracle@ubuntu-desktop:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 4 18:20:48 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE USER am145 identified by am145;

User created.

SQL> CREATE USER am147 identified by am147;

User created.

SQL> GRANT connect,resource to am145,am147;

Grant succeeded.

SQL> GRANT SELECT ON oe.customers to am145,am147;

Grant succeeded.

SQL>

Now, connecting AS new user AM145.

oracle@ubuntu-desktop:~$ sqlplus am145/am145

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 4 18:23:22 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT customer_id,account_mgr_id FROM oe.customers;

CUSTOMER_ID ACCOUNT_MGR_ID
----------- --------------
    101           145
    102           145
    103           145
    104           145
    105           145
    106           145
    107           145
    108           145
    109           145
    110           145
    111           145
...........................................

..........................................

So, He can only view his customers only.

Now, customer_id=782 belongs to account manager 149. We will try to update it from AM145.

SQL> l
  1  UPDATE oe.CUSTOMERS SET cust_first_name='abc'
  2* WHERE customer_id=782
SQL> /

0 rows updated.

SQL>