Safely Wrapping a Connection Pool

Question

I am trying to implement row level security so our application can enforce more stringent access control.

One of the technologies we are looking into is Oracle's Virtual Private Database, which allows row level security by basically augmenting all queries against specific tables with a where clause predicate. Since we are in a web environment, we need to set up a special context within Oracle, inside a single request's thread. We use connection pooling with a service account.

I started to look into Eclipse Link and Hibernate. Eclipse Link seems to have events that fit perfectly into this model.

This would involve us migrating from hibernate, which is not a problem, but we would then be bound to EL for these events.

Oracle seems to imply that they implement at the data source level in Web Logic product.

The context is set and cleared by the WebLogic data source code.

Question: Is it more appropriate to do this at the DataSource level with some series of events. What are the events or methods that I should pay the most attention too?

Added Question: How would I extend a connection pool to safely initialize an oracle context with some custom data? I am digging around in Apache, and it seems like extending BasicDataSource doesn't give me access to anything that would allow me to clean up the connection when Spring is done with it.

I need to set up a connection, and clean up a connection as the exit / enter the connection pool. I am hoping for an implementation that is so simple, no one can mess it up by breaking some delicate balance of products.

- Specifically we are currently using Apache Commons DBCP Basic Data Source

This would allow us to use various ways to connect to the database and still have our security enforced. But I don't see a great example or set of events to work with, and rolling my own security life cycle is never a good idea.

Answer 1

I eventually solved my problem by extending some of the Apache components.

First I extended org.apache.commons.pool.impl.GenericObjectPool and overrode both borrowObject() and returnObject(). I knew the type of the objects in the pool (java.sql.Connection) so I could safely cast and work with them.

Since for my case I was using Oracle VPD, I was able to set information in the Application context. I recommend you read about that in more depth. It is a little complicated and there are a lot of different options to hide or share data at various contexts level, and across RAC nodes. Start

In essence what I did was generate a nonce and use it to instantiate a session within oracle, and then set the access level of the user to a variable in that session, that the Oracle VPD policy would then read and use to do the row level filtering.

I instantiated and destroyed that information in my overridden borrowObject() and returnObject() The SQL I ran was something like this:

CallableStatement callStat =
                    conn.prepareCall("{call namespace.cust_ctx_pkg.set_session_id(" + Math.random() + ")}");
                  callStat.execute();

Note math.random() isn't a good nonce.

Next was to simply extend org.apache.commons.dbcp.BasicDataSource and set my object pool by overriding createConnectionPool(). Note that the way I did this disabled some functionality I did not need, so you may need to rewrite more or less than I did.