-- rls_vpd.sql
-- Jalal Raissi, mailto:raissi@itucator.com
-- The following script demonstrates the use of Oracle's VPD.
-- Prepared for Atlanta Oracle Users' Conference, 2007.
-- Prelims: 
--                   1. Copy, Paste, and Save this document as rls_vpd.sql
--                   2. Login as SYSTEM/yoursystempassword.
--                   3. Start (@) rls_vpd
--
-- At each PAUSE, read the corresponding notes here.
-- That's all.

SET SERVEROUTPUT ON
SET LINESIZE 132
SET PAGESIZE 24

-- Create a user called vpd/ksu with some basic privileges. vpd/ksu needs 
-- to be able to create a table and add data, create package procedures, create 
-- a context, and access the row level security API and session packages. 

CONNECT system/vpd

DROP USER vpd CASCADE;

CREATE USER vpd IDENTIFIED BY ksu
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION TO vpd;
GRANT CREATE ANY CONTEXT TO vpd;
GRANT CREATE TABLE TO vpd;
GRANT UNLIMITED TABLESPACE TO vpd;
GRANT CREATE PROCEDURE TO vpd;

CONNECT SYS/vpd AS SYSDBA
GRANT EXECUTE ON DBMS_RLS TO vpd;
GRANT EXECUTE ON DBMS_SESSION TO vpd;

PAUSE Press Enter key to continue.

CONNECT vpd/ksu

SHOW user

-- In order to implement VPD, the following 10 steps need to be followed.

-- STEP 1: Choose the tables or views to be protected using VPD.
-- In this example I create a simple table called orders that hold
-- order attributes. Order details are, of course, stored in an
-- order details table with m-1 relationship with orders table.

CREATE TABLE orders (
     orderid VARCHAR2(20),
     orderdate DATE,
     customerid VARCHAR2(20),
     orderamount NUMBER(12,2),
     ordersource VARCHAR2(10),
     ordertype VARCHAR2(10)) 
TABLESPACE users;

-- Insert a few rows 

INSERT INTO orders (orderid,orderdate,customerid,orderamount,ordersource,ordertype)
   VALUES ('10000',TO_DATE('15-FEB-2007','DD-MON-YYYY'),'1000',100.00,'WEBACCESS','CREDIT');
INSERT INTO orders (orderid,orderdate,customerid,orderamount,ordersource,ordertype)
   VALUES ('10001',TO_DATE('16-FEB-2007','DD-MON-YYYY'),'1001',200.00,'WEBACCESS','CREDIT');
INSERT INTO orders (orderid,orderdate,customerid,orderamount,ordersource,ordertype)
   VALUES ('10002',TO_DATE('17-FEB-2007','DD-MON-YYYY'),'1002',300.00,'WALKIN','CASH');
INSERT INTO orders (orderid,orderdate,customerid,orderamount,ordersource,ordertype)
   VALUES ('10003',TO_DATE('18-FEB-2007','DD-MON-YYYY'),'1003',400.00,'WALKIN','CASH');
INSERT INTO orders (orderid,orderdate,customerid,orderamount,ordersource,ordertype)
   VALUES ('10004',TO_DATE('18-FEB-2007','DD-MON-YYYY'),'1004',500.00,'WALKIN','CREDIT');

COMMIT;

SELECT * FROM orders;

PAUSE Press Enter key to continue.

-- STEP 2: Define the business rules for data storage and retrieval

-- This step should revolve around which groups of users should be able to 
-- store and retrieve which data and how. It is possible to define differing rules 
-- on a table or view for SELECT, INSERT, UPDATE, and DELETE actions. 

-- In the case of this simple example I define the following rules:
-- 1. Any user who is employed as a CLERK can view credit orders only. 
-- 2. Any user who is employed as a CASHIER can view cash orders only. 
-- 3. ADMIN users can view all orders.
-- 4. Any user who is not  CLERK, CASHIER, and ADMIN cannot view any orders. 
-- 5. The same rules apply to INSERT, UPDATE, and DELETE.

PAUSE Press Enter key to continue.

-- STEP 3: Create a security context to manage application sessions

-- The security context is stored as an application context, a namespace with 
-- name/value pairs. The only way to set a context is through PL/SQL package 
-- that is bound to that context. 
-- Note: The package does not have to exist yet to be able to create the context.

CREATE OR REPLACE CONTEXT vpd_test USING SET_VPD_CONTEXT;

-- The following is a list of SELECT statements to get the user environment variables 
-- and their content.

-- Database name for current connected session
SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER') FROM DUAL
/

-- OS terminal name for current connected session
SELECT SYS_CONTEXT('USERENV', 'TERMINAL') FROM DUAL
/

-- Name of host machine for current connected session
SELECT SYS_CONTEXT('USERENV', 'HOST') FROM DUAL
/

-- OS user name for current connected session
SELECT SYS_CONTEXT('USERENV', 'OS_USER') FROM DUAL
/

-- Auditing ID for the connected session
SELECT SYS_CONTEXT('USERENV', 'SESSIONID') FROM DUAL
/

PAUSE Press Enter key to continue.

-- STEP 4: Create a procedure or function to manage setting of users' security contexts.

CREATE OR REPLACE PACKAGE SET_VPD_CONTEXT
IS
   PROCEDURE SET_ADMIN;
   PROCEDURE SET_CLERK;
   PROCEDURE SET_CASHIER;
END;
/

CREATE OR REPLACE PACKAGE BODY SET_VPD_CONTEXT
AS
  PROCEDURE SET_ADMIN
    IS
    BEGIN
       DBMS_SESSION.SET_CONTEXT('VPD_TEST','APP_ROLE','ADMIN');
    END;
    --
    PROCEDURE SET_CLERK
    IS
    BEGIN
       DBMS_SESSION.SET_CONTEXT('VPD_TEST','APP_ROLE','CLERK');
    END;
    --
    PROCEDURE SET_CASHIER
    IS
    BEGIN
       DBMS_SESSION.SET_CONTEXT('VPD_TEST','APP_ROLE','CASHIER');
    END;
END;
/

PAUSE Press Enter key to continue.

-- STEP 5: Write a package to generate the dynamic access predicates for each table 

-- This is the essence of Oracle VPD implementation. This function checks 
-- the context for the current user in line with the business rules defined and 
-- implemented in the security context. The function then, based on the rights 
-- of the user executing the SELECT, INSERT, UPDATE, and DELETE returns
-- a predicate. This predicate is appended at runtime to the WHERE clause of 
-- the executing SQL by Oracle optimizer engine. 

CREATE OR REPLACE PACKAGE VPD_POLICY
  AS
    FUNCTION VPD_PREDICATE(SCHEMA_NAME IN VARCHAR2, OBJECT_NAME IN VARCHAR2)
    RETURN VARCHAR2;
  END;
/

CREATE OR REPLACE PACKAGE BODY VPD_POLICY
AS
  FUNCTION VPD_PREDICATE(SCHEMA_NAME IN VARCHAR2,OBJECT_NAME IN VARCHAR2)
     RETURN VARCHAR2
     IS
        LV_PREDICATE VARCHAR2(1000):='';
     BEGIN
        IF SYS_CONTEXT('VPD_TEST','APP_ROLE') = 'ADMIN' THEN
           LV_PREDICATE:=''; -- NO PREDICATE, i.e. ALLOW ALL ACCESS
        ELSIF SYS_CONTEXT('VPD_TEST','APP_ROLE') = 'CLERK' THEN
           LV_PREDICATE:='ordertype=''CREDIT''';
        ELSIF SYS_CONTEXT('VPD_TEST','APP_ROLE') = 'CASHIER' THEN
           LV_PREDICATE:='ordertype=''CASH''';
        ELSE
           LV_PREDICATE:='1=2'; -- FORCE FALSE, i.e. BLOCK ANY ACCESS
        END IF;
        RETURN LV_PREDICATE;
     END;
   END;
/

SHOW ERRORS PACKAGE BODY VPD_POLICY

PAUSE Press Enter key to continue.

-- STEP 6: Register the policy policy with Oracle using DBMS_RLS package.

-- The policy function needs to be registered with the database and specifically 
-- with the target table or view. In a real application this step involves one call to 
-- a SYS owned package called DBMS_RLS which is the API interface to the 
-- Oracle Kernel Row Level Security at $ORACLE_HOME/rdbms/admin/dbmsrlsa.sql. 

-- UPDATE_CHECK:
-- Optional argument for INSERT or UPDATE statement types. The default is 
-- FALSE. Setting update_check to TRUE causes the server to check the policy 
-- against the value after the insert or update has been performed. 

-- STATIC_POLICY:
-- The default is FALSE. If it is set to TRUE, the server assumes that the policy 
-- function for the static policy produces the same predicate string for anyone 
-- accessing the object, except for SYS or the privilege user who has the 

-- EXEMPT ACCESS POLICY privilege. Check parameters at:
-- http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_rls.htm

BEGIN
  DBMS_RLS.ADD_POLICY(
     OBJECT_SCHEMA => 'VPD',
     OBJECT_NAME => 'orders',
     POLICY_NAME => 'VPD_TEST_POLICY',
     FUNCTION_SCHEMA => 'VPD',
     POLICY_FUNCTION => 'VPD_POLICY.VPD_PREDICATE',
     STATEMENT_TYPES => 'SELECT, INSERT, UPDATE, DELETE',
     UPDATE_CHECK => TRUE,
     ENABLE => TRUE,
     STATIC_POLICY => FALSE);
END;
/

-- STEP 7: Automate the setting of the security context when user logs on.

-- The security context can be set automatically via a database logon trigger. 

--CONNECT sys/vpd
--CREATE OR REPLACE TRIGGER VPD_LOGON_TRIGGER
--AFTER LOGON ON DATABASE
--BEGIN
--   SET_VPD_CONTEXT.SET_CLERK;
--END;
-- /

PAUSE Press Enter key to continue.

-- STEP 8: Take a water break.

-- STEP 9: Begin test

CONNECT vpd/ksu

SELECT * FROM orders;

-- Since an application role (security context) does not exist, the business security 
-- rules dictated that no data should be accessed.

PAUSE Press Enter key to continue.

-- Set VPD Context to CASHIER

EXEC SET_VPD_CONTEXT.SET_CASHIER;

COL NAMESPACE FOR A15
COL ATTRIBUTE FOR A15
COL VALUE FOR A15

SELECT * FROM SESSION_CONTEXT;

SELECT * FROM orders;

-- Only CASH orders are seen by a CASHIER. This is correct. 

PAUSE Press Enter key to continue.

-- Can the context be set directly by using dbms_session and not
-- the associated package?

EXEC DBMS_SESSION.SET_CONTEXT('VPD_TEST','APP_ROLE','ADMIN');

-- This error indicates that Oracle will only allow the context to be set using the correctly 
-- associated function, i.e. SET_VPD_CONTEXT.

PAUSE Press Enter key to continue.

-- Set VPD Context (application role) to CLERK.

EXEC SET_VPD_CONTEXT.SET_CLERK;

SELECT * FROM SESSION_CONTEXT;

SELECT * FROM orders;

-- Only CREDIT orders are displayed. This is correct behaviour.

PAUSE Press Enter key to continue.

-- Now, let's set the application role to ADMIN and verify that all of the orders 
-- are displayed.

EXEC SET_VPD_CONTEXT.SET_ADMIN;

SELECT * FROM SESSION_CONTEXT;

SELECT * FROM orders;

-- Succes! It worked.

PAUSE Press Enter key to continue.

-- So far, we tested SELECT against the security policy. Now, let's INSERT a row and 
-- watch the behaviour of one of the DBMS_RLS.ADD_POLICY parameters. Because 
-- we set the same policy function for all access methods the same visibility rules apply. 
-- If we set the application context to CLERK and attempt to INSERT a CASH order,
-- it should return an EXCEPTION.

EXEC SET_VPD_CONTEXT.SET_CLERK;

SELECT *  FROM SESSION_CONTEXT;

INSERT INTO orders(orderid,orderdate,customerid,orderamount,ordersource,ordertype)
   VALUES ('10005',TO_DATE('19-FEB-2007','DD-MON-YYYY'),'1000',600.00,'WALKIN','CASH');

-- Again, this is correct. A CLERK should be able to INSERT CREDIT orders only. 

PAUSE Press Enter key to continue.

-- Now, let's attempt to INSERT a CREDIT order.

INSERT INTO orders(orderid,orderdate,customerid,orderamount,ordersource,ordertype)
   VALUES ('10006',TO_DATE('20-FEB-2007','DD-MON-YYYY'),'1000',700.00,'WALKIN','CREDIT');

-- Success again! The behaviour for INSERT, UPDATE, and DELETE can be modified when 
-- specifying the policy functions to the API. The parameter UPDATE_CHECK is, by default,
-- set to FALSE. Since it was set to TRUE when we added the policy function, no data 
-- could be INSERTed, UPDATEd, and DELETEd that could not be SELECTed. If we change 
-- UPDATE_CHECK parameter back to FALSE and try INSERTing the order above 
-- again it should not fail!

PAUSE Press Enter key to continue.

BEGIN
   DBMS_RLS.DROP_POLICY(
       OBJECT_SCHEMA => 'VPD',
       OBJECT_NAME => 'orders',
       POLICY_NAME => 'VPD_TEST_POLICY');
END;
/

BEGIN
   DBMS_RLS.ADD_POLICY(
       OBJECT_SCHEMA => 'VPD',
       OBJECT_NAME => 'orders',
       POLICY_NAME => 'VPD_TEST_POLICY',
       FUNCTION_SCHEMA => 'VPD',
       POLICY_FUNCTION => 'VPD_POLICY.VPD_PREDICATE',
       STATEMENT_TYPES => 'SELECT, INSERT, UPDATE, DELETE',
       UPDATE_CHECK => FALSE, -- SET BACK TO FALSE
       ENABLE => TRUE,
       STATIC_POLICY => FALSE);
 END;
 /

PAUSE Press Enter key to continue.

INSERT INTO orders (orderid,orderdate,customerid,orderamount,ordersource,ordertype)
   VALUES ('10007',TO_DATE('21-FEB-2007','DD-MON-YYYY'),'1001',800.00,'WEBACCESS','CASH');

-- The UPDATE_CHECK parameter is one of the backdoors which effectively circumvent 
-- the policy rules defined. 

CONNECT SYSTEM/vpd AS sysdba

SELECT * FROM vpd.orders;

-- The SYS user or any user such as SYSTEM connected as SYSDBA also bypasses all 
-- row level security policies. Audit activities by SYS or any user with SYSDBA privileges.

PAUSE Press Enter key to continue.

CONNECT vpd/ksu

SELECT * FROM orders;

PAUSE Press Enter key to continue.

-- You can EXEMPT certain users from VPD. This action needs to be audited.

CONNECT SYSTEM/vpd AS sysdba

GRANT EXEMPT ACCESS POLICY TO vpd;

CONNECT vpd/ksu

SELECT * FROM orders;

-- Hmmm. Backdoors exist. Hence, security is not absolute even with VPD. 

PAUSE Press Enter key to continue.

CONNECT SYSTEM/vpd AS sysdba

REVOKE EXEMPT ACCESS POLICY FROM vpd;

CONNECT vpd/ksu

SELECT * FROM orders;

PAUSE Press Enter key to continue.

-- STEP 10: Use the following SELECTs to display VPD policies via data dictionaries.

CONNECT SYSTEM/vpd

-- VPD_POLICY
COL SQL_TEXT FOR A25
COL PREDICATE FOR A20
COL POLICY FOR A15
COL OBJECT_NAME FOR A15

SELECT SUBSTR(SQL_TEXT,1,25) SQL_TEXT,
    PREDICATE,
    POLICY,
    OBJECT_NAME
FROM V$SQLAREA ,V$VPD_POLICY
WHERE HASH_VALUE = SQL_HASH;

PAUSE Press Enter key to continue.

CONNECT vpd/ksu

-- USER_POLICIES
COL OBJECT_NAME FOR A15
COL POLICY_NAME FOR A15
COL FUNCTION FOR A15
COL SEL FOR A3
COL INS FOR A3
COL UPD FOR A3
COL DEL FOR A3
COL CHK FOR A3
COL CHK FOR A3
COL ENB FOR A3

SELECT OBJECT_NAME OBJECT_NAME,
    POLICY_NAME POLICY_NAME,
    FUNCTION FUNCTION,
    SEL SEL,
    INS INS,
    UPD UPD,
    DEL DEL,
    CHK_OPTION CHK,
    ENABLE ENB
FROM USER_POLICIES;

-- What next?   Label and column level security in 2008.

-- References:
-- Oracle Database Security Guide, http://download-east.oracle.com/docs/cd/B19306_01/network.102/b14266/toc.htm
-- Using VPD to Implement Application Security Policy, http://download-west.oracle.com/docs/cd/B19306_01/network.102/b14266/apdvpoli.htm
-- Introduction to Oracle Label Security, http://download-east.oracle.com/docs/cd/B10501_01/network.920/a96578/intro.htm
-- Oracle 10g Security, http://www.databasejournal.com/ features/oracle/article.php/3644956
-- Oracle Row Level Security, http://www.securityfocus.com/infocus/1743