Passing ClientInfo/ClientIdentifier on syscontext/connection with Hibernate for audit purposes
<p>I have a web service which processes inserts/updated data to DB. When client calls this webservice, <strong>UserId</strong>(currently logged in user to portal) will be sent in Request. I need to pass this userId to Db connection or set it in <strong>sys context</strong> for <strong>Audit purpose</strong>. we have existing audit tables and triggers to inserts/updates to Audit table after insert/update on actual table. So to track these changes I need to pass this UserId somehow to <strong>connection</strong> so that it can be retrieved from DB from Sys Context or $session and inserts in Audit table. I am currently using Spring and Hibernate transactions to process data with DB.</p>

<p>I tried to Set client info on Connection but it's not working. I tried below:</p>

<pre><code>Session session=sessionFactory.getCurrentSession();
SessionImpl sImpl=(SessionImpl) session;
Connection connection=sImpl.connection();
connection.setClientInfo("ClientUser", "ABC");
</code></pre>

<p>And also I am trying to set client info by calling Stored procedure <strong>DBMS_APPLICATION_INFO.SET_CLIENT_INFO</strong> before performing operation on DB every time from application code.but I am not sue if it's a correct way to handle it.</p>

<p>I am trying it with both OCI and thin JDBC drivers but not able find a way to set this user id.</p>

<p>Can someone let me know if there is any efficient way to pass user id on sys context or with Connection. I am currently using <strong>hibernate4, Spring, Websphere Server, Oracle DB.</strong></p>

<p>I am using Spring @Transactional to handle hibernate Connections and transactions to perform operation on DB.Connections are from Connection pool and I am using <strong>org.springframework.jndi.JndiObjectFactoryBean</strong> for <strong>dataSource</strong>. </p>

<p>is there any way to have interceptor or wrapper around connection to set it when we get the connection from connection pool.</p>

<p>Has anyone done this before?</p>