Integrating Oracle VPD, proxy connections and JBoss application server
Pete Bennett (pbennett@redhat.com)
5th June 2007
Summary
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.
Disclaimer
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 http://wiki.jboss.org/wiki/Wiki.jsp?page=IntegratingOracleVPDandJBoss .
Pre-requisites
To Run
J2SE 5.0
Oracle 10g Enterprise
system login required
JBoss Enterprise Application Platform (JBEAP) 4.2 RC 4
The attached OracleVPD.zip file
To Build and Modify
Apache Ant 1.6.5
JBoss IDE 2.0.0beta2 (recommended)
Installing
Unzip OracleVPD.zip 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
Running
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. /roles.properties and /users.properties 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/roles.properties 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.
sample.vpd.jdbc.ProxiedVPDDriver
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.
\html
This is where you can find the .jsp pages that make up the UI part of the PoC.
Notes, Caveats and Thoughts
Architecture
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).
http://jira.jboss.com/jira/browse/JBAS-1429
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
Oracle VPN Tutorial
Comments