1) Which application is calling the procedure
2) Who is currently logged into the application
After investigating the topic a bit, I've found OracleConnection.setEndToEndMetrics method in oracle JDBC driver. Using this method, you can populate some fields in v$session view, including v$session.client_identifier and v$session.module. In our case, logged in user goes to client_identifier and calling application to module.
There are already some samples of setting client identifier using this method, but I found most of them incomplete. Here comes another one:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package my.package; | |
import static oracle.jdbc.OracleConnection.END_TO_END_CLIENTID_INDEX; | |
import static oracle.jdbc.OracleConnection.END_TO_END_MODULE_INDEX; | |
import static oracle.jdbc.OracleConnection.END_TO_END_STATE_INDEX_MAX; | |
import static org.springframework.util.StringUtils.hasText; | |
import oracle.jdbc.OracleConnection; | |
import org.aspectj.lang.annotation.AfterReturning; | |
import org.aspectj.lang.annotation.Aspect; | |
import org.springframework.beans.factory.annotation.Autowired; | |
import org.springframework.security.core.Authentication; | |
import org.springframework.security.core.context.SecurityContext; | |
import org.springframework.stereotype.Component; | |
import java.sql.Connection; | |
import java.sql.SQLException; | |
@Aspect | |
@Component | |
public class OracleConnectionMetricsSettingAspect { | |
private static final String APPLICATION_NAME = "MY_APP"; | |
private SecurityContext securityContext; | |
@AfterReturning( | |
pointcut = "execution(* javax.sql.DataSource.getConnection(..))", | |
returning = "connection") | |
public void setMetrics(Connection connection) throws SQLException { | |
Connection metaDataConnection = connection.getMetaData().getConnection(); | |
if (!(metaDataConnection instanceof OracleConnection)) { | |
return; | |
} | |
String[] metrics = new String[END_TO_END_STATE_INDEX_MAX]; | |
if (hasText(getLoggedInUser())) { | |
metrics[END_TO_END_CLIENTID_INDEX] = getLoggedInUser(); | |
} | |
metrics[END_TO_END_MODULE_INDEX] = APPLICATION_NAME; | |
((OracleConnection) metaDataConnection).setEndToEndMetrics(metrics, (short) 0); | |
} | |
private String getLoggedInUser() { | |
Authentication authentication = securityContext.getAuthentication(); | |
if (authentication == null) { | |
return null; | |
} | |
return authentication.getName(); | |
} | |
@Autowired | |
public void setSecurityContext(SecurityContext securityContext) { | |
this.securityContext = securityContext; | |
} | |
} |
You can see here, that we are using AOP to intercept javax.sql.DataSource.getConnection() methods and populate all connections with logged in user from Spring security SecurityContext. Module is just a constant.