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.