如何实现MySQL数据库使用情况的审计
发布网友
发布时间：2022-04-24 04:45
共2个回答
懂视网
时间：2022-04-08 08:21
1. 前言
为了安全和操作的可追溯性考虑，越来越多的公司加入了审计功能。mysql5.5推出了相关的审计功能，到5.6.20功能进一步完善，算是勉强可用了，虽然细粒度方面做的不是太好，但是后续版本还是可以期待一下的。这里主要介绍下相关的功能和特性。2. 开启审计
2.1 配置文件加载
mysql5.6中的审计是通过audit_log插件来实现的，我们可以在配置文件中加载该插件来开启。[mysqld] plugin-load=audit_log.so
如果希望数据库强制开启审计功能，如果不开启的话server不启动，或者审计功能不能进行时server挂住，加入
[mysqld] plugin-load=audit_log.so audit-log=FORCE_PLUS_PERMANENT
2.2 加载插件列表
审计功能的开启还有另外一种方式，就是在命令行中安装审计插件。确保在数据库的插件目录中存在audit_log.so。[i686数据库的插件目录默认是/usr/lib/mysql/plugin,中，也可以指定参数plugin_dir]mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';
3. 参数介绍
审计参数如下：
3.1 audit_log_buffer_size
audit_log_buffer_size ：审计缓存，建议设置为4096的倍数，该参数只有在audit_log_strategy为ASYNCHRONOUS时生效。3.2 audit_log_connection_policy
audit_log_connection_policy：记录了连接审计的信息。包含三个参数| Value | Description |
|---|---|
ALL |
Log all connection events |
ERRORS |
Log only failed connection events |
NONE |
Do not log connection events |
3.3 audit_log_current_session
audit_log_current_session：标志当前会话是否进入审计，是个只读参数，只能通过 audit_log_exclude_accounts和 audit_log_include_accounts来控制哪儿些进入会话审计。3.4 audit_log_exclude_accounts/audit_log_include_accounts
audit_log_exclude_accounts：控制哪儿些用户可以不进入审计，字符串类型，默认可以使用逗号分隔。audit_log_include_accounts：控制哪儿些用户可以进入审计，字符串类型，默认可以使用逗号分隔。
exclude和include同时只有一个参数生效。
3.5 audit_log_file
audit_log_file：可以用于控制审计日志的名称和路径。3.6 audit_log_flush
audit_log_flush：控制审计日志的归档，只有在audit_log_rotate_on_size=0的时候生效，在手工重命名审计日志归档后，可以指定audit_log_flush=1来生成新的审计日志。3.7 audit_log_format
audit_log_format：审计日志的格式，分为OLD和NEW(NEW格式在5.6.14才出现)。当更改格式的时候需要进行3个步骤：1 ：关闭数据库 2：重命名当前的audit.log文件
3：更改audit_log_format参数，并重启mysql，重启后会自动生成一个新的audit.log文件
防止NEW格式和OLD格式在同一个审计日志中，会导致审计功能错误。 audit_log_policy ：记录了审计日志的控制策略：
| Value | Description |
|---|---|
ALL |
Log all events |
LOGINS |
Log only login events |
QUERIES |
Log only query events |
NONE |
Log nothing (disable the audit stream |
3.8 audit_log_statement_policy
audit_log_statement_policy：记录了语句的审计策略，可能会被audit_log_policy给覆盖：| Value | Description |
|---|---|
ALL |
Log all statement events |
ERRORS |
Log only failed statement events |
NONE |
Do not log statement events |
3.9 audit_log_rotate_on_size
audit_log_rotate_on_size：审计日志的文件大小。当参数大于0的时候，当审计日志超过后，会自动的重命名为加时间戳后缀的日志文件。同时创建新的审计日志。3.10 audit_log_strategy
audit_log_strategy：审计日志的刷新策略分为：| Value | Meaning |
|---|---|
ASYNCHRONOUS |
Log asynchronously, wait for space in output buffer |
PERFORMANCE |
Log asynchronously, drop request if insufficient space in output buffer |
SEMISYNCHRONOUS |
Log synchronously, permit caching by operating system |
SYNCHRONOUS |
Log synchronously, call sync() after each
request |
4. 日志格式
审计日志格式是XML的形式，NEW要比OLD的标签详细一些。具体的标签信息如下： 实例：<?xml version="1.0" encoding="UTF-8"?> <AUDIT> <AUDIT_RECORD> <TIMESTAMP>2013-09-17T15:03:24 UTC</TIMESTAMP> <RECORD_ID>1_2013-09-17T15:03:24</RECORD_ID> <NAME>Audit</NAME> <SERVER_ID>1</SERVER_ID> <VERSION>1</VERSION> <STARTUP_OPTIONS>/usr/local/mysql/bin/mysqld --socket=/usr/local/mysql/mysql.sock --port=3306</STARTUP_OPTIONS> <OS_VERSION>x86_-osx10.6</OS_VERSION> <MYSQL_VERSION>5.7.2-m12-log</MYSQL_VERSION> </AUDIT_RECORD> <AUDIT_RECORD> <TIMESTAMP>2013-09-17T15:03:40 UTC</TIMESTAMP> <RECORD_ID>2_2013-09-17T15:03:24</RECORD_ID> <NAME>Connect</NAME> <CONNECTION_ID>2</CONNECTION_ID> <STATUS>0</STATUS> <STATUS_CODE>0</STATUS_CODE> <USER>root</USER> <OS_LOGIN></OS_LOGIN> <HOST>localhost</HOST> <IP>127.0.0.1</IP> <COMMAND_CLASS>connect</COMMAND_CLASS> <PRIV_USER>root</PRIV_USER> <PROXY_USER></PROXY_USER> <DB>test</DB> </AUDIT_RECORD> ... <AUDIT_RECORD> <TIMESTAMP>2013-09-17T15:03:41 UTC</TIMESTAMP> <RECORD_ID>4_2013-09-17T15:03:24</RECORD_ID> <NAME>Query</NAME> <CONNECTION_ID>2</CONNECTION_ID> <STATUS>0</STATUS> <STATUS_CODE>0</STATUS_CODE> <USER>root[root] @ localhost [127.0.0.1]</USER> <OS_LOGIN></OS_LOGIN> <HOST>localhost</HOST> <IP>127.0.0.1</IP> <COMMAND_CLASS>drop_table</COMMAND_CLASS> <SQLTEXT>DROP TABLE IF EXISTS t</SQLTEXT> </AUDIT_RECORD> <AUDIT_RECORD> <TIMESTAMP>2013-09-17T15:03:41 UTC</TIMESTAMP> <RECORD_ID>5_2013-09-17T15:03:24</RECORD_ID> <NAME>Query</NAME> <CONNECTION_ID>2</CONNECTION_ID> <STATUS>0</STATUS> <STATUS_CODE>0</STATUS_CODE> <USER>root[root] @ localhost [127.0.0.1]</USER> <OS_LOGIN></OS_LOGIN> <HOST>localhost</HOST> <IP>127.0.0.1</IP> <COMMAND_CLASS>create_table</COMMAND_CLASS> <SQLTEXT>CREATE TABLE t (i INT)</SQLTEXT> </AUDIT_RECORD> ... <AUDIT_RECORD> <TIMESTAMP>2013-09-17T15:03:41 UTC</TIMESTAMP> <RECORD_ID>7_2013-09-17T15:03:24</RECORD_ID> <NAME>Quit</NAME> <CONNECTION_ID>2</CONNECTION_ID> <STATUS>0</STATUS> <STATUS_CODE>0</STATUS_CODE> <USER></USER> <OS_LOGIN></OS_LOGIN> <HOST></HOST> <IP></IP> <COMMAND_CLASS>connect</COMMAND_CLASS> </AUDIT_RECORD> ... <AUDIT_RECORD> <TIMESTAMP>2013-09-17T15:03:47 UTC</TIMESTAMP> <RECORD_ID>9_2013-09-17T15:03:24</RECORD_ID> <NAME>Shutdown</NAME> <CONNECTION_ID>3</CONNECTION_ID> <STATUS>0</STATUS> <STATUS_CODE>0</STATUS_CODE> <USER>root[root] @ localhost [127.0.0.1]</USER> <OS_LOGIN></OS_LOGIN> <HOST>localhost</HOST> <IP>127.0.0.1</IP> <COMMAND_CLASS></COMMAND_CLASS> </AUDIT_RECORD> <AUDIT_RECORD> <TIMESTAMP>2013-09-17T15:03:47 UTC</TIMESTAMP> <RECORD_ID>10_2013-09-17T15:03:24</RECORD_ID> <NAME>Quit</NAME> <CONNECTION_ID>3</CONNECTION_ID> <STATUS>0</STATUS> <STATUS_CODE>0</STATUS_CODE> <USER></USER> <OS_LOGIN></OS_LOGIN> <HOST></HOST> <IP></IP> <COMMAND_CLASS>connect</COMMAND_CLASS> </AUDIT_RECORD> <AUDIT_RECORD> <TIMESTAMP>2013-09-17T15:03:49 UTC</TIMESTAMP> <RECORD_ID>11_2013-09-17T15:03:24</RECORD_ID> <NAME>NoAudit</NAME> <SERVER_ID>1</SERVER_ID> </AUDIT_RECORD> </AUDIT>
<audit>:文件的根标签为<AUDIT>，并以 </AUDIT>为结束标签
<AUDIT_RECORD> :包含一系列的必选标签和可选标签，可选标签是否出现取决于audit record类型。
<NAME>：必选，例如<NAME>Query</NAME>，可能出现的值还包含Audit, Binlog Dump, Change user, Close stmt, Connect Out, Connect, Create DB, Daemon, Debug, Delayed insert, Drop DB, Execute, Fetch, Field List, Init DB, Kill, Long Data, NoAudit, Ping, Prepare, Processlist, Query, Quit, Refresh, Register Slave, Reset stmt, Set option, Shutdown, Sleep, Statistics, Table Dump, Time.
<RECORD_ID>：必选，例如<RECORD_ID>28743_2013-09-18T21:03:24</RECORD_ID>，包含一些列数字和时间戳，数字表示的是记录数，每增加一条记录，数字加1.
<TIMESTAMP>：必选，例如<TIMESTAMP>2013-09-17T15:03:49 UTC</TIMESTAMP>，包含时间戳和时区两部分，记录的是从客户端接收到的sql执行完时刻的时间。
以下标签audit record类型决定是否出现
<COMMAND_CLASS>：命令的类型。例如<COMMAND_CLASS>drop_table</COMMAND_CLASS>.
<CONNECTION_ID>：例如<CONNECTION_ID>127</CONNECTION_ID>，代表客户端连接标识符的无符号整型数字。
<DB>：mysql连接的默认数据库名称，该标签只在 <NAME>值是Connect或Change user时出现.
<HOST>：client端的主机名，该标签只在 <NAME>值是Connect，Change user或Query时出现，例如<HOST>localhost</HOST>。
<IP>：client端的IP地址，该标签只在 <NAME>值是Connect，Change user或Query时出现，例如<IP>127.0.0.1</IP>。
<MYSQL_VERSION>：mysql版本号，只在 <NAME>值是Audit时出现，例如<MYSQL_VERSION>5.7.1-m11-log</MYSQL_VERSION>
<OS_LOGIN>：外部用户，该标签只在 <NAME>值是Connect，Change user或Query时出现。
<OS_VERSION>：表示运行数据库的服务器的操作系统，只在 <NAME>值是Audit时出现，例如<OS_VERSION>x86_-Linux</OS_VERSION>。
<PRIV_USER>：服务器认证的客户端名称。该标签只在 <NAME>值是Connect或Change user时出现。例如<PRIV_USER>root</PRIV_USER>。
<PROXY_USER>：通过proxy连接到mysql的用户。该标签只在 <NAME>值是Connect或Change user时出现。
<SERVER_ID>：mysql数据库服务器的ID号，该标签只在 <NAME>值是Audit或No Audit时出现。例如<SERVER_ID>1</SERVER_ID>。
<SQLTEXT>：实际执行的SQL语句。该标签只在 <NAME>值是 Query 或 Execute时出现。例如<SQLTEXT>DELETE FROM t1</SQLTEXT>。
<STARTUP_OPTIONS>：mysql数据库启动选项，该标签只在 <NAME>值是Audit时出现，例如<STARTUP_OPTIONS>/usr/local/mysql/bin/mysqld --port=3306 --log-output=FILE</STARTUP_OPTIONS>
<STATUS>：代表sql命令的执行状态，0表示成功，其余表示有错误。例如<STATUS>1051</STATUS>。
<STATUS_CODE>：代表sql命令的执行状态，0表示成功，1表示有错误。例如<STATUS_CODE>0</STATUS_CODE>。
<USER>：客户端连接mysql服务器的用户名。例如<USER>root[root] @ localhost [127.0.0.1]</USER>。
<VERSION>：表示日志文件格式的版本号。该标签只在 <NAME>值是Audit时出现。例如<VERSION>1</VERSION>。
5. 审计
审计日志默认存放在data路径下，由于XML文件没有经过加密，官网建议通过参数指定到特殊路径下，设置相应人员权限，进行安全控制。 此外审计功能有如下情况不能进行记录： 1）只有top-level（无变量定义）的语句才能进行审计，存储程序如存储过程，触发器，函数等不审计； 2）涉及到外部文件的语句无法进行审计，如load data infile。版权声明：本文为博主原创文章，未经博主允许不得转载。
Mysql5.6审计功能
标签：数据库 mysql 安全
热心网友
时间：2022-04-08 05:29
mysql的审计功能
mysql服务器自身没有提供审计功能,但是我们可以使用init-connect + binlog的方法进行mysql的操作审计。由于mysql binlog记录了所有对数据库长生实际修改的sql语句，及其执行时间，和connection_id但是却没有记录connection_id对应的详细用户信息。在后期审计进行行为追踪时，根据binlog记录的行为及对应的connection-id 结合 之前连接日志记录 进行分析，得出最后的结论。
1. 设置init-connect
1.1 创建用于存放连接日志的数据库和表
create database accesslog;
CREATE TABLE accesslog.accesslog (`id` int(11) primary key auto_increment, `time` timestamp, `localname` varchar(30), `matchname` varchar(30))
1.2 创建用户权限
可用现成的root用户用于信息的读取
grant select on accesslog.* to root;
如果存在具有to *.* 权限的用户需要进行*。
这里还需要注意用户必须对accesslog表具有insert权限
grant select on accesslog.* to user@’%’;
1.3 设置init-connect
在[mysqld]下添加以下设置：
init-connect=’insertinto accesslog.accesslog(id, time, localname, matchname)
values(connection_id(),now(),user(),current_user());’
------注意user()和current_user()的区别
log-bin=xxx
这里必须开启binlog
1.4 重启数据库生效
shell> /etc/init.d/mysql restart
2. 记录追踪
2.1 thread_id确认
可以用以下语句定位语句执行人
Tencent:~ # mysqlbinlog --start-datetime='2011-01-26 16:00:00'
--stop-datetime='2011-01-26 17:00:00' /var/lib/mysql/mysql-bin.000010
| grep -B 5 'wsj'
COMMIT/*!*/;
# at 767
#110126 16:16:43 server id 1 end_log_pos 872 Query thread_id=19 exec_time=0 error_code=0
use test/*!*/;
SET TIMESTAMP=1296029803/*!*/;
create table wsj(id int unsigned not null)
--
BEGIN
/*!*/;
# at 940
#110126 16:16:57 server id 1 end_log_pos 1033 Query thread_id=19 exec_time=0 error_code=0
SET TIMESTAMP=1296029817/*!*/;
insert into wsj(id) values (1)
--
BEGIN
/*!*/;
# at 1128
#110126 16:16:58 server id 1 end_log_pos 1221 Query thread_id=19 exec_time=0 error_code=0
SET TIMESTAMP=1296029818/*!*/;
insert into wsj(id) values (2)
2.2 用户确认
thread_id 确认以后，找到元凶就只是一条sql语句的问题了。
mysql> select * from accesslog where id=19;
+----+---------------------+---------------------+-----------+
| id | time | localname | matchname |
+----+---------------------+---------------------+-----------+
| 19 | 2011-01-26 16:15:54 | test@10.163.1.216 | test@% |
+----+---------------------+---------------------+-----------+
1 row in set (0.00 sec)
3. Q
Q：使用init-connect会影响服务器性能吗？
A：理论上，只会在用户每次连接时往数据库里插入一条记录，不会对数据库产生很大影响。除非连接频率非常高（当然，这个时候需要注意的就是如何进行连接复用和控制，而非是不是要用这种方法的问题了）---如果采用长连接并且缓存的话,可以提高性能
Q：access-log表如何维护?
A: 由于是一个log系统，推荐使用archive存储引擎，有利于数据厄压缩存放。如果数据库连接数量很大的话，建议一定时间做一次数据导出，然后清表。
Q：表有其他用途么？
A：有！access-log表当然不只用于审计，当然也可以用于对于数据库连接的情况进行数据分析，例如每日连接数分布图等等，只有想不到没有做不到。---可以用来测试读写分离,验证负载均衡等
Q：会有遗漏的记录吗？
A：会的，init-connect 是不会在super用户登录时执行的。所以access-log里不会有数据库超级用户的记录，这也是为什么我们不主张多个超级用户，并且多人使用的原因。--这种审计不会记录root等具有super权限的账号对数据库的访问
