Saturday, July 7, 2012

Row level Security using ADF and Sys_context package

When Developing a web application you must know that Database connection will be shared between different end users. Traditional Forms applications create a connection for each client connecting to the database while ADF shares the same connection pool and try to activate/passivate connection based on end user transactions.

Implementing Row level security or (Virtual Private Database) VPD can be into two ways:

- By Building a view on each table you have on your owner schema, and then adding the where condition inside view SQL script that will do the predicate based on user context variables.

- Or by using DBMS.RLS package by creating a policy procedure, and then add this policy to schema table using DBMS.RLS.Add_Policy(), the procedure returns the predicate to the table to execute the appropriate command in varchar2 format that contains your conditions.

In here I will demonstrate how to build and use context package through ADF web application using the first approach, the second one is easy to implement once you build the context package and you knew where to call it inside your ADF application.

At first you need to create CTX package at your owner schema:

CREATE OR REPLACE PACKAGE SEC_CTX_PKG IS 
   PROCEDURE SET_SESSION_ID (IN_SESSION_ID VARCHAR2); 
   PROCEDURE SET_CONTEXT (IN_NAME VARCHAR2, IN_VALUE VARCHAR2); 
 END SEC_CTX_PKG;
/

--------------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY SEC_CTX_PKG 
 IS 
   GC$SESSION_ID  VARCHAR2 (100); 
   PROCEDURE SET_SESSION_ID (IN_SESSION_ID VARCHAR2) 
   IS 
   BEGIN 
    GC$SESSION_ID := IN_SESSION_ID;
    DBMS_SESSION.SET_IDENTIFIER (IN_SESSION_ID);
  END;  

-------------------------------------------------------------------------------------------------
   PROCEDURE SET_CONTEXT (IN_NAME VARCHAR2, IN_VALUE VARCHAR2) 
   IS 
   BEGIN 
    DBMS_SESSION.SET_CONTEXT ('SEC_CTX', IN_NAME, IN_VALUE, USER, 
                 GC$SESSION_ID); 
   END; 
 END SEC_CTX_PKG;
/


Then you need to create your own context variable:
CREATE OR REPLACE CONTEXT SEC_CTX
 USING SEC_CTX_PKG
 ACCESSED GLOBALLY;

 

You need to create a view on table that you want to filter at database which have the predicate. As an example if I have a table called user_info, I will be building a view called user_info_v as following:
SELECT *
     FROM user_info
    WHERE user_id = SYS_CONTEXT ('SEC_CTX', 'user_id_p');


All Developers should use this view to retrieve, insert, update and delete data.

This is all created at your table owner schema. Now moving to the ADF application part, you need to override the prepareSession method for the application module connecting to schema. The prepareSession allow the application module to initialize user defined context variable at our case user_id_p parameter.

    protected void prepareSession(Session session) {
        super.prepareSession(session);
        setVPDcontext(session);
    }



    private void setVPDcontext(Session session) {
        SessionImpl session2 = (SessionImpl)transaction.getSession();
        String username=session2.getUserPrincipalName();

        CallableStatement stmt = null;
       
        try {          
            String sql = "BEGIN  SEC_CTX_PKG.SET_SESSION_ID ('"+username+"');                  

                        CTX_PKG.SET_CONTEXT ('user_id_p', '"+username+"'); END; ";
            stmt = this.getDBTransaction().createCallableStatement(sql, 0);
            stmt.execute();
        } catch (Exception exp) {
            exp.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception exp) {
                    exp.printStackTrace();
                }
            }
        }
    }


Of-course you can add complex conditions, but this for demo only. If you want to filter the data on table base use DBMS.RLS package rather than building view on top of the table.