MySQL審計外掛介紹

wang_KJ發表於2022-07-01
MySql

前言：

資料庫審計功能主要將使用者對資料庫的各類操作行為記錄審計日誌，以便日後進行跟蹤、查詢、分析，以實現對使用者操作的監控和審計。審計是一項非常重要的工作，也是企業資料安全體系的重要組成部分，等保評測中也要求有審計日誌。對於 DBA 而言，資料庫審計也極其重要，特別是發生人為事故後，審計日誌便於我們進行責任追溯，問題查詢。

1. MySQL 社群版審計日誌現狀

如果你用的是 MySQL 社群版的話，你會發現 MySQL 官方並沒有提供嚴格意義上的審計日誌。雖然 MySQL 提供有 binlog 及 general log ，這二者雖然具備部分審計功能，但一般不當做審計日誌來看待。

binlog 即二進位制日誌檔案，它記錄了資料庫所有執行的 DDL 和 DML 語句（除了資料查詢語句select、show等），以事件形式記錄並儲存在二進位制檔案中。雖然能查到具體 SQL 的執行記錄，但其作用主要是主從複製，並不能當做是審計日誌。

general log 是全量日誌，開啟後將會記錄所有到達 MySQL Server 的SQL語句。一般不會開啟此日誌，因為 log 的量會非常龐大，對資料庫效能有影響，並且 general log 會記錄大量無用資訊，當做審計日誌的話，後期篩選有難度。

那麼 MySQL 社群版應該怎樣進行審計呢？查閱資料我們發現通過安裝審計外掛可實現 MySQL 的審計功能，常見的審計外掛有 MariaDB Audit Plugin、Percona Audit Log Plugin、McAfee MySQL Audit Plugin 三種，MariaDB 自帶的審計外掛比較適合用於 MySQL 社群版，下面我們來學習下如何使用審計外掛來實現審計功能。

2. 審計外掛使用教程

首先我們要做的是從 MariaDB 安裝包中拷貝出來審計外掛，需要注意的是作業系統要選擇一致，比如說你的 MySQL 安裝在 CentOS 系統中，那就要下載 CentOS 系統的 MariaDB 安裝包並從中拷貝，Windows 系統則需要下載對應系統的審計外掛。

MariaDB 審計外掛的名稱是 server_audit.so（Windows系統下是 server_audit.dll ），要注意的是，審計外掛一直在更新，不同版本的審計外掛功能也不同，推薦使用 >= 1.4.4 版本的外掛，新版本的外掛可以排除掉 select 語句。不同版本的審計外掛支援的審計事件如下圖：

image.png

審計外掛版本與 MariaDB 版本對應圖如下：

image.png

MySQL 5.7 一般可對應 MariaDB 10.2 版本，我們以 CentOS 系統 MySQL 5.7 版本為例來安裝下審計外掛。我這裡選擇下載的是 MariaDB 10.2.38 版本的安裝包（審計外掛版本 1.4.13），下載地址：https://downloads.mariadb.com/MariaDB/mariadb-10.2.38/bintar-linux-x86_64/mariadb-10.2.38-linux-x86_64.tar.gz

下載完成之後，解壓安裝包，然後到 mariadb-10.2.38-linux-x86_64/lib/plugin/ 路徑下複製出來 server_audit.so 檔案，將其拷貝到 MySQL 伺服器上，具體步驟如下：

# 檢視 MySQL 外掛存放路徑
mysql> show variables like 'plugin_dir';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| plugin_dir    | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+

# 將審計外掛 server_audit.so 存放到該路徑下
[root@localhost plugin]# ls -lh server_audit.so 
-rw-r--r--. 1 root root 191K May  4  2021 server_audit.so

# 更改外掛屬主及許可權
[root@localhost plugin]# chown mysql:mysql server_audit.so
[root@localhost plugin]# chmod 755 server_audit.so
[root@localhost plugin]# ls -lh server_audit.so 
-rwxr-xr-x. 1 mysql mysql 191K May  4  2021 server_audit.so

以上均為準備內容，為方便各位小夥伴，點選下面連結即可單獨下載 Linux 64 位系統的 1.4.13 版本的審計外掛：
雲盤連結: https://pan.baidu.com/s/1HO5sjKb5zpj3CiyRulV5bw?pwd=r85k 提取碼: r85k 。下面我們來開始正式安裝。

# 進入資料庫安裝審計外掛
mysql> INSTALL PLUGIN server_audit SONAME 'server_audit.so';
Query OK, 0 rows affected (0.07 sec)

mysql> show plugins;
+----------------------------+--------+--------------------+-----------------+---------+
| Name                       | Status | Type               | Library         | License |
+----------------------------+--------+--------------------+-----------------+---------+
...
| SERVER_AUDIT               | ACTIVE | AUDIT              | server_audit.so | GPL     |
+----------------------------+--------+--------------------+-----------------+---------+

# 檢視 audit 初始引數配置
mysql> show variables like '%audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           |                       |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_loc_info         |                       |
| server_audit_logging          | OFF                   |
| server_audit_mode             | 1                     |
| server_audit_output_type      | file                  |
| server_audit_query_log_limit  | 1024                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+

# 線上開啟審計
mysql> set global server_audit_logging=on;
Query OK, 0 rows affected (0.00 sec)

mysql> set global server_audit_events='connect,table,query_ddl,query_dcl,query_dml_no_select';
Query OK, 0 rows affected (0.00 sec)

mysql> set global server_audit_file_path ='/data/mysql/logs/server_audit.log';
Query OK, 0 rows affected (0.00 sec)

mysql> set global server_audit_file_rotate_size=104857600;
Query OK, 0 rows affected (0.01 sec)

# [mysqld]下新增以下配置 使得永久生效
server_audit=FORCE_PLUS_PERMANENT
server_audit_logging=ON
server_audit_file_path=/data/mysql/logs/server_audit.log         
server_audit_events=connect,table,query_ddl,query_dcl,query_dml_no_select
server_audit_file_rotate_size=104857600

通過以上步驟，我們已經完成審計外掛的安裝與配置，參照官方文件，我們來了解下主要配置引數的作用：

image.png

以上引數還是很容易理解的，下面我們進行增刪改查測試，看下審計日誌具體記錄的內容：

# 進行操作後 檢視審計日誌內容
20220512 15:17:17,mysqlhost2,test_user,10.30.21.95,118,0,FAILED_CONNECT,,,1045
20220512 15:17:30,mysqlhost2,test_user,10.30.21.95,119,0,FAILED_CONNECT,,,1045
20220512 15:20:26,mysqlhost2,test_user,10.30.21.95,124,0,CONNECT,,,0
20220512 15:20:49,mysqlhost2,test_user,10.30.21.95,124,395,QUERY,,'create database testdb',0
20220512 15:22:06,mysqlhost2,test_user,10.30.21.95,129,419,QUERY,testdb,'CREATE TABLE if not exists `test_tb0` (\r\n  `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT \'自增主鍵\',\r\n  `test_id` int(11) NOT 
NULL ,\r\n  `test_name` varchar(20) DEFAULT NULL,\r\n  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT \'建立時間\',\r\n  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE C
URRENT_TIMESTAMP COMMENT \'修改時間\',\r\n  PRIMARY KEY (`increment_id`)\r\n) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT=\'測試table\'',0
20220512 15:23:09,mysqlhost2,test_user,10.30.21.95,129,426,QUERY,testdb,'insert into test_tb0 (test_id,test_name) values (1001,\'4343df\'),(1002,\'dfd\')',0
20220512 15:23:22,mysqlhost2,test_user,10.30.21.95,129,433,QUERY,testdb,'delete from test_tb0',0
20220512 15:24:14,mysqlhost2,test_user,10.30.21.95,129,448,QUERY,testdb,'create table test_tb0 (id int)',1050
20220512 15:24:25,mysqlhost2,test_user,10.30.21.95,129,452,QUERY,testdb,'drop table test_tb0',0
20220512 15:25:13,mysqlhost2,test_user,10.30.21.95,126,0,DISCONNECT,testdb,,0

# 連線審計主要審計連線資料庫、斷開連線、連線失敗等操作，其日誌格式如下：
[timestamp],[serverhost],[username],[host],[connectionid],0,CONNECT,[database],,0
[timestamp],[serverhost],[username],[host],[connectionid],0,DISCONNECT,,,0
[timestamp],[serverhost],[username],[host],[connectionid],0,FAILED_CONNECT,,,[retcode]

# QUERY審計各種資料庫變更事件，執行失敗也會記錄，其日誌記錄格式如下：
[timestamp],[serverhost],[username],[host],[connectionid],[queryid],QUERY,[database],[object], [retcode]

至此，我們基本上完成審計外掛的初步使用，從審計日誌內容中我們可以看出，記錄的格式還是很清晰詳細的，每列內容都是需要的，根據日誌很容易查到對應的操作。使用下來，筆者覺得 server_audit 審計外掛基本能滿足審計需求，不過審計外掛也是有優缺點的，優劣勢整理如下：

server_audit 審計外掛優勢：

  • 豐富的審計內容：包括使用者連線，關閉，DML操作，儲存過程，觸發器，事件等。
  • 靈活的審計策略：可以自定義審計事件，例如過濾掉select查詢，或者排除審計某個使用者等。
  • 靈活方便：免費使用且安裝方便，可以線上開啟和停用審計功能。

server_audit 審計外掛劣勢：

  • 開啟審計會增加資料庫的效能開銷，並佔用磁碟空間。
  • 日誌格式不夠豐富，不能自定義輸出格式。

參考：

相關文章