using Oracle proxy_user across JDBC datasource

[jhalmes]

I am trying to setup an Oracle proxy_user login configuration where there is a single user with connect only privileges configured in the ds.xml file and then each connection calls Oracle's (proprietary) openProxySession method to scope the connection's permissions to a user with specific access controlled by Oracle's OLS mechanism.

I found this wiki entry:
http://wiki.jboss.org/wiki/Wiki.jsp?page=IntegratingOracleVPDandJBoss

It describes using a custom class to wrap the OracleDriver so that every call to the Connection will open the proxy session transparently. However after following the instructions it doesn't appear to work. Somehow JBoss appears to be getting a non-wrapped Oracle driver or something and is not being proxied. I suspect the issue has something to do with the LocalManagedConnectionFactory or WrapperDataSource that JBoss uses to manage connection pools.

Does anyone know how the connection objects are being instantiated into the pool? I'm wondering if a clone or some reflection is being used and somehow getting to the actual Oracle driver or connection class underneath the Proxied class which negates the call to openProxySession. Any ideas?

-jasen

[jhalmes]

I traced through the jboss code and found the problem. It appears that the managed connection factory stores drivers in a map of some sort using the url as the key. In my case I had 2 ds.xml files deployed with different jndi names, but the same url. One of them had the actual OracleDriver specified and was stomping on my proxied driver config. The example from the wiki works great now.

fyi, unless there's some really compelling reason that I'm not aware of, I'd change the managed connection factories to store things with a more unique key. I can imagine situations where using more than one datasource config trying to talk to the same url could reasonably happen. If possible getting the jndi name and just appending it to the url string for the purposes of using it as a key would probably work.

-jasen