--
-- Sample showing set up of trivial (artificial) Virtual Private Database
-- policy for Instant JChem. It restricts access to `pubchem_demo' table.
-- `chemaxon' user can access all rows of the table whereas other users can
-- access only rows with CD_ID < 5.
--
-- Based on "Tutorial: Implementing a Policy with a Database Session-Based
-- Application Context"
--  -- http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm#DBSEG98265
--

set serveroutput on

-- creates the `ijc_ctx' application context
CREATE OR REPLACE CONTEXT ijc_ctx USING ijc_ctx_pkg;
/

-- Creates PL/SQL package.
CREATE OR REPLACE PACKAGE ijc_ctx_pkg IS
  PROCEDURE set_user(ijc_username VARCHAR2);
  PROCEDURE clear_user;
END;
/

CREATE OR REPLACE PACKAGE BODY ijc_ctx_pkg IS

  -- Will be called by Instant JChem, passing logged user as `ijc_username'
  -- parameter. The user name is then stored in database session-based
  -- application context and might be later picked up by Oracle Virtual Private
  -- Database policy.
  --
  -- See `get_pubchem_mols' below.
  PROCEDURE set_user(ijc_username VARCHAR2) IS
    user_missing EXCEPTION;
  BEGIN
    IF ijc_username IS NULL THEN
      RAISE user_missing;
    ELSE
      DBMS_SESSION.SET_CONTEXT('ijc_ctx', 'ijc_username', ijc_username);
    END IF;
  END set_user;

  -- Will be called by Instant JChem before connection is returned to IJC pool.
  -- Clears the context set by `set_user' procedure.
  PROCEDURE clear_user IS
  BEGIN
    DBMS_SESSION.CLEAR_CONTEXT('ijc_ctx', 'ijc_username');
  END clear_user;

END;
/

-- Trivial policy implementation which creates and returns a WHERE statement
-- for `pubchem_demo' table based on `ijc_username' set in the session context.
--
-- `chemaxon' user has no restrictions. All other users are restricted with
-- 'CD_ID < 5'.
CREATE OR REPLACE FUNCTION get_pubchem_mols(
    schema_p IN VARCHAR2,
    table_p  IN VARCHAR2)
  RETURN VARCHAR2
  AS
    pubchem_pred VARCHAR2 (400);
  BEGIN
    IF LOWER(SYS_CONTEXT('ijc_ctx','ijc_username')) = 'chemaxon' THEN
      pubchem_pred := NULL;
    ELSE
      pubchem_pred := 'CD_ID < 5';
    END IF;
  RETURN pubchem_pred;
END;
/

-- Ensure no policy is present before proceeding to next statement.
BEGIN
  DBMS_RLS.DROP_POLICY('MK', 'pubchem_demo', 'pubchem_policy');
END;
/

-- Set up new security policy called `pubchem_policy' for `pubchem_demo' table.
-- Restriction applies to SELECT statement only.
BEGIN
 DBMS_RLS.ADD_POLICY (
  object_schema    => 'MK',
  object_name      => 'pubchem_demo',
  policy_name      => 'pubchem_policy',
  function_schema  => 'MK',
  policy_function  => 'get_pubchem_mols',
  statement_types  => 'select');
END;
/

-- For debugging purposes.
CREATE OR REPLACE PROCEDURE dump_current_ijc_username
AS
BEGIN
  dbms_output.put_line(SYS_CONTEXT('ijc_ctx','ijc_username'));
END;
/

-- vim:ft=plsql

