MySQL審計功能

MySQL審計功能介紹

之前有同事發現資料丟失，由於MySQL自身沒有提供審計功能,查詢binlog可以看到操作時間與連線IP，並不顯示哪個使用者做的操作，詢問開發同事都說沒有做刪除操作，也沒辦法定位操作的人員證據，無奈只能恢復資料。

general log會記錄詳細的SQL執行記錄，但是生產環境如果業務量大，會產生大量的磁碟IO操作，嚴重降低資料庫效能，所以生產環境一般不會開啟general log。後來發現可以使用init-connect + binlog的方法進行mysql的操作審計。由於mysql binlog記錄了所有對資料庫長生實際修改的sql語句，及其執行時間，和connection_id但是卻沒有記錄connection_id對應的詳細使用者資訊。在後期審計進行行為追蹤時，根據binlog記錄的行為及對應的connection-id 結合 之前連線記錄進行分析，得出最後的結論。

設定init-connect

1、建立用於存放連線資訊的表

mysql> create database AuditDB default charset utf8;
mysql> use AuditDB;
mysql> create table accesslog (
  ID int primary key auto_increment,
  ConnectionID int, 
  ConnUser varchar(30), 
  MatchUser varchar(30), 
  LoginTime datetime
);

2、保證所有連結使用者對此表有寫入許可權

mysql> insert into mysql.db (Host,Db,User,Insert_priv) values ('%','AuditDB','','Y');
Query OK, 1 row affected (0.03 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3、設定init-connect

在[mysqld]下新增以下設定：
#設定初始化連線操作
init-connect='Insert into AuditDB.accesslog(ConnectionID, ConnUser, MatchUser, LoginTime) values(connection_id(),user(),current_user(),now());'
#開啟binlog
log-bin=xxx

4、重啟資料庫生效

shell> /etc/init.d/mysql restart

連線測試查詢
mysql> select * from AuditDB.accesslog;
+----+--------------+--------------------+-----------------+---------------------+
| ID | ConnectionID | ConnUser           | MatchUser       | LoginTime           |
+----+--------------+--------------------+-----------------+---------------------+
|  1 |            1 | svoid@localhost    | svoid@localhost | 2015-04-24 14:16:18 |
|  2 |            3 | svoid@192.168.56.1 | svoid@%         | 2015-04-24 14:16:53 |
+----+--------------+--------------------+-----------------+---------------------+
2 rows in set (0.00 sec)

查詢操作記錄

1、 進行模擬操作，下列操作可由多個連線進行

mysql> use test;
Database changed

mysql> create table t (id int ,name varchar(20));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t values(1,'a');
Query OK, 1 row affected (0.16 sec)

mysql> insert into t values(2,'b');
Query OK, 1 row affected (0.03 sec)

mysql> truncate table t ;
Query OK, 0 rows affected (0.03 sec)

2、根據binlog，確認操作truncate的thread_id

mysqlbinlog --start-datetime='2015-04-24 14:10:00' --stop-datetime='2015-04-24 14:25:00' /db/mysql/data/mysql_info.000007 | grep -B 5 truncate
# at 1223
#150424 14:23:36 server id 1  end_log_pos 1302     Query    thread_id=3    exec_time=0    error_code=0
SET TIMESTAMP=1429856616/*!*/;
/*!\C gbk *//*!*/;
SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=33/*!*/;
truncate table t

根據上面的提示可以看到操作的thread_id=3

3、確認操作的使用者

mysql> select * from AuditDB.accesslog where ConnectionID=3;
+----+--------------+--------------------+-----------+---------------------+
| ID | ConnectionID | ConnUser           | MatchUser | LoginTime           |
+----+--------------+--------------------+-----------+---------------------+
|  2 |            3 | svoid@192.168.56.1 | svoid@%   | 2015-04-24 14:16:53 |
+----+--------------+--------------------+-----------+---------------------+
1 row in set (0.00 sec)

補充

1. init-connect 是不會在super使用者登入時執行， 所以最好不使用超級使用者
2. 如多人使用同一使用者可能無法區分，最好一個人分配一個資料庫操作使用者
3. 理論上，使用者每次連線時往資料庫裡插入一條記錄，不會對資料庫產生很大影響，考慮降低連線頻率及accesslog插入效率優化

參考：
http://www.cnblogs.com/cenalulu/archive/2012/05/09/2491736.html

整理自網路