A dba in my office proposed the following to audit db access (oracle):
Put the current logged in user in my application as a property on the db connection (java.sql.Connection).
Code:
public class ClientInfoProvider {
void enrichConnection(Connection connection, String userName) throws SQLException {
OracleConnection oConn = (OracleConnection) connection;
String[] metrics = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = userName;
oConn.setEndToEndMetrics(metrics, (short) 0);
}
}
public class AuditableDataSource implements DataSource {
...
@Override
public Connection getConnection() throws SQLException {
Connection connection = dataSource.getConnection();
clientInfoProvider.enrichConnection(connection, userName);
return connection;
}
...
}
My main concern is that connections are pooled. Wil users not end up using the same connection? The last user which changes the properties of the connection might influence the other users that are already using this connection. Is this a correct assumption?
Does a connection gets locked when it is used for a single session?
In the docs I can find that a connection is released by a session once it end, but not that one session locks the connection for private use while the session is running.
Thanks for the insights!