How to log user operations in a web application?

Question

Hi.

The environment of my application: web-based, Spring MVC+Security, Hibernate, MySQL(InnoDB)

I am working on a small database application operated from a web interface. There are specific and known users that handle the stored data. Now I need to keep track of every create/update/delete action a user executes on the database and produce simple, "list-like" reports from this. As of now, I am thinking of a "log" table (columns: userId + timestamp + description etc.). I guess an aspect could be fired upon any C(R)UD operation inserting a log row in this table. But I am not sure this is how it should be done.

I am also aware of the usual MySQL logs as well as log4j. As for the logfiles, I might need more information than what is available to MySQL. Log4j might be a solution, but I do not see how it is able to write to MySQL tables. Also, I would like to have some associations preserved in my log table (e.g. the user id) to let the db do the basic filtering etc. Directions on this one appreciated.

What would you recommend? Is there even any built-in support in Hibernate/Spring or is log4j the right way to go?

Thanks!

Answer 1

1. Log4j is modular, you can write your own backend that writes the log into a database if you wish to do so; in fact, it even comes with a JDBC appender right out of box, although make note of the big red warning there.
2. For Hibernate, you probably can build something on the interceptors and events that keep track of all modifications and log them to a special audit table.

Answer 2

In case you go for logging solution and looking for doing it yourself, try searching for JDBCAppender, it's not perfect but should work. In case you want off the shelf product for centralized logging - consider trying logFaces - it can write directly into your own database.

Answer 3

Have you looked into using a MappedSuperclass for C(R)UD operation logging?

@MappedSuperclass
public class BaseEntity {
@Basic
@Temporal(TemporalType.TIMESTAMP)
public Date getLastUpdate() { ... }
public String getLastUpdater() { ... }
...
}

@Entity class Order extends BaseEntity {
@Id public Integer getId() { ... }
...
}