技术分享 | MySQL 审计功能实现方案

爱可生开源社区

发布于 2022-09-26 10:07:00

2.4K0

发布于 2022-09-26 10:07:00

文章被收录于专栏：爱可生开源社区

作者：刘聪

爱可生华东交付服务部 DBA 成员，专职 MySQL 故障处理及相关技术支持。座右铭：好好学习，天天向上。

本文来源：原创投稿

* 爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

背景

鉴于目前MySQL审计需求不断深入扩张，以提高数据资产的安全，使得数据库愈来愈规范化管理，数据库实现审计功能成为必要安全环节。

众所周知，MySQL 社区版是不带审计插件的（Audit Plugin），那么该如何实现审计功能呢？我们自然会想到使用binlog日志做审计，因为binlog日志本就记录着数据库上的所有改变。但是无奈，binlog日志并不会记录用户的登录信息，所以无法做到真正意义上的审计。那使用general_log日志呢？

general_log日志可不仅记录着登录信息，甚至错误的登录信息、select等所有对数据库的操作都会记录着。也正是因为general_log日志记录得过于详细，不好做区分。且当并发访问量过大时，极大的占用空间，影响数据库的性能。所以也不会将之作为实现审计功能的工具。

本文将介绍如下三种方式，实现使用审计功能。

相关说明：本文所使用的工具版本

环境：MySQL - 5.7.25（社区版）

工具：

工具	获取源包名
MariaDB审计插件	mariadb-10.2.43-linux-systemd-x86_64
McAfee审计插件	audit-plugin-mysql-5.7-1.1.11-985-linux-x86_64.zip

init_connect 参数

实现步骤

1、创建审计日志表

审计日志表记录内容包括：登录数据库的用户、IP、本次登录时间（审计必要信息）；

CREATE TABLE `test_db`.`audit` (
  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `USER` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '登录用户名',
  `ADRESS` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '登录IP',
  `LOGIN_TIME` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '登录时间',
  PRIMARY KEY (`ID`),
  KEY `IDX_USER` (`USER`),
  KEY `IDX_HOST` (`ADRESS`),
  KEY `IDX_LOGIN_TIME` (`LOGIN_TIME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='普通用户审计日志表';

2、对数据库中所有用户都授予审计日志表的权限

审计原理其实就是用户在登录时刻执行init_connect所指定的内容（对审计日志表进行insert操作），那么必须要求所有的用户都要有此表的insert权限才行。

##为了文章的继续发展，注意这里并没有给普通用户'user_3'@'%'授权哦
mysql> select user,host from mysql.user;
+---------------+--------------+
| user          | host         |
+---------------+--------------+
| user_1        | %            |
| user_3        | %            |
| user_2        | 10.186.61.17 |
| mysql.session | localhost    |
| mysql.sys     | localhost    |
| root          | localhost    |
+---------------+--------------+
6 rows in set (0.00 sec)


mysql> grant insert on test_db.audit to 'user_1'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> grant insert on test_db.audit to 'user_2'@'10.186.61.17';
Query OK, 0 rows affected (0.01 sec)

3、动态设置，开启审计日志表

##动态设置init_connect参数
mysql> set global init_connect="insert into test_db.audit(USER,ADRESS) values(current_user(),substring_index(user(),'@',-1));";
Query OK, 0 rows affected (0.00 sec)

##补充说明current_user()可以查到当前登录的用户信息，substring_index()是一个截取函数，例如使用普通用户'user_2'@'10.186.61.17'
mysql> select current_user();
+---------------------+
| current_user()      |
+---------------------+
| user_2@10.186.61.17 |
+---------------------+
1 row in set (0.00 sec)
mysql> select substring_index(user(),'@',-1);
+--------------------------------+
| substring_index(user(),'@',-1) |
+--------------------------------+
| 10.186.61.17                   |
+--------------------------------+
1 row in set (0.00 sec)

为了永久生效，必须还要在配置文件中添加如下内容：

[mysqld]
init_connect="insert into test_db.audit(USER,ADRESS) values(current_user(),substring_index(user(),'@',-1));";

4、审计日志表结果说明

对于普通用户，需要提前被授予一定权限，否则不会被记录到审计日志表中，并且会导致连接失败（'user_3'@'%'用户因为没有审计表的insert权限，所以没有被记录）。
对于普通用户，如果init_connect的内容有语法错误，依然会直接导致连接失败（无法执行init_connect的内容）。
对于具有super权限的用户，在登录时并不会执行init_connec的内容（所以审计日志表里也没有super用户的记录）。
对于密码过期的普通用户，登录数据库会直接连接失败，且不会记录也不会报错（无法执行init_connect的内容）。

##模拟对审计表有insert权限的普通用户登录数据库
[root@10-186-61-17 ~]# /data/mysql/3306/base/bin/mysql -uuser_2 -p -P6666 -h10.186.61.17
[root@10-186-61-17 ~]# /data/mysql/3306/base/bin/mysql -uuser_1 -p -P6666 -h10.186.61.17
[root@10-186-61-55 ~]# /data/mysql/3306/base/bin/mysql -uuser_1 -p -P6666 -h10.186.61.17

##模拟对审计表无insert权限的普通用户登录数据库
[root@10-186-61-17 ~]# /data/mysql/3306/base/bin/mysql -uuser_3 -p -P6666 -h10.186.61.17
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.25-log

Copyright (c) 2000, 2019, 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> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query


##使用root用户登录查看审计日志表记录结果展示
[root@10-186-61-17 ~]# /data/mysql/3306/base/bin/mysql -uroot -p -S /data/mysql/6666/data/mysqld.sock
Enter password:
……
mysql> select * from test_db.audit;
+----+---------------------+--------------+---------------------+
| ID | USER                | ADRESS       | LOGIN_TIME          |
+----+---------------------+--------------+---------------------+
|  1 | user_2@10.186.61.17 | 10.186.61.17 | 2022-05-26 14:17:35 |
|  2 | user_1@%            | 10.186.61.17 | 2022-05-26 14:18:12 |
|  3 | user_1@%            | 10.186.61.55 | 2022-05-26 14:19:34 |
+----+---------------------+--------------+---------------------+
3 rows in set (0.02 sec)

小结

优点

实现原理和操作步骤简单；
几乎不影响MySQL性能。

缺点

记录的内容简略（仅记录登录时刻执行init_connect所定义插入的值）；
只记录登入行为，不记录登录数据库后的行为；
不会记录有super权限的用户的登录行为；
密码过期的用户登录数据库，会直接连接失败，且不会记录也不会报错。

MariaDB审计插件

实现步骤

1、获取MariaDB的审计插件—— server_audit.so

##下载MariaDB Server安装包
##下载地址：https://mariadb.org/download/?t=mariadb&p=mariadb&r=10.2.43&os=Linux&cpu=x86_64&i=systemd&m=bkns
##本地解压后，在解压目录下找到审计插件`server_audit.so`
./mariadb/mariadb-10.2.43-linux-systemd-x86_64/lib/plugin/server_audit.so

2、将审计插件 server_audit.so复制到MySQL的plugin目录下，并授权

cp ./mariadb/mariadb-10.2.43-linux-systemd-x86_64/lib/plugin/server_audit.so /data/mysql/3306/base/lib/plugin/
chown -R mysql:mysql server_audit.so
chmod 755 server_audit.so

3、进入MySQL客户端，安装插件

##install审计插件
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 |
+----------------------------+----------+--------------------+--------------------+---------+
 ……
| ngram                      | ACTIVE   | FTPARSER           | NULL               | GPL     |
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
| rpl_semi_sync_slave        | ACTIVE   | REPLICATION        | semisync_slave.so  | GPL     |
| SERVER_AUDIT               | ACTIVE   | AUDIT              | server_audit.so    | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+
47 rows in set (0.00 sec)

4、配置说明

当安装完审计插件之后，MySQL中将有一些新的全局变量出现。这些变量就是用于配置审计日志的，例如配置审计日志的位置、日志大小、记录的信息格式等。查看相关变量：

mysql> show variables like '%server_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              |
+-------------------------------+-----------------------+
16 rows in set (0.01 sec)

部分变量说明：

变量名	获取源包名
server_audit_logging	控制审计功能的开关；
server_audit_events	指定记录的事件类型；如果设置指定值，则审核日志只记录在设定值范围内的事件类型；如果未设置，则每个事件类型都会记录到审核日志中（默认值选项）。
server_audit_file_path	当server_audit_output_type=file时，设置日志文件的路径和文件名；默认文件路径为datadir，默认文件名为server_audit.log 。
server_audit_file_rotate_now	手工触发审计日志轮换；即：设置为 ON 或 1 来强制日志文件轮换。
server_audit_file_rotate_size	日志大小限制，达到了阈值，审计日志会自动轮换。
server_audit_file_rotations	指定轮换后所保留的日志文件个数；如果设置为 0，则日志永远不会轮换；默认值为 9，即保存最多9个审计日志。
server_audit_incl_users	指定用户列表，在内的用户，将被记录；注：CONNECT 记录不受此变量的影响。
server_audit_excl_users	指定用户列表，在内的用户，不会被记录；如果用户在server_audit_incl_users中也指定了，用户活动仍会被记录；注：CONNECT 记录不受此变量的影响。
server_audit_query_log_limit	限制记录中查询字符串的长度。

配置示例：

##开启审计功能
mysql> SET GLOBAL server_audit_logging=ON;

##指定记录内容‘connect,query’，记录用户的连接和查询语句
SET global server_audit_events='connect,query';
##重命名审计日志
mysql> set global server_audit_file_path='mysql_3306_audit.log';

为了永久生效，必须还要在配置文件中添加如下内容：

##这里仅定义了两项变量值供参考
[server]
server_audit_logging=ON
server_audit_events=connect,query
…

5、审计日志结果说明

##模拟相关操作（查询结果篇幅过长，省略部分内容）
mysql> show databases;
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
mysql> select count(*) from sbtest1;
mysql> select * from sbtest1 limit 3;
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k      | c                                                                                                                       | pad                                                         |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  1 | 498670 | 31451373586-15688153734-79729593694-96509299839-83724898275-86711833539-78981337422-35049690573-51724173961-87474696253 | 98996621624-36689827414-04092488557-09587706818-65008859162 |
|  2 | 497778 | 21472970079-70972780322-70018558993-71769650003-09270326047-32417012031-10768856803-14235120402-93989080412-18690312264 | 04776826683-45880822084-77922711547-29057964468-76514263618 |
|  3 | 498956 | 49376827441-24903985029-56844662308-79012577859-40518387141-60588419212-24399130405-42612257832-29494881732-71506024440 | 26843035807-96849339132-53943793991-69741192222-48634174017 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> exit
Bye

[root@10-186-65-155 plugin]# /data/mysql/3306/base/bin/mysql -utest -p -S /data/mysql/3306/data/mysqld.sock
Enter password:
mysql> show databases;
mysql> use occdb
mysql> show tables;
mysql> create table abc like occ_application_config;
Query OK, 0 rows affected (10.09 sec)
mysql> exit
Bye

##查看datadir下审计日志文件
[root@10-186-65-155 data]# cd /data/mysql/3306/data/ && ll -h | grep 'audit.log'
-rw-r----- 1 mysql mysql  711 Apr 19 15:30 mysql_3306_audit.log
-rw-r----- 1 mysql mysql  329 Apr 19 15:26 server_audit.log

##查看审计日志`mysql_3306_audit.log`记录的内容
20220419 15:26:04,10-186-65-155,root,localhost,6,6,QUERY,,'set global server_audit_file_path=\'mysql_3306_audit.log\'',0
20220419 15:29:30,10-186-65-155,root,localhost,6,7,QUERY,,'show databases',0
20220419 15:29:38,10-186-65-155,root,localhost,6,8,QUERY,,'SELECT DATABASE()',0
20220419 15:29:38,10-186-65-155,root,localhost,6,10,QUERY,test,'show databases',0
20220419 15:29:38,10-186-65-155,root,localhost,6,11,QUERY,test,'show tables',0
20220419 15:29:43,10-186-65-155,root,localhost,6,17,QUERY,test,'show tables',0
20220419 15:30:04,10-186-65-155,root,localhost,6,18,QUERY,test,'select count(*) from sbtest1',0
20220419 15:30:25,10-186-65-155,root,localhost,6,19,QUERY,test,'select * from sbtest1 limit 3',0
20220419 15:36:14,10-186-65-155,root,localhost,6,0,DISCONNECT,test,,0
20220419 15:36:24,10-186-65-155,test,localhost,7,0,CONNECT,,,0
20220419 15:36:24,10-186-65-155,test,localhost,7,21,QUERY,,'select @@version_comment limit 1',0
20220419 15:36:28,10-186-65-155,test,localhost,7,22,QUERY,,'show databases',0
20220419 15:36:36,10-186-65-155,test,localhost,7,23,QUERY,,'SELECT DATABASE()',0
20220419 15:36:36,10-186-65-155,test,localhost,7,25,QUERY,occdb,'show databases',0
20220419 15:36:36,10-186-65-155,test,localhost,7,26,QUERY,occdb,'show tables',0
20220419 15:36:39,10-186-65-155,test,localhost,7,28,QUERY,occdb,'show tables',0
20220419 15:36:55,10-186-65-155,test,localhost,7,29,QUERY,occdb,'create table abc like occ_application_config',0
20220419 15:37:11,10-186-65-155,test,localhost,7,0,DISCONNECT,occdb,,0

补充：下发的错误语句，或者连接失败等语句，审计日志依然会记录在内。

##模拟错误语句和连接失败情景
mysql> show creates table abc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'creates table abc' at line 1
mysql> exit
Bye
[root@10-186-65-155 plugin]# /data/mysql/3306/base/bin/mysql -utest -p -S /data/mysql/3306/data/mysqld.sock
Enter password:
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: NO)

##查看审计日志记录的相关内容
……
20220419 15:42:16,10-186-65-155,test,localhost,9,45,QUERY,occdb,'show creates table abc',1064
20220419 15:42:34,10-186-65-155,test,localhost,9,0,DISCONNECT,occdb,,0
20220419 15:42:48,10-186-65-155,test,localhost,10,0,FAILED_CONNECT,,,1045

根据以上记录的内容可以总结审计日志的记录格式：

[timestamp],[serverhost],[username],[host],[connectionid],[queryid],[operation],[database],[object],[retcode]

小结

优点

使用xml格式和内容明确且详细；
审计粒度细：例如可通过server_audit_events和server_audit_incl_users等参数指定需要审计的内容；
审计日志维护管理方便：自带审计日志切割轮换参数；
审计日志会记录下发的错误语句、连接失败信息等。

缺点

对MySQL性能有一定影响。

McAfee审计插件

相关说明

所属McAfee公司McAfee审计插件，专门为 MySQL 提供审计功能。其设计重点是安全和审计要求。可以独立的作为审计插件，实现MySQL的审计功能，实现步骤与MariaDB 审计插件类似。

实现步骤

1、获取审计插件——libaudit_plugin.so

##下载插件
##下载地址：https://github.com/mcafee/mysql-audit/releases
##本地解压后，在解压目录下找到审计插件libaudit_plugin.so
./audit-plugin-mysql-5.7-1.1.11-985/lib/libaudit_plugin.so

2、将审计插件server_audit.so复制到MySQL的plugin目录下，并授权

cp ./audit-plugin-mysql-5.7-1.1.11-985/lib/libaudit_plugin.so /data/mysql/6666/base/lib/plugin/
chown -R mysql:mysql libaudit_plugin.so
chmod 755 libaudit_plugin.so

3、进入MySQL客户端，安装插件

mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
Query OK, 0 rows affected (1.08 sec)
 
mysql> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+
……
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
| rpl_semi_sync_slave        | ACTIVE   | REPLICATION        | semisync_slave.so  | GPL     |
| AUDIT                      | ACTIVE   | AUDIT              | libaudit_plugin.so | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+
47 rows in set (0.00 sec)

4、配置说明

同Mariadb的审计插件类似，当安装完审计插件之后，MySQL中将有一些新的全局变量，用于配置审计日志相关属性。

mysql> show variables like '%audit%';
+---------------------------------+-----------------------------------------------------------------------------------------------                  
| Variable_name                   | Value                                                                                                           
+---------------------------------+-----------------------------------------------------------------------------------------------                  
| audit_before_after              | after                                                                                                           
| audit_checksum                  |                                                                                                                 
| audit_client_capabilities       | OFF                                                                                                             
| audit_delay_cmds                |                                                                                                                 
| audit_delay_ms                  | 0                                                                                                               
| audit_force_record_logins       | OFF                                                                                                             
| audit_header_msg                | ON                                                                                                              
| audit_json_file                 | OFF                                                                                                             
| audit_json_file_bufsize         | 1                                                                                                               
| audit_json_file_flush           | OFF                                                                                                             
| audit_json_file_retry           | 60                                                                                                              
| audit_json_file_sync            | 0                                                                                                               
| audit_json_log_file             | mysql-audit.json                                                                                                
| audit_json_socket               | OFF                                                                                                             
| audit_json_socket_name          | /var/run/db-audit/mysql.audit__data_mysql_6666_data_6666                                                        
| audit_json_socket_retry         | 10                                                                                                              
| audit_json_socket_write_timeout | 1000                                                                                                            
| audit_offsets                   |                                                                                                                 
| audit_offsets_by_version        | ON                                                                                                              
| audit_password_masking_cmds     | CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER,UPDATE                        
| audit_password_masking_regex    | 
| audit_record_cmds               |                                                                                                                 
| audit_record_objs               |                                                                                                                 
| audit_sess_connect_attrs        | ON                                                                                                              
| audit_socket_creds              | ON                                                                                                              
| audit_uninstall_plugin          | OFF                                                                                                             
| audit_validate_checksum         | ON                                                                                                              
| audit_validate_offsets_extended | ON                                                                                                              
| audit_whitelist_cmds            | BEGIN,COMMIT,PING                                                                                               
| audit_whitelist_users           |                                                                                                                 
+---------------------------------+-----------------------------------------------------------------------------------------------                  
30 rows in set (0.01 sec)

部分变量说明：

变量名	获取源包名
audit_json_file	是否开启审计 ON/OFF；默认值：OFF。
audit_json_log_file	设置日志文件的路径和文件名；默认值：mysql-audit.json，默认路径：datadir。
audit_record_cmds	设置要记录的语句类型，例如：insert,update,delete ；默认记录所有的命令。
audit_record_objs	设置要记录到审计跟踪的对象（表），例如：database.table，可以使用通配符。
audit_whitelist_cmds	设置不需要记录的语句类型。
audit_whitelist_users	设置不需要记录的用户。
audit_before_after	控制是在执行当前 SQL 语句之前还是之后写入其日志记录，或两者兼而有之。可能的值为'before'、'after'或'both'，默认为'after'。
audit_json_file_flush	手工进行轮换日志。
audit_json_file_sync	控制日志刷写周期json，如果此变量的值为N（N≠0），则审计日志将在每N次写入后同步到磁盘。默认值：0 。

配置示例：

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

mysql> set global audit_json_log_file='mysql_6666_audit.log';
Query OK, 0 rows affected (0.00 sec)

为了永久生效，必须还要在配置文件中添加如下内容：

##这里仅定义了两项变量值供参考
[mysqld]
audit_json_file=on
audit_record_cmds='query_type'  ##自定义需要记录的语句类型
……

5、审计日志结果说明

##模拟相关操作（查询结果篇幅过长，省略部分内容）
[root@10-186-65-155 plugin]# /data/mysql/6666/base/bin/mysql -uroot -p -S /data/mysql/6666/data/mysqld.sock
Enter password:
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
mysql> select count(*) from mysql.user;
mysql> create database test;
mysql> use test;
Database changed
mysql> create table aaa(id int,name varchar(20),primary key(id));
mysql> insert into aaa values (1,'zhangsan'),(2,'lisi');
mysql> exit
Bye
[root@10-186-65-155 plugin]# /data/mysql/6666/base/bin/mysql -utest -p -S /data/mysql/6666/data/mysqld.sock
Enter password:
mysql> use test;
mysql> select * from aaa;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+

 ##查看datadir下的审计日志记录内容
 {"msg-type":"activity","date":"1650361253128","thread-id":"11","query-id":"0","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1650361253129","thread-id":"11","query-id":"145","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"1","status":"0","cmd":"select","query":"select @@version_comment limit 1"}
{"msg-type":"activity","date":"1650361255866","thread-id":"11","query-id":"146","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"4","status":"0","cmd":"show_databases","objects":[{"db":"information_schema","name":"/data/mysql/6666/tmp/#sql_1b0f_0","obj_type":"TABLE"}],"query":"show databases"}
{"msg-type":"activity","date":"1650361283561","thread-id":"11","query-id":"147","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"1","status":"0","cmd":"select","objects":[{"db":"mysql","name":"user","obj_type":"TABLE"}],"query":"select count(*) from mysql.user"}
{"msg-type":"activity","date":"1650361313234","thread-id":"11","query-id":"148","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","status":"0","cmd":"create_db","query":"create database test"}
{"msg-type":"activity","date":"1650361318055","thread-id":"11","query-id":"149","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"1","status":"0","cmd":"select","query":"SELECT DATABASE()"}
{"msg-type":"activity","date":"1650361318055","thread-id":"11","query-id":"150","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"1","status":"0","cmd":"Init DB","objects":[{"db":"test","obj_type":"DATABASE"}],"query":"Init DB"}
{"msg-type":"activity","date":"1650361318057","thread-id":"11","query-id":"151","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"5","status":"0","cmd":"show_databases","objects":[{"db":"information_schema","name":"/data/mysql/6666/tmp/#sql_1b0f_0","obj_type":"TABLE"}],"query":"show databases"}
{"msg-type":"activity","date":"1650361318057","thread-id":"11","query-id":"152","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","status":"0","cmd":"show_tables","objects":[{"db":"information_schema","name":"/data/mysql/6666/tmp/#sql_1b0f_0","obj_type":"TABLE"}],"query":"show tables"}
{"msg-type":"activity","date":"1650361361491","thread-id":"11","query-id":"153","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","status":"0","cmd":"create_table","objects":[{"db":"test","name":"aaa","obj_type":"TABLE"}],"query":"create table aaa(id int,name varchar(20),primary key(id))"}
{"msg-type":"activity","date":"1650361424702","thread-id":"11","query-id":"154","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"2","status":"0","cmd":"insert","objects":[{"db":"test","name":"aaa","obj_type":"TABLE"}],"query":"insert into aaa values (1,'zhangsan'),(2,'lisi')"}
{"msg-type":"activity","date":"1650361432156","thread-id":"11","query-id":"155","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","cmd":"Quit","query":"Quit"}
{"msg-type":"activity","date":"1650361439497","thread-id":"12","query-id":"0","user":"test","priv_user":"test","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5162","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5162","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1650361439498","thread-id":"12","query-id":"156","user":"test","priv_user":"test","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5162","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5162","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"1","status":"0","cmd":"select","query":"select @@version_comment limit 1"}
{"msg-type":"activity","date":"1650361443135","thread-id":"12","query-id":"157","user":"test","priv_user":"test","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5162","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5162","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"1","status":"0","cmd":"select","query":"SELECT DATABASE()"}
{"msg-type":"activity","date":"1650361443136","thread-id":"12","query-id":"158","user":"test","priv_user":"test","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5162","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5162","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"1","status":"0","cmd":"Init DB","objects":[{"db":"test","obj_type":"DATABASE"}],"query":"Init DB"}
{"msg-type":"activity","date":"1650361443138","thread-id":"12","query-id":"159","user":"test","priv_user":"test","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5162","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5162","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"5","status":"0","cmd":"show_databases","objects":[{"db":"information_schema","name":"/data/mysql/6666/tmp/#sql_1b0f_0","obj_type":"TABLE"}],"query":"show databases"}
{"msg-type":"activity","date":"1650361443139","thread-id":"12","query-id":"160","user":"test","priv_user":"test","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5162","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5162","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"1","status":"0","cmd":"show_tables","objects":[{"db":"information_schema","name":"/data/mysql/6666/tmp/#sql_1b0f_0","obj_type":"TABLE"}],"query":"show tables"}
{"msg-type":"activity","date":"1650361454500","thread-id":"12","query-id":"162","user":"test","priv_user":"test","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5162","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5162","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"2","status":"0","cmd":"select","objects":[{"db":"test","name":"aaa","obj_type":"TABLE"}],"query":"select * from aaa"}

根据以上的记录内容可总结审计日志的记录格式：

审计日志使用的是json格式记录，如下为一条记录内容
{
	"msg-type": "activity",
	"date": "1650361454500",
	"thread-id": "12",
	"query-id": "162",
	"user": "test",
	"priv_user": "test",
	"ip": "",
	"host": "localhost",
	"_os": "linux-glibc2.12",
	"_client_name": "libmysql",
	"_pid": "5162",
	"_client_version": "5.7.25",
	"_platform": "x86_64",
	"program_name": "mysql",
	"pid": "5162",
	"os_user": "root",
	"appname": "/data/mysql/6666/base/bin/mysql",
	"rows": "2",
	"status": "0",
	"cmd": "select",
	"objects": [{
		"db": "test",
		"name": "aaa",
		"obj_type": "TABLE"
	}],
	"query": "select * from aaa"
}

小结

优点：

使用json格式和内容明确且详细；
审计粒度细：例如可通过audit_record_cmds和audit_record_objs等指定需要审计的内容；
记录连接失败的语句（audit_force_record_logins参数值为on条件下）；
审计日志刷写策略可控，可通过audit_json_file_sync参数控制。

缺点：

对MySQL性能有一定影响；
不记录下发错误的语句；
审计日志维护管理不便，无自带审计日志切割轮换参数。

补充说明：

Percona 公司开发的审计插件 audit_log.so（可以在Percona Server包中获取），目前无法直接install到社区版MySQL中使用。

mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';
ERROR 1126 (HY000): Can't open shared library '/data/mysql/7777/base/lib/plugin/audit_log.so' (errno: 2 /data/mysql/7777/base/lib/plugin/audit_log.so: undefined symbol: plugin_thdvar_safe_update)

结束语

本文介绍了三种实现审计功能的方案，各个方案都有自己的优缺点。如果仅仅记录普通用户的登录信息就能够满足审计需求，那就选择第一种方案（init_connect参数）再好不过了。如果想要实现更全面的审计功能，那么第二种或者第三种方案会更加合适。总之，根据审计需求来选择自己认为最合适的方案吧。另外，在实际使用之前，请务必做好充分的测试工作！

本文关键字：#审计#

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2022-06-07，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server