The user definition must be borne by the application itself, as it controls the actions in the database. Unless the database had registered each user and the connection used their credentials.

In general, I would recommend auditing in the application as it is more flexible than directly in the database.

However, I know that auditing the bank also has its advantages there, for example in the case of changes via script or from other systems.

And it is precisely because of such situations that it is difficult to rely on a parameter defined by the application. This coupling can cause problems for changes in the database that do not come through the application.

Without thinking about a deeper change, we can think of ways to get around this situation.

But first, we must understand that the connections in a web application are usually in a pool and are reused in several requests, possibly by different users.

Defining the user whenever accessing the bank

One way to get around this is by setting the user in the database at the beginning of each method that will access the database.

To make this easy, you could use Aspects Orientation with Spring AOP to intercept all DAO methods, for example, so that the command runs automatically.

Another possibility for applying AOP would be to create a @Auditoria annotation. So whenever a method with this annotation was executed, the user would be passed to the database.

Encapsulate the connection

Another alternative would be to create a bean encapsulating the connection, which would execute the command defining the user whenever it was used.

The scope of this bean would have to be request , to be re-created for each request. It would then retrieve a connection and set the user.

bean could implement Connection methods to facilitate integration.

Connection reuse filter

A third approach would be to place a request filter in the application.

At each request it opens a connection and updates the user name.

This connection would have to be stored during the entire request and used by all methods that access the database.

Considerations

Finally, I tried to give some ideas. In fact it would be necessary to ascertain exactly how the system works to not end gambiarras, but I hope to have helped you think about a possible solution.

Good afternoon.

I chose to do AOP using Aspectj . In case I have a BaseDao and caused me to intercept the methods before they are executed so I get the connection and this is a prorpity (see set for more info) and in my trigger I get the value of these properties. Here is the code:

import javax.servlet.http.HttpSession;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.beans.factory.annotation.Configurable;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;

@Aspect
@Component
@Configurable
public class HistoricoAlteracaoAspect {
    @Before("execution(* net.empresateste.projeto.base.BaseDAO.*(..)) && args(bean)")
    public void beforeExecute(JoinPoint joinPoint, Object bean) {
        // public void beforeExecute(JoinPoint joinPoint, Object bean, String username) {
        if (joinPoint.getTarget() instanceof UsuarioDaoImpl) {
            UsuarioDaoImpl dao = (UsuarioDaoImpl) joinPoint.getTarget();
            HttpSession session = (HttpSession) RequestContextHolder.currentRequestAttributes().resolveReference(RequestAttributes.REFERENCE_SESSION);
            Object userAtribute = session.getAttribute("user");
            if (userAtribute != null) {
                if (session.getAttribute("user") != null) {
                    String sql = "set session myapp.usuario = '" + userAtribute.toString().toLowerCase() + "';";
                    dao.getJdbcTemplate().execute(sql);
                    sql = "set session application_name = 'myapp';";
                    System.out.println("BEFORE HASHCODE " + userAtribute + " " + dao.getJdbcTemplate().hashCode());
                    dao.getJdbcTemplate().execute(sql);
                }
            }

        }
    }
}

I hope I have helped