Problem solve Get help with specific problems with your technologies, process and projects.

Using virtual private databases with application servers

This tip helps to clarify by example the use of Oracle's Virtual Private Database by an application server account.

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 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:


You can see that Testy's EMPNO is the MGR for employee Miller. We also included the output of the Client_Identifier to show that the OneBigApplication user session was customized for Testy, successfully implementing row-level security. If you were to run the the program for user MILLER, you would only see the employee record for Miller since he is not a Manager.

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 /

Create the Stored Procedure EMPSEC.SETEMPCTX to set Context at login

  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; /

Create the procedure used in the EMP table policy

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 *********************************************************

Create the Package

 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 the Package Body

 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; /

Create the policy used when accessing the EMP table

 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

Seed the EMP and VALIDATE_EMP tables with test data

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*; 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 ( ()) 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 ( ()) 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(); } }

Run the Example

  1. Edit the 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/ 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.

Dig Deeper on Oracle hosting, outsourcing and remote DBAs

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.