Mysql – Java Spring application Database triggers auditing – how to provide the right user that made the change

auditMySQLtrigger

We are developing a new web application.
one of the most basic requirement is to audit all entities changes into a separate table.

We would like to use DB triggers for that purpose.

We use MySQL as our RDMBS.

The problem we now foresee is that whenever a trigger is pulled, and insert a new entry for the DB, it cant possibly know the (applicative) user that made the change. (all users have different ids, but spring uses a single user account for the db manipulations.)

Any ideas how to resolve this issue?

Best Answer

Unless you create a different database user for each application user -so it is available with the user() or current_user() functions: 

mysql> SELECT user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

it is not possible to know this at MySQL level unless it is provided by the application.

You could use SQL comments on application code:

/* user=jynus */ INSERT INTO test values (1);

Which can later be processed using the slow log, if active, or some of the auditing plugins. Please note that the mysql command-line client application deletes comments before sending them to the server.

The only way you can log it using triggers with a single user that I can now think is setting a variable at the start of the connection:

mysql> SET @user := 'jynus';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test (i int PRIMARY KEY);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE log (id serial, i int, user varchar(100));
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TRIGGER test_AI AFTER INSERT ON test FOR EACH ROW 
       INSERT INTO log (i, user) VALUES (NEW.i, @user);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test VALUES (5);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM log;
+----+------+-------+
| id | i    | user  |
+----+------+-------+
|  1 |    5 | jynus |
+----+------+-------+
1 row in set (0.00 sec)
Related Question