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

Question

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?

Answer 1

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)

Answer 2

What you desire is called Proxy Authentication. Not sure how well MySQL handles it, but MariaDB does and so does PostgreSQL.

Do you need to use Connection Pooling? How many concurrent users do you expect?

Here's how it would work in PostgreSQL:

create role neil login password 'secret';

create role www noinherit login password 's3cret';

grant neil to www;

When I login, connect to the database as neil/secret to check that I authenticate.

Then connect to database as www/s3cret

Then SET ROLE neil;

This gives you connection pooling (except for login) and current_user is neil, for auditing.

In Java, you can use a JdbcInterceptor with Tomcat's connection pool and Spring Security:

public class SetRoleJdbcInterceptor extends JdbcInterceptor {

    @Override
    public void reset(ConnectionPool connectionPool, PooledConnection pooledConnection) {

        Authentication authentication = SecurityContextHolder.getContext().getAuthentication();

        if(authentication != null) {
            try {

                String username = ESAPI.encoder().encodeForSQL(MY_CODEC, authentication.getName());

                Statement statement = pooledConnection.getConnection().createStatement();
                statement.execute("set role \"" + username + "\"");
                statement.close();
            } catch(SQLException exp){
                throw new RuntimeException(exp);
            }
        }
    }

    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {

        if("close".equals(method.getName())){
            Statement statement = ((Connection)proxy).createStatement();
            statement.execute("reset role");
            statement.close();
        }

        return super.invoke(proxy, method, args);
    }
}