Question:
I'm trying to implement a generic trigger for registration history. I'm following this Audit Trigger link that works perfectly. The only problem is that I can't get past the user making the changes.
I use Spring MVC and when I create the datasource I pass a single user so in the history all changes are in the name of the same.
I tried using the SQL command
set session myapp.usuario = 'usuarioLogado'
but since it is Spring that controls the openings and closings of my connections it reuses the same connection so the myapp.usuario parameter is not correct.
Problem similar to these:
1 – Has anyone experienced these problems?
2 – How can I create history without having to be in charge of the application to do this?
Answer:
The definition of the user must be in charge of 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 in-application auditing as it is more flexible than directly in the database.
However, I know that putting auditing in the bank also has its advantages, for example in the case of changes via script or from other systems.
And precisely because of situations like these that it is difficult to trust a parameter defined by the application. This coupling can generate problems for changes in the database that do not come from the application.
Without considering a deeper change, we can think of some ways to get around this situation.
But first, we must understand that connections in a web application are often pooled and reused across multiple requests, possibly by different users.
Defining the user whenever accessing the bank
A way to work around the situation is to define the user in the database at the beginning of each method that will access the database.
To facilitate this you could use Aspect Orientation with Spring AOP to intercept all DAO methods, for example, so that the command is executed automatically.
Another possibility of applying AOP would be to create an @Auditoria annotation. So whenever a method with that 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 on each request. It would then retrieve a connection and define the user.
The bean could implement the Connection methods to facilitate integration.
Filter with connection reuse
A third approach would be to put a request filter in the application.
At each request it opens a connection and updates the username.
This connection would have to be stored during the entire request and used by all methods that access the database.
Considerations
Anyway, I tried to give some ideas. In fact, it would be necessary to find out precisely how the system works in order not to end up with hacks, but I hope I have helped you to think of a possible solution.