Label Security and Connection Pooling... performance problem

cmaksymchuk · **Posted:** Fri Mar 25, 2011 5:18 pm

One of the mandates on our project is to use oracle label security, which we have been using successfully. In order to use label security, we need to 'stamp' the username of the user on the connection that comes out of the connection pool prior to using it. We accomplish this through a C3P0 connection customizer:

Code:

public void onCheckOut(Connection connection, String arg1) throws Exception {
   Properties properties = new Properties();      
   proxyAs = // get the username from the user in the session;
   properties.put(OracleConnection.PROXY_USER_NAME, proxyAs);
   if (connection instanceof OracleConnection) {
      if (!((OracleConnection) connection).isProxySession()) {
         ((OracleConnection) connection).openProxySession(OracleConnection.PROXYTYPE_USER_NAME, properties);
....

This has worked well for us, except the performance of the openProxySession is slow (between 1/10 of a second and 3 seconds (under simulated high load). This performance, is not good because it happens every single time we request a connection.

We need an alternative solution... where each user still has a connection with their name stamped on it, that performs better than the above (hopefully with a connection pool?).

Is there any connection pooling/datasource that allows users to hold on to their connections for a period of time. Alternatively, does anyone know how to speed up the openProxySession method? I am open to all solutions and could really use some help.

Cheers,

Cory

archos · **Posted:** Mon Mar 05, 2012 9:34 am

We do not use hibernate but we solved the problem with a plsql session initialzation function which just setzt the ols user (with sa_session.set_access_profile) when its not already set.
when we put the session back to the pool we do not reset the ols session state. So many ols session initialization are saved.