MySQL审计正确使用姿势

2020-02-25 17:34:24

前言

数据库审计功能主要将用户对数据库的各类操作行为记录审计日志，以便日后进行跟踪、查询、分析，以实现对用户操作的监控和审计。审计是一项非常重要的工作，也是企业数据安全体系的重要组成部分。

MySQL 5.5企业版增加审计功能，但是需要付费。MySQL社区版没有审计功能，除了商业版的审计插件外，常见的还有三类审计插件Percona Audit Log Plugin、MariaDB Audit Plugin、McAfee MySQL Audit Plugin。这几个插件功能上大同小异，只是展示的内容和格式略有不同。InnoSQL 5.7版本基于Percona 5.7.26-29 审计插件Audit Log Plugin 实现了审计功能，并对性能进行了优化，修改了原生版本的一些日志记录的问题，同时也增加了审计日志的字段丰富记录内容。

下面对InnoSQL审计插件的功能及使用方法进行介绍。

开启审计

安装插件

配置文件加载插件

我们可以在配置文件中加载插件开启审计。

[mysqld]

plugin-load=audit_log.so

如果在配置文件中加载了audit_log.so可以直接配置审计相关的参数

audit_log_timestamps=SYSTEM

否则，可以通过loose的方式配置插件参数，INSTALL插件后生效。这也是MySQL插件参数的通用配置方法。

loose-audit_log_timestamps=SYSTEM

命令行加载插件

审计功能的开启还有另外一种方式，就是在命令行中安装审计插件。

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

启停审计功能

支持在线进行审计功能的启停操作。

启用审计：

mysql> set global audit_log_policy= ALL/ LOGINS/ QUERIES;

停用审计：

mysql> set global audit_log_policy=NONE;

卸载插件

卸载插件命令

mysql> UNINSTALL PLUGIN audit_log;

如果此时仍有业务记录审计日志，那么将无法在线卸载，需重启生效。

如果不需要审计了，可以使用set global audit_log_policy=NONE 在线停用审计功能，停用之后跟未安装插件的性能一致。

参数介绍

audit_log_buffer_size

variable
audit_log_buffer_size
Command Line
YES
Scope
Global
Dynamic
No
Variable Type
Numeric
Default Value
1MB

审计日志缓冲区大小，当audit_log_strategy生效（audit_log_handler 为 'FILE'）

且设置为ASYNCHRONOUS、PERFORMANCE时生效。audit_log_buffer_size 的大小设置需要是4096的正整数倍。

audit_log_include_accounts / audit_log_exclude_accounts

variable
audit_log_include_accounts 
audit_log_exclude_accounts 
Command Line
YES
Scope
Global
Dynamic
YES
Variable Type
String
Default Value
NULL

指定记录包含/不包含的用户的审计日志，取值逗号分隔，如果设置include或者exclude，则另一个变量不能设置任何值，只能将设置值的变量置NULL后才能设置另一个变量的值，反之亦然。

注意：设置过滤后，在已经存在的服务器连接中不生效，需要重新连接才生效。

举例：

mysql> SET GLOBAL audit_log_include_accounts = 'user1@localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL audit_log_exclude_accounts = 'root@localhost';
Query OK, 0 rows affected (0.00 sec)

audit_log_include_commands / audit_log_exclude_commands

variable
audit_log_include_commands
audit_log_exclude_commands
Command Line
YES
Scope
Global
Dynamic
YES
Variable Type
String
Default Value
NULL

指定记录包含/不包含的命令类型的审计日志，取值逗号分隔，如果设置include或者exclude，则另一个变量不能设置任何值，只能将设置值的变量置NULL后才能设置另一个变量的值，反之亦然。

Commands可以设置的类型：

mysql> SELECT name FROM performance_schema.setup_instruments WHERE name LIKE "statement/sql/%" ORDER BY name;
+---------------------------------------+
| name                                  |
+---------------------------------------+
| statement/sql/alter_db                |
| statement/sql/alter_db_upgrade        |
| statement/sql/alter_event             |
| statement/sql/alter_function          |
| statement/sql/alter_instance          |
| statement/sql/alter_procedure         |
| statement/sql/alter_server            |
| statement/sql/alter_table             |
| statement/sql/alter_tablespace        |
| statement/sql/alter_user              |
| statement/sql/analyze                 |
| statement/sql/assign_to_keycache      |
| statement/sql/begin                   |
| statement/sql/binlog                  |
| statement/sql/call_procedure          |
| statement/sql/change_db               |
……
| statement/sql/xa_rollback             |
| statement/sql/xa_start                |
+---------------------------------------+
141 rows in set (0.00 sec)

举例：

mysql> SET GLOBAL audit_log_include_commands = NULL;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL audit_log_exclude_commands= 'set_option,create_db';
Query OK, 0 rows affected (0.00 sec)

audit_log_include_databases / audit_log_exclude_databases

variable
audit_log_include_databases
audit_log_exclude_databases
Command Line
YES
Scope
Global
Dynamic
YES
Variable Type
String
Default Value
NULL

指定记录包含/不包含的DB的审计日志，取值逗号分隔，如果设置include或者exclude，则另一个变量不能设置任何值，只能将设置值的变量置NULL后才能设置另一个变量的值，反之亦然。

PS：只有当Query访问对应过滤条件audit_log_include_databases和audit_log_exclude_databases内的表时，过滤条件才生效，比如：

设置了audit_log_exclude_databases=test，那么只有query成功访问了test库中的表时，过滤条件才会生效。

访问表的命令类型包括：SQLCOM_REPLACE_SELECT、SQLCOM_INSERT_SELECT、SQLCOM_INSERT、SQLCOM_REPLACE、SQLCOM_LOAD、SQLCOM_DELETE、SQLCOM_DELETE_MULTI、SQLCOM_TRUNCATE、SQLCOM_UPDATE、SQLCOM_UPDATE_MULTI、SQLCOM_SELECT和SQLCOM_HA_READ。

设置举例：

mysql> SET GLOBAL audit_log_include_databases = 'test,mysql,db1';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL audit_log_exclude_databases = NULL;
Query OK, 0 rows affected (0.00 sec)

audit_log_include_tables / audit_log_exclude_tables（新增）

variable
audit_log_include_tables
audit_log_exclude_tables
Command Line
YES
Scope
Global
Dynamic
YES
Variable Type
String
Default Value
NULL

指定记录包含/不包含的table的审计日志，取值逗号分隔，如果设置include或者exclude，则另一个变量不能设置任何值，只能将设置值的变量置NULL后才能设置另一个变量的值，反之亦然。

PS：只有当Query访问对应过滤条件audit_log_include_tables和audit_log_exclude_tables内的表时，过滤条件才生效，比如：

设置了audit_log_exclude_tables=test，那么只有query成功访问了test表时，过滤条件才会生效。

另外还需要注意：如果同时设置了audit_log_include_databases / audit_log_exclude_databases和audit_log_include_tables / audit_log_exclude_tables参数，它们是条件与的关系。比如同时include库test 表t1，则只有操作test.t1表才会记录审计日志。

举例：

mysql> SET GLOBAL audit_log_include_tables = 't1,test';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL audit_log_exclude_tables = NULL;
Query OK, 0 rows affected (0.00 sec)

audit_log_file

variable
audit_log_file
Command Line
YES
Scope
Global
Dynamic
No
Variable Type
String
Default Value
mysql-audit.log

指定审计日志路径及文件名称，路径可为相对路径或路径。

audit_log_flush

variable
audit_log_flush
Command Line
YES
Scope
Global
Dynamic
YES
Variable Type
String
Default Value
OFF

手动日志归档，只有在audit_log_rotate_on_size=0的时候生效，在手工重命名审计日志归档后，可以指定audit_log_flush=ON来打开新的审计日志。

例如：已经记录的审计日志文件mysql-audit.log，当需要手动归档的时候先将mysql-audit.log修改成其他名字mysql-audit-bak.log，此时日志仍会被记录在修改名字后的文件中mysql-audit-bak.log，通过设置set global audit_log_flush = ON; 会新打开审计日志mysql-audit.log继续记录日志。

audit_log_format

variable
audit_log_format
Command Line
YES
Scope
Global
Dynamic
NO
Variable Type
String
Default Value
JSON
Allowed values
OLD, NEW, CSV, JSON

指定日志格式：OLD, NEW, JSON 和 CSV。OLD和NEW都是XML格式，NEW要比OLD的标签详细一些。这四种格式记录的内容是相同的。详细的日志格式类型及字段描述见第4章节。

audit_log_fsync_size（新增）

variable
audit_log_fsync_size
Command Line
YES
Scope
Global
Dynamic
YES
Variable Type
Numeric
Default Value
64MB

审计日志文件缓冲区大小，当审计日志文件缓冲区内的日志大小超过阈值时调用fsync刷盘。当audit_log_handler 为 'FILE' 并且 audit_log_strategy设置为ASYNCHRONOUS或者PERFORMANCE时生效。audit_log_fsync_size大小设置需要为4096的整数倍，如果是0则不主动fsync，用操作系统的默认刷盘机制pdflush线程定期/proc/sys/vm/dirty_expire_centisecs(单位是1/100秒，默认3000)刷新变脏时间超过dirty_expire_centisecs的页面到磁盘。

该参数可以调整日志刷盘的频率，调整性能波动。

audit_log_handler

variable
audit_log_handler
Command Line
YES
Scope
Global
Dynamic
NO
Variable Type
String
Default Value
FILE
Allowed values
FILE, SYSLOG

指定审核日志保存形式：FILE或者SYSLOG。

参数audit_log_strategy, audit_log_buffer_size, audit_log_fsync_size , audit_log_rotate_on_size, 和audit_log_rotations 只有在审计日志是FILE的情况下才生效。

参数 audit_log_syslog_ident, audit_log_syslog_facility,和audit_log_syslog_priority只在审计日志是SYSLOG的情况才生效。

audit_log_policy

variable
audit_log_policy
Command Line
YES
Scope
Global
Dynamic
YES
Variable Type
String
Default Value
ALL
Allowed values
ALL, LOGINS, QUERIES, NONE

指定审计日志记录的事件，设置为NONE关闭记录审计日志，性能与未开启审计一致。

ALL - 记录所有事件
LOGINS - 只记录登录连接事件
QUERIES - 只记录SQL事件
NONE - 不记录任何事件

audit_log_rotate_on_size

variable
audit_log_rotate_on_size
Command Line
YES
Scope
Global
Dynamic
YES
Variable Type
Numeric
Default Value
0 (don’t rotate the log file)

指定大审计日志大小，超过日志文件将会循环。变量生效条件，audit_log_handler 为 'FILE'并且audit_log_rotations大于0。audit_log_rotate_on_size 的大小设置需要是4096的正整数倍。

audit_log_rotations

variable
audit_log_rotations
Command Line
YES
Scope
Global
Dynamic
YES
Variable Type
String
Default Value
0

指定保存审计日志个数，变量生效条件，audit_log_handler 为 'FILE'并且audit_log_rotate_on_size大于0。

使用建议：

当业务只需要保留近一段时间的审计日志时，可以根据业务产生的日志量估算近一段时间的日志大小，设置audit_log_rotations和audit_log_rotate_on_size。比如，业务只需要保留近7天的审计日志，大约10GB，可以设置audit_log_rotations=10，audit_log_rotate_on_size=1GB，这样会循环使用10个1GB的审计日志文件记录日志，可以固定审计日志的磁盘存储空间防止审计日志无限增长，并减少磁盘空间浪费。

audit_log_strategy

variable
audit_log_strategy
Command Line
YES
Scope
Global
Dynamic
NO
Variable Type
String
Default Value
ASYNCHRONOUS
Allowed values
ASYNCHRONOUS, PERFORMANCE, 
SEMISYNCHRONOUS, SYNCHRONOUS

指定审计日志的刷新策略，只有在 audit_log_handler 为 'FILE' 时该变量才生效。

ASYNCHRONOUS - 异步刷新，使用内存缓冲区，缓冲区满时不删除消息
PERFORMANCE - 使用内存缓冲区，缓冲区满时删除消息
SEMISYNCHRONOUS - 直接写入到文件中，通过操作系统刷新同步事件
SYNCHRONOUS - 直接写入到文件中，实时同步刷新事件

audit_log_syslog_facility

variable
audit_log_syslog_facility
Command Line
YES
Scope
Global
Dynamic
YES
Variable Type
String
Default Value
LOG_USER
Allowed values：
LOG_USER, LOG_AUTHPRIV, LOG_CRON, LOG_DAEMON, LOG_FTP, LOG_KERN,   LOG_LPR, LOG_MAIL, LOG_NEWS, LOG_SYSLOG, LOG_AUTH, LOG_UUCP, LOG_LOCAL0, LOG_LOCAL1, LOG_LOCAL2, LOG_LOCAL3, LOG_LOCAL4, LOG_LOCAL5, LOG_LOCAL6,  LOG_LOCAL7

指定syslog的facility值。参数取值说明与参考文档2中的描述一致。

audit_log_syslog_ident

variable
audit_log_syslog_ident
Command Line
YES
Scope
Global
Dynamic
NO
Variable Type
String
Default Value
innosql-audit

指定syslog的ident值。

audit_log_syslog_priority

variable
audit_log_syslog_priority
Command Line
YES
Scope
Global
Dynamic
YES
Variable Type
String
Default Value
LOG_INFO
Allowed values：
LOG_INFO, LOG_ALERT, LOG_CRIT, LOG_ERR, LOG_WARNING, LOG_NOTICE, LOG_EMERG, LOG_DEBUG

指定syslog的priority值。参数取值说明与参考文档2中的描述一致。

audit_log_timestamps（新增）

variable
audit_log_timestamps
Command Line
YES
Scope
Global
Dynamic
YES
Variable Type
String
Default Value
UTC
Allowed values
UTC, SYSTEM

指定审计日志时间戳类型UTC / SYSTEM。

日志格式

日志格式举例

审计日志默认JSON格式，日志格式可以通过设置audit_log_format为OLD（XML格式）、NEW（NEW要比OLD的标签详细一些）、JSON和CSV。

OLD格式

<AUDIT_RECORD
  NAME="Query"
  RECORD="22_2019-10-17T08:49:22"
  TIMESTAMP="2019-10-17T09:51:17 UTC"
  COMMAND_
  COMMAND_DB="information_schema"
  CONNECTION_ID="13"
  STATUS="0"
  SQLTEXT="show databases"
  DIGEST_TEXT="SHOW SCHEMAS "
  QUERY_TIME="0.000118"
  LOCK_TIME="0.000032"
  SCAN_ROWS="5"
  AFFECTED_ROWS="-1"
  USER="root[root] @ localhost []"
  HOST="localhost"
  OS_USER=""
  IP=""
  DB="test"
/>

NEW格式

<AUDIT_RECORD>
  <NAME>Connect</NAME>
  <RECORD>9040_2019-10-17T09:53:06</RECORD>
  <TIMESTAMP>2019-10-17T09:53:45 UTC</TIMESTAMP>
  <CONNECTION_ID>3</CONNECTION_ID>
  <STATUS>0</STATUS>
  <USER>root</USER>
  <PRIV_USER>root</PRIV_USER>
  <OS_LOGIN></OS_LOGIN>
  <PROXY_USER></PROXY_USER>
  <HOST>localhost</HOST>
  <IP></IP>
  <DB></DB>
</AUDIT_RECORD>

JSON格式

{"audit_record":{"name":"Audit","record":"13822_2019-10-17T09:56:50","timestamp":"2019-10-17T09:57:37 UTC","mysql_version":"5.7.20-v3c-log","startup_options":"--defaults-file=/home/fengsen/data/mysql-5.7.20-v3c/mysql/my.cnf","os_version":"x86_64-Linux"}}

CSV格式

"Query","15040_2019-10-18T01:31:39","2019-10-18T01:35:33 UTC","show_databases","information_schema","3",0,"show databases","SHOW SCHEMAS ","0.000378","0.000089","5","-1","root[root] @ localhost []","localhost","","",""

日志字段说明

审计插件提供了三种事件监控记录：Audit、Query 和 Connection，每种事件都有不同的字段记录审计日志信息，其中Audit事件记录的是审计插件的安装和卸载，Query事件记录的是所有的Query操作，Connection事件记录的是连接和关闭连接的信息。

AUDIT事件字段

NAME：事件名称，Audit表示审计插件安装，NoAudit表示审计插件卸载
RECORD：的记录ID
TIMESTAMP：时间戳
MYSQL_VERSION：MySQL server版本号
STARTUP_OPTIONS：命令行启动参数
OS_VERSION：操作系统版本号

QUERY事件字段

NAME：事件名称，如Query, Prepare, Execute, Change user等
RECORD：的记录ID
TIMESTAMP：时间戳
COMMAND_CLASS：操作命令类，如select, alter_table, create_table等
COMMAND_DB（新增）：执行QUERY所涉及的DB名称，多个DB使用逗号分隔。
CONNECTION_ID：连接ID
STATUS：非0表示有错误，非0数字代表错误号
SQLTEXT：SQL语句原型
DIGEST_TEXT（新增）：记录SQL模板
QUERY_TIME（新增）：记录SQL执行时间，单位秒，到微秒
LOCK_TIME（新增）：记录SQL执行时的加锁时间，单位秒，到微秒
SCAN_ROWS（新增）：记录SQL扫描行数
AFFECTED_ROWS（新增）：记录SQL更新行数
USER：连接的用户名
HOST：连接的主机名
OS_USER：外部用户名
IP：连接用户IP
DB：连接时指定的数据库名

举例说明几组字段的区别和含义：

1、COMMAND_DB字段和DB字段，COMMAND_DB字段是新增的字段，是为了记录当前Query操作中涉及的DB信息，比如insert into test1.t1 select * from test2.t2; 该字段会记录"test1,test2"，便于按照操作库进行筛选日志。而DB字段记录的信息是当前连接命令所指定的数据库的名称。如连接mysql -S mysql.sock -u root -D test 这时DB字段会记录"test"。如果不指定-D 该字段内容为空。IP字段也是一样，需要连接命令指定-h才会记录连接的IP。

2、DIGEST_TEXT字段和SQLTEXT字段，DIGEST_TEXT字段是新增的字段，是为了记录SQL的模板信息，类似于预处理执行prepare的语句，常量的数值用 ‘?’ 代替，便于统计某一类SQL的审计信息；而SQLTEXT字段记录的是SQL语句本身。

DIGEST_TEXT模板的记录方式分两种情况，一种是普通的SQL语句，另一种是预处理执行的SQL语句。两者的区别：普通的SQL语句每次执行都会进行一次词法语法解析，在词法解析的过程中会将SQL语句解析为token数组，同时调用函数digest_add_token()将SQL语句中的常量数值部分按照lex_token.h文件中定义的替换规则换成特殊的符号 ‘?’ , ‘?...’ , ‘(...)’等，DIGEST_TEXT字段调用compute_digest_text()将替换后token重新拼接成SQL模板，这样就获取了对应的模板SQL；而预处理的方式执行的SQL，相同语句只在prepare时进行一次词法语法解析，然后将解析后的语法解析树放入内存缓存，execute语句时直接使用内存中对应的语法树进行执行，省去了词法语法解析的过程，所以execute操作也就没有token解析替换过程，这种情况DIGEST_TEXT字段记录的是prepare的SQL语句。

SQL语句的词法解析过程是按照lex_token.h文件中定义的规则进行token替换的。

DIGEST_TEXT普通SQL举例说明：

SQLTEXT="select * from t1 limit 3"
DIGEST_TEXT="SELECT * FROM `t1` LIMIT ? " 

SQLTEXT="select * from t1 limit 50,3"
DIGEST_TEXT="SELECT * FROM `t1` LIMIT ?, ... "

SQLTEXT="insert into t1(a) values(1)"
DIGEST_TEXT="INSERT INTO `t1` ( `a` ) VALUES (?) "

SQLTEXT="insert into t1(a) values(1),(2),(3)"
DIGEST_TEXT="INSERT INTO `t1` ( `a` ) VALUES (?) /* , ... */ "

SQLTEXT="insert into t1 values(1,'abc')"
DIGEST_TEXT="INSERT INTO `t1` VALUES (...) "

SQLTEXT="insert into t1 values(1,'abc'),(2,'def'),(3,'ghj')"
DIGEST_TEXT="INSERT INTO `t1` VALUES (...) /* , ... */ "

如果是预处理的方式执行SQL，那么DIGEST_TEXT记录的是prepare的模板SQL

SQLTEXT="INSERT INTO sbtest1 (id, k, c, pad) VALUES (499186, 547389, '09639910877-81539071641-44797030830-49942591848-82590254546-12903447993-68880971699-36045188022-69731708334-89286798364', '84740111240-38162945696-58845517039-45078827441-99916611431')"
DIGEST_TEXT="INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)"

3、AFFECTED_ROWS字段记录的是更新的行数：

for DML statements: to the number of affected rows;
for DDL statements: to 0.
for result set to -1, to indicate that there was a result set.

4、USER和OS_USER的区别

USER="root[root] @ localhost []"

OS_USER=""

USER字段是按照priv_user[user] @ host [ip] 的格式进行记录的，其中priv_user表示的是用户的权限。而OS_USER是外部用户名，当MySQL服务器上基于认证插件进行用户认证时才会有外部用户名。

CONNECTION事件字段

NAME：事件名称，如Connect, Quit
RECORD：的记录ID
TIMESTAMP：时间戳
CONNECTION_ID：连接ID
STATUS：0表示连接成功，非0表示连接失败
USER：连接用户名
PRIV_USER：连接用户的权限
OS_LOGIN：外部用户名
PROXY_USER：代理用户名
HOST：连接主机名
IP：连接用户IP
DB：连接时指定的数据库名

HA如何配置审计

在HA配置下，主从都需要安装审计插件，从节点回放binlog的过程不会被记录审计日志，从节点只会记录发生在其上的Query操作。当发生主从切换的时候，新的主节点可以继续记录业务的审计日志，保证HA集群始终处于审计状态。

审计插件在HA配置前后均可以通过install安装。

MGR如何配置审计

MGR的审计配置方式与HA类似，各个MGR节点都需要安装审计插件，当PRIMARY节点发生故障切换时，其他SECONDARY节点升级为PRIMARY节点后可以继续记录业务的审计日志，保证MGR集群始终处于审计状态。

配置MGR前需要先安装审计插件再配置MGR，如果MGR已经配置完，那么SECONDARY节点会因为设置了super-read-only而无法通过install的方式安装审计插件。

总结

审计优势：

审计插件能够细粒度的记录用户对数据库的操作，当数据库出现问题时能够协助定位分析用户操作行为，从而提升数据库的安全性。
丰富的审计内容：包括用户连接，关闭，DML操作，存储过程，触发器，事件，预处理等。
灵活的审计策略：可以自定义审计策略过滤不同用户，不同命令，不同数据库和不同的表。可以在线开启和停用审计功能。
丰富的日志格式：支持记录XML，JSON和CSV格式的日志文件，可根据用户需求扩展审计日志的字段。

审计劣势：

开启审计会增加数据库的性能开销，经测试InnoSQL 5.7版本采用8核16GB内存的云主机，sysbench测试20GB数据32线程并发oltp_read_write，审计配置默认参数，开启审计性能下降4.7%，TPS曲线波动平稳。
审计日志会额外占用大量的磁盘存储空间，需要提前规划好磁盘使用情况。