Java Spring 應用程序數據庫觸發審計 - 如何提供做出更改的正確使用者

我們正在開發一個新的網路應用程序。最基本的要求之一是將所有實體的更改審計到一個單獨的表中。

我們想為此目的使用數據庫觸發器。

我們使用 MySQL 作為我們的 RDMBS。

我們現在預見的問題是，每當拉動觸發器並為數據庫插入新條目時，它就不可能知道進行更改的（應用程序）使用者。（所有使用者都有不同的 id，但 spring 使用單個使用者帳戶進行數據庫操作。）

任何想法如何解決這個問題？

除非您為每個應用程序使用者創建不同的數據庫使用者，否則它可以與user()orcurrent_user()函式一起使用：

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)

除非應用程序提供，否則不可能在 MySQL 級別知道這一點。

您可以在應用程式碼上使用 SQL 註釋：

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

稍後可以使用慢速日誌（如果處於活動狀態）或一些審計外掛來處理。請注意，mysql 命令行客戶端應用程序在將註釋發送到伺服器之前會刪除它們。

我現在認為可以使用單個使用者的觸發器記錄它的唯一方法是在連接開始時設置一個變數：

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)

您想要的稱為代理身份驗證。不確定 MySQL 處理它的效果如何，但 MariaDB 可以，PostgreSQL 也可以。

你需要使用連接池嗎？您期望有多少並髮使用者？

以下是它在 PostgreSQL 中的工作方式：

create role neil login password 'secret';

create role www noinherit login password 's3cret';

grant neil to www;

當我登錄時，連接到數據庫neil/secret以檢查我是否進行了身份驗證。

然後連接到數據庫www/s3cret

然後SET ROLE neil;

這為您提供了連接池（登錄除外）並且current_user是 neil，用於審計。

在 Java 中，您可以將 JdbcInterceptor 與 Tomcat 的連接池和 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);
   }
}

引用自：https://dba.stackexchange.com/questions/74526