Using virtual private databases with application servers

The main usage of this tip is to help clarify by example the use of Oracle's virtual private database by an application server account. Most examples and documentation by Oracle show how VPD can restrict access to data based on your user account when you login to the Oracle database. The problem with app servers is that you login to the app server with your login but the app server accesses the database as the app server account so all users would appear to login to the database as the app server account and the VPD cannot differentiate individual app users. The tip shows how to pass a session ID from the App to the database allowing the use of VPD.

The example uses a modified version of Oracle's worked example for fine grained access. Jeff Tarnok is a coauthor of this tip.

Oracle provides fine-grained access control and row-level security via their Virtual Private Database (VPD) technology. VPD uses application contexts to provide row-level security and fine-grained access control based on policies. Application contexts are basically name value pairs created in a defined namespace. You can think of them as variables stored in a namespace. Users are assigned specific contexts that provide appropriate data access based on a policy. The policy in our example calls a procedure that restricts access the EMP table. For example, you may want to restrict data access for web-users to view only their customer records but a manager may need to access transaction records from many different customers. VPD can reduce application coding by standardizing application modules and making them inherently reusable.

There are many suitable examples of implementing VPD using direct database connections but few, if any, utilizing application server implementations. Applications servers connect to the database for all application data requests as one single database account which Oracle "OneBigApplicationUser". The conundrum with this implementation is that it is difficult to differentiate users because all database connections share the same database account. Login triggers are commonly used to assign application contexts but this is not appropriate with "OneBigApplicationUser" implementations.

The solution is to capture the user login id used to access the application and pass the id as a client_identifier with all data access requests. The client_identifier session primitive is provided in Oracle 9i by the built-in application context namespace USERENV. Session characteristics can be controlled using session primitives by passing them to stored procedures from JDBC programs.

Our proof-of-concept demo simulates an application server by passing the login id to a JDBC program, uses the JDBC program to assign an application context via a stored procedure call, and then executes a query for the user using a policy. The demo uses an Oracle demonstration of row level security and Contexts from the "Fine Grained Access Control - Worked-Example" document to facilitate creating the application contexts, packages, stored procedures, and policy. We wrote the JDBC program to accept a login id parameter to better simulate the call from an application server. The program also contains a usage statement to assist with command line usage. We utilized Oracle 9.2.0.1 for our implementation but any 9i version should work.

The JDBC source code and Oracle demo source is included below. After compiling the JDBC program and running the demo statements you should be able to test the "OneBig pplicationUser" implementation by running the vpd.class from the Unix or NT command line: java vpd TESTY Testy is an employee name in the employee table. Employee Testy will only see information pertaining to himself and his subordinates. Our implementation automatically adds a where predicate base on the context and policy.

Executing java vpd TESTY outputs:

ENAME  EMPNO MGR SAL
MILLER 7934 7782 1300
TESTY 7782 7839 5000
 
CLIENT_IDENTIFIER
Testy

Example setup:

Create the Context namespace EMPCTX.

The setting of a user defined Context namespace appears to be arbitrary and can hold any name/value pair. In this example we will be setting the value of role to CEO,MGR or EMP in the EMPCTX context.

1. Login to SQL*plus and run commands below

   -- Ensure that scott can create any context 
   -- connect sys/change_on_install 
   grant create any context to scott 
   / 
   grant create any trigger to scott 
   / 
   grant administer database trigger to scott 
   / 
    
   -- 
   connect scott/tiger 
   -- 
   -- 
   -- Register The Namespace 
   -- 
   create or replace context empctx using scott.empsec 
   / 
   

1. Create the package DBMS_RLS running the scripts dbmsrlsa.sql and prvtrlsa.plb as SYS user.
2. Grant the EXECUTE privilege on DBMS_RLS to SCOTT

       grant execute on dbms_rls to scott;

connect scott/tiger 
set serveroutput on 
set echo on 
-- 
-- Application Context/Fine Grained access demo 
-- PREQUISITES 
-- =========== 
-- 1. SCOTT/TIGER 
-- 2. SCOTT.EMP 
-- 3. SCOTT.VALIDATE_EMP ( CTAS of EMP ) 
-- 
-- This is the basic demonstration of row level security and Contexts. 
-- It is based on a Presentation by Mark Vandenbroek Of Oracle Belgium. 
-- 
-- A context is a general method for storing session level information,
similar 
-- to being able to store things then access them through userenv. 
-- 
-- In this example there are three contexts 
-- Employee - only allowed to see their own data 
-- Manager  - only allowed to their and their immediate 
--            Subbordinates data 
-- CEO      - Full access 
-- 
 
create or replace package empsec as 
    procedure setempctx (CID in varchar2); 
end empsec; 
/ 
 
create or replace package body empsec as 
procedure setempctx (CID in varchar2) AS 
   the_role varchar2(10); 
   me       varchar2(10); 
   myename  varchar2(30); 
   myempno  number; 
   myboss   number; 
   subs     number; 
begin 
--Set USERENV context for client_identifier to passed in value
     DBMS_SESSION.SET_IDENTIFIER(CID);

     -- By default I am an EMP
     dbms_session.set_context( 'empctx', 'role', 'EMP' ); 

     -- get user info 
     me := sys_context( 'userenv', 'client_identifier' );
     -- 
     -- get my information from emp 
     -- 
     select empno, mgr, ename into myempno, myboss, myename 
     from validate_emp 
     where upper(ename)=upper(me); 
     -- 
     -- If I have any subordinates I am an MGR 
     -- 
     select count(*) into subs 
     from validate_emp 
     where mgr=myempno; 
     -- 
     -- @ do i really still need this ? 
     -- 
     if nvl(subs,0) > 0 then 
         dbms_session.set_context( 'empctx', 'role', 'MGR' ); 
     end if; 
     -- 
     -- If I do NOT have a manager then I am CEO 
     -- 
     if myboss is null then 
          dbms_session.set_context('empctx','role','CEO'); 
     end if; 
     exception 
     -- Necessary because if there are users on your database 
     -- Who aren't in emp you will get ORA-4088 
     when others then null; 
end setempctx; 
end empsec; 
/

The policy here calls the procedure hr_access.secure_access which sets the security by setting the where predicate based on the users context set at login. If the user is an employee they will only see their own employee record. If they are a manger , they will see their own record and all records where they are listed as the employees manager. CEO's will see all records.

REM ********************************************************* 
REM We will now create the package that is used to 
REM control the access. This is executed by the POLICY. 
REM The signature is mandatory 
REM ********************************************************* 
 

connect scott/tiger 
-- 
create or replace package hr_access as 
     function secure_access( obj_schema varchar2, obj_name varchar2 ) 
     return varchar2; 
end hr_access; 
/ 

create or replace package body hr_access is 
     function secure_access( obj_schema varchar2, obj_name varchar2 ) 
     return varchar2 is 
     l_predicate varchar2(2000); 
     myename     varchar2(30); 
     myempno     number; 
 
     cursor c1 is 
     select empno 
     from validate_emp 
     where upper(ename) = upper(myename); 
     -- 
begin 
     -- 
     -- get the ename from the context 
     -- 
     myename := sys_context( 'userenv', 'client_identifier' ); 
     dbms_output.put_line( myename ); 
     -- 
     open c1; 
     fetch c1 into myempno; 
     close c1; 
     -- 
     if sys_context('empctx','role') = 'CEO' then 
         return ''; 
         -- no extra qualification to the query 
     elsif sys_context('empctx','role') = 'MGR' then 
         l_predicate := 'empno = ' || myempno || ' or empno in (select
empno from validate_emp where mgr= ' || myempno || ')'; 
            -- Make this a tree walk later 
     elsif sys_context('empctx','role') = 'EMP' then 
         -- I can only see my own data 
         l_predicate := 'empno = '||myempno;
     elsif sys_context('empctx','role') is null then
         l_predicate := 'empno = 0000';
     end if; 
     return l_predicate; 
end secure_access; 
end hr_access; 
/ 

connect scott/tiger 
set serveroutput on;
-- 
exec dbms_rls.drop_policy( 'SCOTT', 'EMP', 'EMP_POLICY' );  
    
exec dbms_rls.add_policy( 'SCOTT', 'EMP', 'EMP_POLICY',
'SCOTT','HR_ACCESS.SECURE_ACCESS', 'SELECT', true, true ); 

select * from user_policies;    -- to see new policy
 

The VALIDATE_EMP table is only used to set the initial context for EMPCTX and contains the essentially the same data as EMP. EMP cannot be used to set the initial EMPCTX context since it has the policy on it that requires the use of the EMPCTX context.

Insert into scott.emp (ENAME, EMPNO, MGR, SAL) values ('MILLER', 7934, 7782,
1300);
Insert into scott.emp (ENAME, EMPNO, MGR, SAL) values ('SMITH', 7369, 7902,
800);
Insert into scott.emp (ENAME, EMPNO, MGR, SAL) values ('JONES', 7698, 7839,
1400);
Insert into scott.emp (ENAME, EMPNO, MGR, SAL) values ('TESTY', 7782, 7566,
5000);
Insert into scott.emp (ENAME, EMPNO, MGR, SAL) values ('SCOTT', 7788, 7566,
500);

JDBC Source:

/*
 * This sample shows how to retrieve and list all the names
 * (FIRST_NAME, LAST_NAME) from the EMPLOYEES table
 *
 * note: jdk1.2 is recommended. jdk1.1 will also work
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;
import oracle.jdbc.driver.*;
import java.io.*;

class vpd
{
  public static void main (String args [])
       throws SQLException
  {
if ( args.length < 1 )
       {
         System.out.println("Usage: vpd Testy ");
         System.exit(1);
       }
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    String url = "jdbc:oracle:thin:@d1de0001:1521:prcd01";
    String TEST = args[0];
    try {
      String url1 = System.getProperty("JDBC_URL");
      if (url1 != null)
        url = url1;
    } catch (Exception e) {
      // If there is any security exception, ignore it
      // and use the default
    }

    // Connect to the database
    Connection conn =
      DriverManager.getConnection (url, "scott", "tiger");

    // Create a Statement
    CallableStatement cs1 = conn.prepareCall
                      ( "{call scott.empsec.setempctx(?)}" );
//    cs1.registerOutParameter(1, Types.VARCHAR);
    cs1.setString (1, TEST);
    cs1.execute();
    Statement stmt = conn.createStatement ();
    Statement stmt1 = conn.createStatement ();

    // Select EMP Records

    ResultSet rset = stmt.executeQuery ("select ENAME, empno, mgr, sal "
                                        + "from EMP");
// Iterate through the result and print the employee names
    System.out.println ("ENAME  " + "EMPNO " + "MGR " + "SAL");
    while (rset.next ())
    System.out.println (rset.getString (1) + " " + rset.getString (2) + " "
+ rs
et.getString (3) + " " + rset.getString(4));

    // Close the RseultSet
    rset.close();

    ResultSet rset1 = stmt1.executeQuery ("select sys_context('USERENV',
'CLIENT
_IDENTIFIER') from dual");

    // Iterate through the result and print the employee names
    System.out.println (" ");
    System.out.println ("CLIENT_IDENTIFIER");
    while (rset1.next ())
      System.out.println (rset1.getString (1));

    // Close the RseultSet
    rset.close();
    rset1.close();

    // Close the Statement
    stmt.close();
    stmt1.close();
    // Close the connection
    conn.close();
  }
}

1. Edit the vpd.java and change the connect info

       String url = "jdbc:oracle:thin:@system:1521:SID";
    

   and user info if needed

       DriverManager.getConnection (url, "scott", "tiger");
       ...
       ...           ( "{call scott.empsec.setempctx(?)}" );
    

2. Compile the java class ( Unix ksh assumed below )

    export CLASSPATH=$CLASSPATH:$ORACLE_HOME/lib/classes12.zip:.
    javac vpd.class
   

3. Query the database

   java vpd TESY
   ENAME  EMPNO MGR SAL
   MILLER 7934 7782 1300
   TESTY 7782 7839 5000
    
   CLIENT_IDENTIFIER
   testy
   
   java vpd MILLER
   ENAME  EMPNO MGR SAL
   MILLER 7934 7782 1300
    
   CLIENT_IDENTIFIER
   miller
   

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.