Home > Oracle Tips > Oracle Database Administrator > Using virtual private databases with application servers
Oracle Tips:
EMAIL THIS
 TIPS & NEWSLETTERS TOPICS 

ORACLE DATABASE ADMINISTRATOR

Using virtual private databases with application servers


David Powell
11.07.2002
Rating: -5.00- (out of 5)


Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us   


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
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 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();
  }
}
Run the Example
  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.

Rate this Tip
To rate tips, you must be a member of SearchOracle.com.
Register now to start rating these tips. Log in if you are already a member.


Submit a Tip




Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us   


RELATED CONTENT
Oracle Database Administrator
What is the difference between a database engineer, architect and administrator?
Import on one table from dump file
Error during RMAN backup
Can I drop a column in SYS schema?
STATSPACK tool: transaction vs. execution measurement
Should I port from Microsoft Access?
Installing multiple Oracle homes
How can I find statistics on total memory usage and database connections?
Modifying SYS password in a RAC environment
How to create Datafiles in a Data Guard (10g) environment

RELATED RESOURCES
2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
Search Bitpipe.com for the latest white papers and business webcasts
Whatis.com, the online computer dictionary

DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

HomeNewsTopicsTipsAsk the ExpertsMultimediaWhite PapersProductsBlogs
About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
SEARCH 
TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of technology-specific Web sites, events and magazines.

TechTarget Corporate Web Site  |  Media Kits  |  Reprints  |  Site Map




All Rights Reserved, Copyright 2003 - 2008, TechTarget | Read our Privacy Policy
  TechTarget - The IT Media ROI Experts