MySQL日志審計 幫你揪出內個干壞事兒的小子

MySQL日志審計 幫你揪出內個干壞事的小子

簡介

Part1:寫在最前

MySQL本身并不像MariaDB和Percona一樣提供審計功能，但如果我們想對數據庫進行審計，去看是誰把我的數據庫數據給刪了，該怎么辦呢？我們主要利用init-connect參數，讓每個登錄的用戶都記錄到我們的數據庫中，并抓取其connection_id()，再根據binlog就能夠找出誰干了那些破事兒。

MariaDB如何審計，可移步：

http://suifu.blog.51cto.com/9167728/1857594

準備

Part1:創建所需庫

[root@HE3 telegraf]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 859
Server version: 5.7.16-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database auditdb;
Query OK, 1 row affected (0.00 sec)

Part2:創建所需表

[root@HE3 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 266
Server version: 5.7.16-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use auditdb;
Database changed

mysql> CREATE TABLE accesslog (
    -> ID INT (10) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
    -> ConnectionID INT (10) UNSIGNED,
    -> ConnUser VARCHAR (30) NOT NULL DEFAULT '',
    -> MatchUser VARCHAR (30) NOT NULL DEFAULT '',
    -> LoginTime datetime
    -> );
Query OK, 0 rows affected (0.02 sec)

Part3:在my.cnf中添加

init-connect='Insert into auditdb.accesslog(ConnectionID ,ConnUser ,MatchUser ,LoginTime)values(connection_id(),user(),current_user(),now());'

并重啟數據庫

[root@HE3 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS!

測試

Part1:環境

[root@HE3 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 266
Server version: 5.7.16-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use auditdb;

mysql> use helei;
Database changed

mysql> select * from t1;
+----+
| id |
+----+
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
+----+
8 rows in set (0.00 sec)

Part2:用不同用戶登錄操作

[root@HE3 telegraf]# mysql -uhelei -pMANAGER
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 185
Server version: 5.7.16-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use helei;
Database changed

mysql> select * from t1;
+----+
| id |
+----+
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
+----+
8 rows in set (0.00 sec)

mysql> delete from t1 where id = 2;
Query OK, 1 row affected (0.00 sec)

mysql> delete from t1 where id = 4;
Query OK, 1 row affected (0.00 sec)


[root@HE3 telegraf]# mysql -uyuhao -pMANAGER
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 185
Server version: 5.7.16-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use helei;
Database changed

mysql> select * from t1;
+----+
| id |
+----+
|  3 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
+----+
8 rows in set (0.00 sec)

mysql> delete from t1 where id = 3;
Query OK, 1 row affected (0.00 sec)

Part3:查看用戶ID

mysql> select * from accesslog;
+----+--------------+-----------------+-----------+---------------------+
| ID | ConnectionID | ConnUser        | MatchUser | LoginTime           |
+----+--------------+-----------------+-----------+---------------------+
|  1 |           10 | helei@localhost | helei@%   | 2016-12-08 19:07:49 |
|  2 |           19 | helei@localhost | helei@%   | 2016-12-08 19:08:44 |
|  3 |          125 | helei@localhost | helei@%   | 2016-12-08 19:24:46 |
|  4 |          128 | yuhao@localhost | yuhao@%   | 2016-12-08 19:25:01 |
|  5 |          182 | helei@localhost | helei@%   | 2016-12-08 19:33:02 |
|  6 |          185 | yuhao@localhost | yuhao@%   | 2016-12-08 19:33:20 |
+----+--------------+-----------------+-----------+---------------------+
6 rows in set (0.00 sec)

Part4:binlog日志對比

這里可以看到t1表的id=2和id=4列是由thread_id=182用戶刪掉的，也就是helei用戶

#161208 19:33:39 server id 1250  end_log_pos 5275 CRC32 0x2ae798a9      Query   thread_id=182   exec_time=0     error_code=0
SET TIMESTAMP=1481254419/*!*/;
BEGIN
/*!*/;
# at 5275
#161208 19:33:39 server id 1250  end_log_pos 5324 CRC32 0x2cf42817      Rows_query
# delete from t1 where id=2
#161208 19:34:07 server id 1250  end_log_pos 5885 CRC32 0x947106d4      Query   thread_id=182   exec_time=0     error_code=0
SET TIMESTAMP=1481254447/*!*/;
BEGIN
/*!*/;
# at 5885
#161208 19:34:07 server id 1250  end_log_pos 5934 CRC32 0xfe1eb7fc      Rows_query
# delete from t1 where id=4

這里可以看到t1表的id=3列是由thread_id=185用戶刪掉的，也就是yuhao用戶

#161208 19:33:49 server id 1250  end_log_pos 5579 CRC32 0x5f8d9879      Query   thread_id=185   exec_time=0     error_code=0
SET TIMESTAMP=1481254429/*!*/;
BEGIN
/*!*/;
# at 5579
#161208 19:33:49 server id 1250  end_log_pos 5630 CRC32 0x71feeadc      Rows_query
# delete from t1 where id = 3

參考資料：

http://dbspace.blog.51cto.com/6873717/1881053

——總結——

審計多多少少會影響數據庫的性能，能不開盡量不開。另外開啟審計數據庫用戶要實名制或者一對一，以免干了壞事兒的人賴賬~由于筆者的水平有限，編寫時間也很倉促，文中難免會出現一些錯誤或者不準確的地方，不妥之處懇請讀者批評指正。