Version 15

    Integrating Oracle VPD, proxy connections and JBoss application server


    Pete Bennett (


    5th June 2007



    Oracle VPD provides a mechanism to handle fine-grained security (a.k.a. row level security) within a relational database system. This involves each user of the database having their own login credentials (i.e. username and password) to the database with Oracle then making decisions based on the username of the logged in user.


    JEE application servers such as JBoss AS provide a mechanism by which a pool of connections can be provided to a database. This mechanism is necessary for performance reasons as creating a new connection is an expensive operation. However, in order for a connection pool to share connections, all of the connections must be opened with the same username and password specified at the time of creation of the DataSource. It is then required to manage security within the application server as the connections to the database are effectively granted "super user" permissions and the AS restricts access.


    For some users of JEE application servers, especially in high security environments, it is unacceptable to use security in the middleware and integration is required with an existing Oracle VPD environment and setup.


    Oracle VPD provides the concept of a proxy user. In this scenario, a connection is opened to the database using a specific proxy account and then additional information can be supplied to this connection indicating the proxy user. Requests in the database are issues as if executed with the proxy user's permissions, including fine-grained security.


    This Wiki page describes a proof-of-concept (PoC) of how JBoss AS and Oracle VPD may be integrated without the need for changes to either the Oracle VPD setup or the JEE servlet and EJB application code.




    The example presented here, including configuration files and code, is a a proof-of-concept designed to show how VPD awareness can be added to J2EE application without code modification within the servlets/JSPs and EJBs that make up such an application. It has not been tested for robustness and should not be used in production without further validation and development. More information and any available updates can be found at .




    To Run

    • J2SE 5.0

    • Oracle 10g Enterprise

      • system login required

    • JBoss Enterprise Application Platform (JBEAP) 4.2 RC 4

    • The attached file


    To Build and Modify

    • Apache Ant 1.6.5

    • JBoss IDE 2.0.0beta2 (recommended)




    • Unzip into a clean directory which we will refer to as '/' in this document

    • Install Oracle 10g Enterprise into $ORA_HOME

    • Install J2SE5 into $JAVA_HOME

    • Install JBEAP into $JBOSS_HOME

    • Logon to Oracle as system and run /sql/createAccounts.sql to setup the database tables and VPD

    • Edit /vpd-ds.xml to configure the connection-url, user-name and password for your Oracle database

    • Copy ojdbc14.jar (Oracle JDBC Drivers) and /ProxiedVPDDriver.jar to $JBOSS_HOME/jboss-as/server/default/lib to allow JBEAP access to Oracle and PoC drivers

    • Start the JBoss server by opening a command terminal and executing $JBOSS_HOME/jboss-as/bin/run

    • Deploy the sample code by copying /OracleVPD.ear to $JBOSS_HOME/jboss-as/server/default/deploy

    • The terminal window should show the .ear deploy without errors




    This PoC uses a very simple Bank domain object which has methods to query named bank accounts and to update the balance on named bank accounts. The createAccounts.sql created three accounts in the database under the names of adam, bill and charlie.


    The application is secured by a mixture of JEE level security and Oracle VPD security. / and / define adam, bill and charlie as users with the role vpdUser. This allows them access to the pages which require a login. vpdAdmin is also created and has the roles vpdUser and vpdAdmin. Finally, a user neville is created who does not have membership of the vpdUser role.


    Open a browser and navigate to http://localhost:8080/OracleVPD/.


    Users are allowed to access their own account balances. Enter adam into the first text box and click "Get Balance". You will be redirected to the login page where you should login as adam (password west). You should then see adam's balance (initially 100.0). Click on the "Home Page" button.


    Users are not allowed to access other people's balances, or even to know if the entered name is valid. Note that we are still logged on as adam. Enter bill into the first text box and click "Get Balance". You should see a message indicating that "Either this account does not exist or you do not have permissions to view the balance." - this request has been filtered by Oracle VPD at the database level. Click on "Home Page" button. Try to "Get Balance" for account name "percival". You will see the same message even though no account named percival exists. Click on "Home Page" button.


    Users can not update any balances. Enter adam into the second text box and 1000000 into the third and then click on "Set Balance". You should see the message "SecurityException: Access Denied at Database Level" - this request has been blocked by Oracle VPD at the database level.


    vpdAdmin can update any balance but not view existing balances. Click on "logout" to log out adam. Now enter adam into the first text field and click "Get Balance". This time logon as vpdAdmin (password vpdAdmin). You will not be able to see adam's balance (filtered by VPD. Return to the home page and enter adam in the second and 0 in the third text boxes and then click "Set Balance". You have now updated adam's balance to 0. Click on logout.


    Access can be restricted at the AS level. Try entering adam in the first text box and clicking "Get Balance". Try to logon as percival (password letmein). At the level of HTTP authentication this will fail and a custom error page presented. Click on "Home Page" and try to logon as neville (password chamberlain) instead. This user exists in the user/ specifications but does not have any permissions to access our pages. Hence we are redirected to a default 403 page. In the real world we would produce a custom 403 page for our application but this has been left in to show all the options.


    Unfortunately the client browser now has the neville credentials cached so to keep playing with the example you will need to shutdown and restart your browser - another good reason for a customer HTTP 403 page.


    Explanation - how does it work?


    vpd-ds.xml - configuring the DataSource

    Normally for an Oracle DataSource, we would configure the driver-class to be oracle.jdbc.driver.OracleConnection. This implementation of the java.sql.Driver

    interface is used by JBoss AS to provide a DataSource which contains a pool

    of Connections to the specified database.


    Instead, we set the driver-class to be sample.vpd.jdbc.ProxiedVPDDriver, the implementation

    of java.sql.Driver which contains the interesting code in this PoC.




    ProxiedVPDDriver delegates all of its methods to an underlying OracleDriver object with the

    exception of connect(...) which, after obtaining an OracleConnection from the OracleDriver

    wraps it in the inner class ProxiedVPDConnection.


    ProxiedVPDConnection also delegates the majority of its methods to this underlying

    OracleConnection with the twist that, rather than using the OracleConnection directly,

    it uses the OracleConnection.openProxySession(...) method (properietary to Oracle)

    to add information about the logged on username to the connection before making the

    requested functionality call.


    ProxiedVPDConnection.getUserName() shows the mechanism by which the logged on username

    is obtained and ProxiedVPDConnection.openProxyConnection(...) shows how the username

    is added to the context of the connection before the SQL call is made.


    sample.vpd.ejb and sample.vpd.servlet


    These packages provide a very simple implementation of a Bank domain object as an EJB3 stateless session bean and a hand-coded ControllerServlet.


    The named JNDI DataSource is injected into the sample.vpd.ejb.Bank which manages its own interactions with the underlying database for illustrative purposes.


    The ControllerServlet simply marshalls calls from one page to another.


    The most notable thing about these packages is that they contain no knowledge whatsoever of VPD or of Oracle - a key design goal for the PoC.



    This is where you can find the .jsp pages that make up the UI part of the PoC.


    Notes, Caveats and Thoughts



    Accessing information derived from an HTTP request from within the ProxiedVPDConneciton wrapper (i.e. at the JDBC driver level) is fundamentally a bad design (the JDBC driver should not be coupled to a specific higher level calling mechanism).


    The correct mechanism is what the J2EE Connector Architecture (JCA) calls

    reauthentication support. That is when a connection is retrieved from the pool

    there is a "private" api that associates the current user with the real connection,

    either from the Subject or the ConnectionRequestInfo (CRI).


    Unfortunately, the current JDBC ResourceAdapter within JBoss doesn't

    have Reauthentication support, there is a feature request that explains how to

    implement it (i.e. it is a plugin class based on whatever mechanism the

    db users to change the user).


    In the simplest case it might be something as running some sql against the

    connection, e.g.

    "set $user=... $password=..."

    but in other cases it requires special classes.


    If you build a solution based on this PoC you should accept this architectural limitation and make sure you keep the code which determines the user context encapsulated so that, at a future date, getUsername() can be handled in a cleaner fashion.


    Interaction with Second Level Caches

    A cache which sits on top of the JDBC layer (e.g. Hibernate's second level cache)

    will not be aware of the security limitations under which the VPD proxy user should access information

    hence data will be cached from the database "above" the VPD level which should only be accessed

    by one user and then

    be available for viewing by user without invoking a database call and hence without checking VPD

    security.  Therefore, do not use second level caching (and most likely any other kind of data caching)

    when using VPD or similar mechanisms!


    You could probably write your own custom cache provider and ensure that users data is cached and

    used locally for that single user, but then the point of caching and connection pooling (performance and reduced database

    access) becomes less relevent.


    Useful Links