I have here an application that allows multiple user to connect via it to database. For auditing purpose the value of osuser must be stored.
For a new connection it is easy to add a value:
Properties prop = new Properties();
props.setProperty(oracle.jdbc.OracleConnection.CONNECTION_PROPERTY_THIS_VSESSION_OSUSER, "Apple");
Connection con = DriverManager.getConnection("jdbc:.....", props);
But is it also in some way possible to change it for an already established connection?
I would prefer to avoid connecting to the backend every time a customer requests some data.
My ideal solution would be to have a pool of, say, 10 connections for which in case customer is doing something I intercept the request, set osuser to his user, do some SQL, then release the connection reseting again the customer osuser, so I can reuse it next time.
Any ideas?
Thanks!
osusershould identify the OS owner of the client processes on the client computer - not the application end user. This is especially true for 2- or 3-tier application server situations where the OS user is typically a service account of some kind. It should never be changed in mid-session. If you need to identify the end-user through the application, within the database, use theDBMS_APPLICATION_INFOorDBMS_SESSIONpackages to set session or application context attributes for auditing.osuserfor a in this way, you are actually losing valuable auditing data. Having to reconnect isn't just inconvenient, it also isn't performant or scalable: it defeats the entire purpose of having a connection pool. The appropriate tools for the need you described are the PL/SQL packages I mentioned.