Hi:

We are trying to implement the Oracle 9i Virtual Private Database. It requires extracting the Connection object and then creating a connection specific to the vendor and then setting a client id. E.g.

Connection conn = ds.getConnection();

oracle.jdbc.OracleConnection orConn = (oracle.jdbc.OracleConnection) ((WLConnection) conn).getVendorConnection());

//set the user id for the VPD:
orConn.setClientIndentifier( loggedInUserID );

How do we implement this in Hibernate? Since hibernate uses Session - We thought of extracting the Connection from the session. But we cannot create a new Session from the orConn as oracle.jdbc.OracleConnection does not extend java.sql.Connection.

Looking forward to your help!

Thanks
Amol

i cant belive that the connection used in oracle VPD doesnt implement the java sql interface...anyhow you must be able to get a real usable connection from somewhere and to handle that you might need to write your own connection provider ?

Thanks for your time and reply ! and apologies for my ignorance! Oracle connection DOES implement the java.sql.Connection interface. I missed checking the interface list in the API (how dumb!)

What we are trying now is to obtain a Connection object from a temp session and then setting it back to create a new session which would be used in the application.

E.g.
Session tmpSession = sessionfactory.openSession();
Connection conn = tmpSession.connection();

oracle.jdbc.OracleConnection orConn = (oracle.jdbc.OracleConnection) ((WLConnection) conn).getVendorConnection());

//set the user id for the VPD:
orConn.setClientIndentifier( loggedInUserID );

Session actualSession = sessionfactory.openSession( orConn );

return actualSession;


We would be using the hibernateUtil with the interceptor as well. the code above is just an example for the concept. Is there a cleaner way to do this? would "session.reconnect(orConn)" work?

Thanks for your help!
Amol

It should not be necessary to create a new session - try using the first one after doing that oracle stuff.

hi Amol

Did you get this to work?

cheers
Paul

Hey paul

Sorry for the late reply. But the VPD connection does work for me. Although with a few config changes. We are using the 9.2.0.4 Oracle thin JDBC driver (ojdbc14.jar) which needs to be updated over the default jar in the <bea-home>/server/lib

Also we are using the latest Hibernate 3.0.5 jar file.

The code that works is:



Is late but maybe helps.

Thanks
Amol

Guys, it would be very cool if you set a wiki page with your experience. Be careful not to retieve sensitive things from the second level cache, since it has no notion of the virtual user.

we have a session factory where we get a session and from there we try to get the connection (with session.connection()) also. Should we use sessionfactory.openSession() rather than sessionfactory.getCurrentSession() (or whatever).

When we try to cast that connection basically how the guys above did, we receive something like this: "ClassCastException: $Proxy15"

What is with this proxy stuff? The documentation says that you get the real jdbc connection with session.connection() but that seems to be a lie. I tried getting the datasource like those guys did above and that works fine, but I thought you're supposed to use the sessions.

Posted: Fri Mar 07, 2008 10:56 am Post subject: About Hibernate and Oracle VPD

Hi,

I just want to implement Oracle VPD features in my application, how would i be able to make a connection from hibernate.

My Reference site are :

1. http://www.adp-gmbh.ch/ora/security/vpd/index.html

2. http://javazone.org/howto/hibernate-with-oracle-vpd

I am using Jboss server, as discussed in this forum to make a connection

Some configuration done in the weblogic server it seems, could some one explain me how this is done.

Is there is any other refernce document to go through and get some idea regarding.

Thanks,
Ambika.

Hi,

I've had the same task of integrating Hibernate with the existing system that uses Oracle VPD. I spent some time searching for the solution online, but only found outdated bits and pieces and no single place that would sum it up. Hopefully, my findings will help others in the same situation. This is definitely a hack, but unfortunately, I could not find a more elegant solution.

We have Oracle 10g and Hibernate 3.6.0 with C3P0 connection pool.

To make Oracle VPD work (dynamically changing database users), you need to get underlying oracle.jdbc.OracleConnection class and call an Oracle-specific method to create a proxy session.

1. How to get a native connection.

Unfortunately, C3P0 does not make it easy to retrieve the native JDBC connection. The only way I could do it was to retrieve a proxy class via deprecated session.connection() and call the rawConnectionOperation on C3P0ProxyConnection.

the skeleton of the implementation is:

Connection conn = session.connection();
C3P0ProxyConnection connImpl = (C3P0ProxyConnection) ((ConnectionWrapper)conn).getWrappedConnection();

connImpl.rawConnectionOperation(<your method>, ...)

2. What to call on a native connection.

In Oracle 9 JDBC, OracleConnection used to have a method setClientIdentifier, which was removed in Oracle 10. The method that worked for me in 10g was

Properties prop = new Properties();
prop.put(OracleConnection.PROXY_USER_NAME, "<user_name>");
connection.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, prop);

This creates a lightweight session for a specified user.

Best Regards,
Boris.

It would be great to add that on the Community section in the wiki