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

MySQL 日誌審計幫你揪出內個幹壞事的小子
簡介
Part1: 寫在最前
MySQL 本身並不像 MariaDB 和 Percona 一樣提供審計功能，但如果我們想對資料庫進行審計，去看是誰把我的資料庫資料給刪了，該怎麼辦呢？我們主要利用 init-connect 引數，讓每個登入的使用者都記錄到我們的資料庫中，並抓取其 connection_id()，再根據 binlog 就能夠找出誰幹了那些破事兒。
MariaDB 如何審計，可移步：
http://suifu.blog.51cto.com/9167728/1857594

準備
Part1: 建立所需庫

1
2
3
4
5
6
7
8
9
10
11
12
13

[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: 建立所需表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

[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());’

並重啓資料庫

1
2
3

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

測試
Part1: 環境

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

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: 用不同使用者登入操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

[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

1
2
3
4
5
6
7
8
9
10
11
12

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 使用者

1
2
3
4
5
6
7
8
9
10
11
12
13
14

#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 使用者

1
2
3
4
5
6
7

#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