I have a web service which processes inserts/updated data to DB. When client calls this webservice, UserId(currently logged in user to portal) will be sent in Request. I need to pass this userId to Db connection or set it in sys context for Audit purpose. 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 connection 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.
I tried to Set client info on Connection but it's not working. I tried below:
Session session=sessionFactory.getCurrentSession();
SessionImpl sImpl=(SessionImpl) session;
Connection connection=sImpl.connection();
connection.setClientInfo("ClientUser", "ABC");
And also I am trying to set client info by calling Stored procedure DBMS_APPLICATION_INFO.SET_CLIENT_INFO before performing operation on DB every time from application code.but I am not sue if it's a correct way to handle it.
I am trying it with both OCI and thin JDBC drivers but not able find a way to set this user id.
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 hibernate4, Spring, Websphere Server, Oracle DB.
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 org.springframework.jndi.JndiObjectFactoryBean for dataSource.
is there any way to have interceptor or wrapper around connection to set it when we get the connection from connection pool.
Has anyone done this before?
This is described in spring data JDBC Extensions for the Oracle Database
Chapter
8.2 Configuration of a Custom DataSource Connection Preparer
...but you could implement a ConnectionPreparer that would use the current users login id. That way you can capture user login information even if your data source is configured with a shared user name.
This is a solution for oracle, which I think you are using. It should be also possible to adapt that to another database.