Documentation Home
MySQL 5.7 参考手册

6.4.5.7 审计日志过滤

笔记

从 MySQL 5.7.13 开始，要使审计日志过滤按此处所述工作，必须安装审计日志插件和随附的审计表和函数。如果插件安装时没有附带基于规则的过滤所需的审计表和函数，插件将在旧过滤模式下运行，如 第 6.4.5.10 节“旧模式审计日志过滤”中所述。Legacy 模式是 MySQL 5.7.13 之前的过滤行为；也就是说，在引入基于规则的过滤之前。

审计日志过滤的属性

审计日志插件能够通过过滤来控制审计事件的日志记录：

  • 可以使用以下特征过滤已审计的事件：

    • 用户帐号

    • 审计事件类

    • 审计事件子类

    • 审计事件字段，例如指示操作状态或执行的 SQL 语句的字段

  • 审计过滤是基于规则的：

    • 过滤器定义创建一组审计规则。定义可以配置为包括或排除基于刚刚描述的特征的日志记录事件。

    • 从 MySQL 5.7.20 开始，过滤规则除了现有的事件日志记录功能外，还具有阻止（中止）执行合格事件的能力。

    • 可以定义多个过滤器，并且可以将任何给定的过滤器分配给任意数量的用户帐户。

    • 可以定义默认过滤器以用于没有明确分配过滤器的任何用户帐户。

    有关编写过滤规则的信息，请参阅 第 6.4.5.8 节，“编写审计日志过滤器定义”。

  • 可以使用基于函数调用的 SQL 接口定义、显示和修改审计过滤器。

  • 审计过滤器定义存储在 mysql系统数据库的表中。

  • 在给定的会话中，只读 audit_log_filter_id 系统变量的值指示是否将过滤器分配给会话。

笔记

默认情况下，基于规则的审计日志过滤不会记录任何用户的可审计事件。要记录所有用户的所有可审核事件，请使用以下语句，这些语句创建一个简单的过滤器以启用日志记录并将其分配给默认帐户： 

SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }');
SELECT audit_log_filter_set_user('%', 'log_all');

分配给的过滤器%用于来自没有明确分配过滤器的任何帐户的连接（最初对所有帐户都是如此）。

如前所述，审计过滤控制的 SQL 接口是基于函数的。以下列表简要总结了这些功能：

有关过滤功能的使用示例和完整详细信息，请参阅 使用审核日志过滤功能和 审核日志功能。

审计日志过滤功能的约束

审核日志过滤功能受以下限制：

  • 要使用任何过滤功能， audit_log必须启用插件，否则会发生错误。此外，审计表必须存在，否则会出错。要安装 audit_log插件及其附带的函数和表，请参阅 第 6.4.5.2 节，“安装或卸载 MySQL Enterprise Audit”。

  • 要使用任何过滤功能，用户必须拥有 SUPER权限，否则会发生错误。要将SUPER 权限授予用户帐户，请使用以下语句： 

    GRANT SUPER ON *.* TO user;

    或者，如果您希望避免授予 SUPER特权，同时仍允许用户访问特定的过滤功能， 则可以定义“包装器”存储程序。此技术在使用通用密钥环函数中的密钥环函数的上下文中进行了描述；它可以适应与过滤功能一起使用。

  • audit_log如果安装了插件但未创建随附的审计表和函数，则 该插件以旧模式运行。该插件在服务器启动时将这些消息写入错误日志： 

    [Warning] Plugin audit_log reported: 'Failed to open the audit log filter tables.'
[Warning] Plugin audit_log reported: 'Audit Log plugin supports a filtering,
which has not been installed yet. Audit Log plugin will run in the legacy
mode, which will be disabled in the next release.'

    在传统模式下，可以仅根据事件帐户或状态进行过滤。有关详细信息，请参阅 第 6.4.5.10 节，“传统模式审计日志过滤”。

使用审计日志过滤功能

在使用审计日志功能之前，请按照 第 6.4.5.2 节“安装或卸载 MySQL Enterprise Audit”中提供的说明安装它们。使用这些 SUPER功能中的任何一个都需要特权。

审计日志过滤功能通过提供一个接口来创建、修改和删除过滤器定义以及将过滤器分配给用户帐户来启用过滤控制。

过滤器定义是JSON 值。有关 JSON在 MySQL 中使用数据的信息，请参阅 第 11.5 节，“JSON 数据类型”。本节显示一些简单的过滤器定义。有关过滤器定义的更多信息，请参阅第 6.4.5.8 节，“编写审计日志过滤器定义”。

当连接到达时，审计日志插件通过在当前过滤器分配中搜索用户帐户名来确定将哪个过滤器用于新会话：

  • 如果为用户分配了过滤器，则审核日志将使用该过滤器。

  • Otherwise, if no user-specific filter assignment exists, but there is a filter assigned to the default account (%), the audit log uses the default filter.

  • Otherwise, the audit log selects no audit events from the session for processing.

If a change-user operation occurs during a session (see mysql_change_user()), filter assignment for the session is updated using the same rules but for the new user.

By default, no accounts have a filter assigned, so no processing of auditable events occurs for any account.

Suppose that you want to change the default to be to log only connection-related activity (for example, to see connect, change-user, and disconnect events, but not the SQL statements users execute while connected). To achieve this, define a filter (shown here named log_conn_events) that enables logging only of events in the connection class, and assign that filter to the default account, represented by the % account name: 

SET @f = '{ "filter": { "class": { "name": "connection" } } }';
SELECT audit_log_filter_set_filter('log_conn_events', @f);
SELECT audit_log_filter_set_user('%', 'log_conn_events');

Now the audit log uses this default account filter for connections from any account that has no explicitly defined filter.

To assign a filter explicitly to a particular user account or accounts, define the filter, then assign it to the relevant accounts: 

SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }');
SELECT audit_log_filter_set_user('user1@localhost', 'log_all');
SELECT audit_log_filter_set_user('user2@localhost', 'log_all');

Now full logging is enabled for user1@localhost and user2@localhost. Connections from other accounts continue to be filtered using the default account filter.

To disassociate a user account from its current filter, either unassign the filter or assign a different filter:

  • To unassign the filter from the user account: 

    SELECT audit_log_filter_remove_user('user1@localhost');

    Filtering of current sessions for the account remains unaffected. Subsequent connections from the account are filtered using the default account filter if there is one, and are not logged otherwise.

  • To assign a different filter to the user account: 

    SELECT audit_log_filter_set_filter('log_nothing', '{ "filter": { "log": false } }');
SELECT audit_log_filter_set_user('user1@localhost', 'log_nothing');

    Filtering of current sessions for the account remains unaffected. Subsequent connections from the account are filtered using the new filter. For the filter shown here, that means no logging for new connections from user1@localhost.

For audit log filtering, user name and host name comparisons are case-sensitive. This differs from comparisons for privilege checking, for which host name comparisons are not case-sensitive.

To remove a filter, do this: 

SELECT audit_log_filter_remove_filter('log_nothing');

Removing a filter also unassigns it from any users to whom it is assigned, including any current sessions for those users.

The filtering functions just described affect audit filtering immediately and update the audit log tables in the mysql system database that store filters and user accounts (see Audit Log Tables). It is also possible to modify the audit log tables directly using statements such as INSERT, UPDATE, and DELETE, but such changes do not affect filtering immediately. To flush your changes and make them operational, call audit_log_filter_flush(): 

SELECT audit_log_filter_flush();
Warning

audit_log_filter_flush() should be used only after modifying the audit tables directly, to force reloading all filters. Otherwise, this function should be avoided. It is, in effect, a simplified version of unloading and reloading the audit_log plugin with UNINSTALL PLUGIN plus INSTALL PLUGIN.

audit_log_filter_flush() affects all current sessions and detaches them from their previous filters. Current sessions are no longer logged unless they disconnect and reconnect, or execute a change-user operation.

要确定过滤器是否分配给当前会话，请检查只读 audit_log_filter_id系统变量的会话值。如果值为 0，则不分配过滤器。非零值表示分配的过滤器的内部维护 ID： 

mysql> SELECT @@audit_log_filter_id;
+-----------------------+
| @@audit_log_filter_id |
+-----------------------+
|                     2 |
+-----------------------+