Wednesday, February 29, 2012

Populating Oracle v$session in Spring web app

Our project heavily relies on Oracle PL/SQL procedures. Those procedures are used by different applications and database developers always wanted to know two things:

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:

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.

No comments:

Post a Comment