Pass username to Rigger de historico

Question

I’m trying to implement a Trigger generic for registration history. I am following this link Audit Trigger that works perfectly. The only problem is that I can’t pass the user who is making the changes.

I use the Spring MVC and when I create the datasource step a single user so in the history all changes gets in the name of the same.

I tried using the SQL command

set session myapp.usuario = 'usuarioLogado'

but as it is Spring that controls the openings and closures of my connections it reuses the same connection so the parameter myapp.usuario it’s not right.

Similar problem as these:

• How can I set a username for a Postgresql Audit Trigger?
• Passing user id to Postgresql triggers

1 - Someone has been through these problems?

2 - How can I create history without having to be in charge of the application to do this?

Answer 1

The user’s definition should be in charge of the application itself, since it is it who controls the actions in the database. Unless the database had registered each user and the connection used the respective credentials.

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

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

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

Without considering a deeper change, we can think of some ways around this situation.

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

Setting the user whenever accessing the database

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

To facilitate this you could use Aspect Guidance with Spring AOP to intercept all Daos methods, for example, so that the command runs automatically.

Another possibility of applying AOP would be to create an annotation @Auditoria. 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 recreated with each request. It would then recover a connection and set the user.

The bean could implement the methods of Connection to facilitate integration.

Filter with connection reuse

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

With 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 accessing the database.

Considerations

Anyway, I tried to come up with some ideas. In fact it would be necessary to ascertain precisely how the system works not to end with gambiarras, but I hope to have helped you to think of a possible solution.

Answer 2

Good afternoon.

I chose to do AOP using the Aspectj. In case I have one Based and made it intercept the methods before they are executed so I caught the connection and Seto a prorpiedade (see set for more information) and in my Rigger I take the value of these properties. Follows 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’ve helped