Introduction to MySQL Audit Plugin

Foreword:

The database audit function mainly records the user's various operation behaviors of the database in the audit log, so as to track, query and analyze in the future, so as to realize the monitoring and auditing of user operations. Auditing is a very important job and an important part of the enterprise data security system. Audit logs are also required in the security evaluation. For DBAs, database auditing is also extremely important, especially after a human accident occurs, the audit log is convenient for us to trace responsibilities and find problems.

1. Status of MySQL Community Edition Audit Logs

If you are using the MySQL Community Edition, you will find that MySQL does not officially provide audit logs in the strict sense. Although MySQL provides binlog and general log, although these two have some audit functions, they are generally not regarded as audit logs.

binlog is the binary log file, which records all executed DDL and DML statements in the database (except data query statements select, show, etc.), and records them in the form of events and saves them in binary files. Although the execution record of specific SQL can be found, its function is mainly for master-slave replication and cannot be regarded as an audit log.

The general log is a full log. When enabled, it will record all SQL statements reaching MySQL Server. This log is generally not turned on, because the amount of log will be very large, which will affect the performance of the database, and the general log will record a lot of useless information. If it is used as an audit log, it will be difficult to filter later.

So how should MySQL Community Edition be audited? Looking at the data, we found that the audit function of MySQL can be realized by installing audit plug-ins. Common audit plug-ins include MariaDB Audit Plugin, Percona Audit Log Plugin, and McAfee MySQL Audit Plugin. The audit plug-in that comes with MariaDB is more suitable for MySQL Community Edition. Let's learn how to use the audit plugin to implement the audit function.

2. Audit plugin usage tutorial

The first thing we need to do is to copy the audit plug-in from the MariaDB installation package. It should be noted that the operating system should be selected the same. For example, if your MySQL is installed in the CentOS system, then you need to download the MariaDB installation package of the CentOS system and copy it from it. , Windows system needs to download the audit plug-in corresponding to the system.

The name of the MariaDB audit plug-in is server_audit.so (server_audit.dll under Windows). It should be noted that the audit plug-in has been updated, and the functions of different versions of the audit plug-in are also different. It is recommended to use the plug-in >= 1.4.4. The new version of the plugin can exclude the select statement. The audit events supported by different versions of the audit plug-in are as follows:

The mapping between the audit plugin version and the MariaDB version is as follows:

MySQL 5.7 generally corresponds to MariaDB 10.2 version, we take the CentOS system MySQL 5.7 version as an example to install the audit plug-in. What I choose to download here is the installation package of MariaDB 10.2.38 (audit plugin version 1.4.13), download address: https://downloads.mariadb.com/MariaDB/mariadb-10.2.38/bintar-linux-x86_64/ mariadb-10.2.38-linux-x86_64.tar.gz

After the download is complete, decompress the installation package, then copy the server_audit.so file to the mariadb-10.2.38-linux-x86_64/lib/plugin/ path, and copy it to the MySQL server. The specific steps are as follows:

 # 查看 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

The above are all preparations. For the convenience of all friends, click the link below to download the audit plug-in of version 1.4.13 of the Linux 64-bit system separately:
Cloud disk link: https://pan.baidu.com/s/1HO5sjKb5zpj3CiyRulV5bw?pwd=r85k Extraction code: r85k . Now let's start the formal installation.

 # 进入数据库安装审计插件
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

Through the above steps, we have completed the installation and configuration of the audit plug-in. Referring to the official documentation, let's understand the role of the main configuration parameters:

The above parameters are still easy to understand. Let's carry out the addition, deletion, modification and inspection test to see the specific contents of the audit log:

 # 进行操作后 查看审计日志内容
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]

So far, we have basically completed the initial use of the audit plug-in. From the content of the audit log, we can see that the format of the record is still very clear and detailed. The content of each column is required, and it is easy to find the corresponding operation according to the log. After using it, the author thinks that the server_audit audit plug-in can basically meet the audit needs, but the audit plug-in also has advantages and disadvantages. The advantages and disadvantages are listed as follows:

server_audit audit plugin advantages:

• Rich audit content: including user connections, shutdowns, DML operations, stored procedures, triggers, events, etc.
• Flexible audit strategy: You can customize audit events, such as filtering out select queries, or excluding a user from auditing.
• Flexible and convenient: It is free to use and easy to install, and the auditing function can be turned on and off online.

Disadvantages of the server_audit audit plugin:

• Turning on auditing increases database performance overhead and consumes disk space.
• The log format is not rich enough to customize the output format.

refer to:

• https://www.cnblogs.com/lijiaman/p/14257861.html
• https://www.jianshu.com/p/45b37a73e286
• https://mariadb.com/kb/en/mariadb-audit-plugin-options-and-system-variables/