How To Set a Virtual Private Database (VPD). - Part II

This is a continuation of my previous post about VPD.
The requirement: I have a table like

test_vpd (id_col INTEGER, name_col VARCHAR2(30),contact_no NUMBER, 
email VARCHAR2(50), city VARCHAR2(50))

The user JAMES can not view any email address and also, He can not modify any rows where id_col is less than 20.

 oracle@ubuntu-desktop:~$ sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 27 16:13:34 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> /* Granting scott, the owner of the table a create contetx privilege */
SQL> GRANT CREATE ANY CONTEXT TO scott;
 

Grant succeeded.

Now, It's time to create the table and some sample data with SCOTT user. 

SQL> /* Creating a conetxt */
SQL> CREATE OR REPLACE CONTEXT saubhik_test_context USING test_vpd_pkg;

Context created.
 

SQL> CREATE TABLE test_vpd (id_col INTEGER, name_col VARCHAR2(30),contact_no NUMBER, email VARCHAR2(50), city VARCHAR2(50));
 
Table created.
SQL> ed
Wrote file afiedt.buf
 
  1  INSERT INTO test_vpd SELECT rownum,initcap(substr(object_name,1,30)),object_id,owner||'@mail.com',object_type
  2  FROM all_objects
  3* WHERE rownum<50 data-blogger-escaped-sql=""> / 
 
49 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> set line 180
SQL> SELECT * FROM test_vpd;
 
    ID_COL NAME_COL     CONTACT_NO EMAIL      CITY
---------- ------------------------------ ---------- -------------------------------------------------- --------------------------------------------------
  1 Dual      258 SYS@mail.com     TABLE
  2 Dual      259 PUBLIC@mail.com     SYNONYM
  3 System_Privilege_Map    311 SYS@mail.com     TABLE
  4 System_Privilege_Map    313 PUBLIC@mail.com     SYNONYM
  5 Table_Privilege_Map    314 SYS@mail.com     TABLE
  6 Table_Privilege_Map    316 PUBLIC@mail.com     SYNONYM
  7 Stmt_Audit_Option_Map   317 SYS@mail.com     TABLE
  8 Stmt_Audit_Option_Map   319 PUBLIC@mail.com     SYNONYM
  9 Ol$      452 OUTLN@mail.com     TABLE
 10 Ol$Hints     453 OUTLN@mail.com     TABLE
 11 Ol$Nodes     456 OUTLN@mail.com     TABLE
 
    ID_COL NAME_COL     CONTACT_NO EMAIL      CITY
---------- ------------------------------ ---------- -------------------------------------------------- --------------------------------------------------
 12 Map_Object     604 SYS@mail.com     TABLE
 13 Map_Object     605 PUBLIC@mail.com     SYNONYM
 14 Re$Nv_List     886 SYS@mail.com     TYPE
 15 Standard     887 SYS@mail.com     PACKAGE
 16 Dbms_Standard    889 SYS@mail.com     PACKAGE
 17 Dbms_Standard    890 PUBLIC@mail.com     SYNONYM
 18 V_$Map_Library    900 SYS@mail.com     VIEW
 19 V$Map_Library    901 PUBLIC@mail.com     SYNONYM
 20 V_$Map_File     902 SYS@mail.com     VIEW
 21 V$Map_File     903 PUBLIC@mail.com     SYNONYM
 22 V_$Map_File_Extent    904 SYS@mail.com     VIEW
.............................................................................................................................................................................


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

Now, It's time to write a package to setup the VPD from scott

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE
  2  PACKAGE test_vpd_pkg
  3  AS
  4    --This procedure sets the application context.
  5    --------------------------------------------
  6  PROCEDURE set_app_context;
  7    --This function builds the predicate (the where caluse).
  8    --------------------------------------------------------
  9    --This where caluse filters the rows and determines which can be accessed.
 10    -----------------------------------------------------------------------
 11    --Two parameters pi_schema and pi_name is NOT used anywhere, still those are mandetory!
 12    FUNCTION the_predicate1(
 13    pi_schema IN VARCHAR2,
 14    pi_name   IN VARCHAR2)
 15  RETURN VARCHAR2;
 16  FUNCTION the_predicate2(
 17    pi_schema IN VARCHAR2,
 18    pi_name   IN VARCHAR2)
 19  RETURN VARCHAR2;
 20* END test_vpd_pkg;
 21  /

Package created.
SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE
  2  PACKAGE BODY test_vpd_pkg
  3  AS
  4    c_context_name VARCHAR2(30):= 'saubhik_test_context';
  5    c_attribute_name VARCHAR2(30):= 'ID_COL';
  6  PROCEDURE set_app_context
  7  IS
  8    v_user_name VARCHAR2(30);
  9  BEGIN
 10    SELECT USER INTO v_user_name FROM dual;
 11    DBMS_SESSION.SET_CONTEXT (namespace=>c_context_name, attribute=>c_attribute_name,value=>v_user_name);
 12  END set_app_context;
 13  FUNCTION the_predicate1(
 14  pi_schema IN VARCHAR2,
 15  pi_name   IN VARCHAR2)
 16    RETURN VARCHAR2
 17  IS
 18    v_context_value VARCHAR2(200):=SYS_CONTEXT(c_context_name,c_attribute_name);
 19    v_restriction   VARCHAR2(2000);
 20  BEGIN
 21    IF v_context_value = 'JAMES' THEN
 22  v_restriction:= ' ID_COL > 20';
 23    ELSE
 24  v_restriction:= NULL;
 25    END IF;
 26    RETURN v_restriction;
 27  END the_predicate1;
 28  FUNCTION the_predicate2(
 29  pi_schema IN VARCHAR2,
 30  pi_name   IN VARCHAR2)
 31    RETURN VARCHAR2
 32  IS
 33    v_context_value VARCHAR2(200):=SYS_CONTEXT(c_context_name,c_attribute_name);
 34    v_restriction   VARCHAR2(2000);
 35  BEGIN
 36    IF v_context_value = 'JAMES' THEN
 37  v_restriction:= ' 1=2';
 38    ELSE
 39  v_restriction:= NULL;
 40    END IF;
 41    RETURN v_restriction;
 42  END the_predicate2;
 43* END test_vpd_pkg;
 44  /

Package body created.

SQL> 

 Now, add the policy, and a logon trigger for JAMES from sys.

SQL> BEGIN
 DBMS_RLS.ADD_POLICY ( OBJECT_SCHEMA => 'SCOTT'
                      ,OBJECT_NAME => 'TEST_VPD'
                      ,POLICY_NAME => 'TEST_VPD_POLICY1'
                      ,FUNCTION_SCHEMA => 'SCOTT'
                      ,POLICY_FUNCTION => 'TEST_VPD_PKG.THE_PREDICATE1'
                      ,STATEMENT_TYPES =>'UPDATE, DELETE' --You can add select also if required.
                      ,UPDATE_CHECK => FALSE
                      ,ENABLE => TRUE
                     );
END;   2    3    4    5    6    7    8    9   10   11  
 12  /

PL/SQL procedure successfully completed.

SQL> BEGIN
 DBMS_RLS.ADD_POLICY ( OBJECT_SCHEMA => 'SCOTT'
                      ,OBJECT_NAME => 'TEST_VPD'
                      ,POLICY_NAME => 'TEST_VPD_POLICY2'
                      ,FUNCTION_SCHEMA => 'SCOTT'
                      ,POLICY_FUNCTION => 'TEST_VPD_PKG.THE_PREDICATE2'
                      ,SEC_RELEVANT_COLS=>'EMAIL'
                      ,SEC_RELEVANT_COLS_OPT=>dbms_rls.ALL_ROWS
                      ,UPDATE_CHECK => FALSE
                      ,ENABLE => TRUE
                     );
END;   2    3    4    5    6    7    8    9   10   11   12  
 13  /

PL/SQL procedure successfully completed.

SQL> 
SQL> ed
Wrote file afiedt.buf
 
  1  CREATE OR REPLACE TRIGGER test_vpd_trig
  2   AFTER LOGON ON DATABASE
  3   BEGIN
  4    SCOTT.test_vpd_pkg.set_app_context;
  5*  END;
  6  / 
 
Trigger created.
 
SQL> CREATE USER JAMES IDENTIFIED BY james;
 
User created.
 
SQL> GRANT connect, resource to james;
 
Grant succeeded.
 
SQL> GRANT select,update,delete on scott.test_vpd to james;
 
Grant succeeded.
 
SQL>

All set!. Time to test the VPD.

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

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 27 18:41:21 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> SET line 200
SQL> SELECT * FROM scott.test_vpd;

    ID_COL NAME_COL     CONTACT_NO E CITY
---------- ------------------------------ ---------- - --------------------------------------------------
  1 Dual      258   TABLE
  2 Dual      259   SYNONYM
  3 System_Privilege_Map    311   TABLE
  4 System_Privilege_Map    313   SYNONYM
  5 Table_Privilege_Map    314   TABLE
  6 Table_Privilege_Map    316   SYNONYM
  7 Stmt_Audit_Option_Map   317   TABLE
  8 Stmt_Audit_Option_Map   319   SYNONYM
  9 Ol$      452   TABLE
 10 Ol$Hints     453   TABLE
 11 Ol$Nodes     456   TABLE

So, No email is visible!

SQL> ed
Wrote file afiedt.buf

  1  UPDATE scott.test_vpd
  2  SET name_col='Saubhik'
  3* WHERE id_col=1
SQL> /

0 rows updated.

SQL> ed
Wrote file afiedt.buf

  1  UPDATE scott.test_vpd
  2  SET name_col='Saubhik'
  3* WHERE id_col=21
SQL> /

1 row updated.

SQL> ed
Wrote file afiedt.buf

  1  UPDATE scott.test_vpd
  2  SET name_col='Saubhik'
  3* WHERE id_col=20
SQL> /

0 rows updated.

SQL> 

Anything less than 20 in id_col is can not be updated.

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>