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

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle database administrator Understanding SQL string functions 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? How can I find statistics on total memory usage and database connections? Installing multiple Oracle homes Modifying SYS password in a RAC 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

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

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

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. Create the Package Create the Package Body Create the policy used when accessing the EMP table 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. Run the Example

1. Edit the vpd.java and change the connect info and user info if needed
2. Compile the java class ( Unix ksh assumed below )
3. Query the database

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.