MariaDB Audit Plugin审计插件安装及使用

IT那活儿 2023-05-15

1126

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

背景

在MySQL日常运维过程中，经常会收到安全审计需求，这时我们需要借助审计插件来协助我们对数据库的连接使用进行监控审计。由于MySQL社区版中没有自带的安全审计工具，所以就需要安装第三方开源的审计工具来使用。

常见的审计插件主要有以下几种：

1. MariaDB Audit Plugin

1）MariaDB官方地址

https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin，支持日志分割轮询，目前官方并没有提供单独MariaDB Audit Plugin下载地址需要下载完整的数据库安装文件再从plugin目录下拷贝server_audit.so文件出来。

2）MariaDB版本与audit plugin版本的对应关系

2. MySQL Enterprise Audit Plugin

Oracle MySQL企业版中审计插件，社区版暂时无法使用。

3. Percona Audit Log Plugin

仅适用于Percona Server，MySQL社区版无法适配。

4. McAfee MySQL Audit Plugin

开源版社区插件，下载地址https://github.com/mcafee/mysql-audit/releases，不支持审计日志自动切割，日志格式为JSON格式，时间格式需要转换。

本文以MariaDB Audit Plugin为例进行说明。

安装过程

本次使用MySQL社区版5.7.28版本数据库，MariaDB Audit Plugin 1.4.4版本。

首先下载mariadb-5.5.68-linux-x86_64.tar.gz解压获取server_audit.so插件，将server_audit.so传至plugin_dir下，修改权限为755，用户属组mysql。如不知道路径可执行以下语句查看：

执行插件安装语句：

mysql> INSTALL PLUGIN server_audit SONAME 'server_audit.so';

安装完成后查看MariaDB Audit Plugin的相关参数。用命令设置或在参数文件my.cnf中配置：

参数详解

server_audit_events

Description: If set, then this restricts audit logging to certain event types. If not set, then every event type is logged to the audit log. For example: SET GLOBAL server_audit_events='connect, query'
Commandline: --server-audit-events=value
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: Empty string
Valid Values:

CONNECT, QUERY, TABLE (MariaDB Audit Plugin < 1.2.0)
CONNECT, QUERY, TABLE, QUERY_DDL, QUERY_DML (MariaDB Audit Plugin >= 1.2.0)
CONNECT, QUERY, TABLE, QUERY_DDL, QUERY_DML, QUERY_DCL (MariaDB Audit Plugin >=1.3.0)
CONNECT, QUERY, TABLE, QUERY_DDL, QUERY_DML, QUERY_DCL, QUERY_DML_NO_SELECT (MariaDB Audit Plugin >= 1.4.4)
See MariaDB Audit Plugin - Versions to determine which MariaDB releases contain each MariaDB Audit Plugin versions.

server_audit_excl_users

Description: If not empty, it contains the list of users whose activity will NOT be logged. For example: SET GLOBAL server_audit_excl_users='user_foo, user_bar'. CONNECT records aren't affected by this variable - they are always logged. The user is still logged if it's specified in server_audit_incl_users.
Commandline: --server-audit-excl-users=value
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: Empty string
Size limit: 1024 characters

server_audit_file_path

Description: When server_audit_output_type=file, sets the path and the filename to the log file. If the specified path exists as a directory, then the log will be created inside that directory with the name 'server_audit.log'. Otherwise the value is treated as a filename. The default value is 'server_audit.log', which means this file will be created in the database directory.
Commandline: --server-audit-file-path=value
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: server_audit.log

server_audit_file_rotate_now

Description: When server_audit_output_type=file, the user can force the log file rotation by setting this variable to ON or 1.
Commandline: --server-audit-rotate-now[={0|1}]
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF

server_audit_file_rotate_size

Description: When server_audit_output_type=file, it limits the size of the log file to the given amount of bytes. Reaching that limit turns on the rotation - the current log file is renamed as 'file_path.1'. The empty log file is created as 'file_path' to log into it. The default value is 1000000.
Commandline: --server-audit-rotate-size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 1000000
Range: 100 to 9223372036854775807

server_audit_file_rotations

Description: When server_audit_output_type=file', this specifies the number of rotations to save. If set to 0 then the log never rotates. The default value is 9.
Commandline: --server-audit-rotations=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 9
Range: 0 to 999

server_audit_incl_users

Description: If not empty, it contains a comma-delimited list of users whose activity will be logged. For example: SET GLOBAL server_audit_incl_users='user_foo, user_bar'. CONNECT records aren't affected by this variable - they are always logged. This setting has higher priority than server_audit_excl_users. So if the same user is specified both in incl_ and excl_ lists, they will still be logged.
Commandline: --server-audit-incl-users=value
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: Empty string
Size limit: 1024 characters

server_audit_loc_info

Description: Used by plugin internals. It has no useful meaning to users.

In earlier versions, users see it as a read-only variable.
In later versions, it is hidden from the user.

Commandline: N/A
Scope: Global
Dynamic: No
Data Type: string
Default Value: Empty string
Introduced: MariaDB 10.1.12, MariaDB 10.0.24, MariaDB 5.5.48
Hidden: MariaDB 10.1.18, MariaDB 10.0.28, MariaDB 5.5.53

server_audit_logging

Description: Enables/disables the logging. Expected values are ON/OFF. For example: SET GLOBAL server_audit_logging=on If the server_audit_output_type is FILE, this will actually create/open the logfile so the server_audit_file_path should be properly specified beforehand. Same about the SYSLOG-related parameters. The logging is turned off by default.
Commandline: --server-audit-logging[={0|1}]
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF

server_audit_mode

Description: This variable doesn't have any distinctive meaning for a user. Its value mostly reflects the server version with which the plugin was started and is intended to be used by developers for testing.
Commandline: --server-audit-mode[=#]
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 0
Range: 0 to 1

server_audit_output_type

Description: Specifies the desired output type. Can be SYSLOG, FILE or null as no output. For example: SET GLOBAL server_audit_output_type=file file: log records will be saved into the rotating log file. The name of the file set by server_audit_file_path variable. syslog: log records will be sent to the local syslogd daemon with the standard <syslog.h> API. The default value is 'file'.
Commandline: --server-audit-output-type=value
Scope: Global
Dynamic: Yes
Data Type: enum
Default Value: file
Valid Values: SYSLOG, FILE

server_audit_query_log_limit

Description: Limit on the length of the query string in a record.
Commandline: --server-audit-query-log-limit=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 1024
Range: 0 to 2147483647

server_audit_syslog_facility

Description: SYSLOG-mode variable. It defines the 'facility' of the records that will be sent to the syslog. Later the log can be filtered by this parameter.
Commandline: --server-audit-syslog-facility=value
Scope: Global
Dynamic: Yes
Data Type: enum
Default Value: LOG_USER
Valid Values: LOG_USER, LOG_MAIL, LOG_DAEMON, LOG_AUTH, LOG_SYSLOG, LOG_LPR, LOG_NEWS, LOG_UUCP, LOG_CRON, LOG_AUTHPRIV, LOG_FTP, and LOG_LOCAL0–LOG_LOCAL7.

server_audit_syslog_ident

Description: SYSLOG-mode variable. String value for the 'ident' part of each syslog record. Default value is 'mysql-server_auditing'. New value becomes effective only after restarting the logging.
Commandline: --server-audit-syslog-ident=value
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: mysql-server_auditing

server_audit_syslog_info

Description: SYSLOG-mode variable. The 'info' string to be added to the syslog records. Can be changed any time.
Commandline: --server-audit-syslog-info=value
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: Empty string

server_audit_syslog_priority

Description: SYSLOG-mode variable. Defines the priority of the log records for the syslogd.
Commandline: --server-audit-syslog-priority=value
Scope: Global
Dynamic: Yes
Data Type: enum
Default Value: LOG_INFO
Valid Values:LOG_EMERG, LOG_ALERT, LOG_CRIT, LOG_ERR, LOG_WARNING, LOG_NOTICE, LOG_INFO, LOG_DEBUG

Options

server_audit

Description: Controls how the server should treat the plugin when the server starts up.

OFF - Disables the plugin without removing it from the mysql.plugins table.
ON - Enables the plugin. If the plugin cannot be initialized, then the server will still continue starting up, but the plugin will be disabled.
FORCE - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error.
FORCE_PLUS_PERMANENT - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error. In addition, the plugin cannot be uninstalled with UNINSTALL SONAME or UNINSTALL PLUGIN while the server is running.
Valid values are:
See MariaDB Audit Plugin - Installation: Prohibiting Uninstallation for more information on one use case.
See Plugin Overview: Configuring Plugin Activation at Server Startup for more information.

Commandline: --server-audit=val
Data Type: enumerated
Default Value: ON
Valid Values: OFF, ON, FORCE, FORCE_PLUS_PERMANENT

https://mariadb.com/kb/en/mariadb-audit-plugin-options-and-system-variables/

测试：

卸载：

mysql> UNINSTALL PLUGIN server_audit;
mysql> show variables like '%audit%';
Empty set (0.00 sec)

防止 server_audit 插件被卸载，需要在配置文件中添加：

[mysqld]
server_audit=FORCE_PLUS_PERMANENT

重启MySQL生效：

mysql> UNINSTALL PLUGIN server_audit;
ERROR 1702 (HY000): Plugin 'server_audit' is force_plus_permanent and can not be unloaded

END

本文作者：四条眉毛(上海新炬中北团队）

本文来源：“IT那活儿”公众号

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MariaDB Audit Plugin审计插件安装及使用

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

https://mariadb.com/kb/en/mariadb-audit-plugin-options-and-system-variables/

测试：

卸载：

本文作者：四条眉毛(上海新炬中北团队）

本文来源：“IT那活儿”公众号

评论