Java Spring application Database triggers auditing - how to provide the right user that made the change 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?
Unless you create a different database user for each application user -so it is available with the user() or current_user() functions: 

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

mysql&gt; 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&gt; SET @user := 'jynus';
Query OK, 0 rows affected (0.01 sec)

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

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

mysql&gt; 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&gt; INSERT INTO test VALUES (5);
Query OK, 1 row affected (0.00 sec)

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