网易云社区

个人签名

162篇博客

网易InnoSQL审计插件的功能及使用方法

网易云社区2019-12-31 10:41

前言

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

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表时,过滤条件才会生效。

访问表的命令类型包括: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

另外还需要注意:如果同时设置了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_CLASS="show_databases"
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曲线波动平稳。
审计日志会额外占用大量的磁盘存储空间,需要提前规划好磁盘使用情况。
参考文档
1、https://www.percona.com/doc/percona-server/LATEST/management/audit_log_plugin.html

2、https://linux.die.net/man/3/syslog