MySQL :: MySQL 8.0 Reference Manual :: 8.4.5.7 Audit Log Filtering (original) (raw)

8.4.5.7 Audit Log Filtering

Properties of Audit Log Filtering

The audit log plugin has the capability of controlling logging of audited events by filtering them:

Note

By default, rule-based audit log filtering logs no auditable events for any users. To log all auditable events for all users, use the following statements, which create a simple filter to enable logging and assign it to the default account:

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

The filter assigned to % is used for connections from any account that has no explicitly assigned filter (which initially is true for all accounts).

As previously mentioned, the SQL interface for audit filtering control is function based. The following list briefly summarizes these functions:

For usage examples and complete details about the filtering functions, seeUsing Audit Log Filtering Functions, andAudit Log Functions.

Constraints on Audit Log Filtering Functions

Audit log filtering functions are subject to these constraints:

GRANT privilege ON *.* TO user;

Alternatively, should you prefer to avoid granting theAUDIT_ADMIN orSUPER privilege while still permitting users to access specific filtering functions,“wrapper” stored programs can be defined. This technique is described in the context of keyring functions in Using General-Purpose Keyring Functions; it can be adapted for use with filtering functions.

[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.'

In legacy mode, which is deprecated as of MySQL 8.0.34, filtering can be done based only on event account or status. For details, seeSection 8.4.5.10, “Legacy Mode Audit Log Filtering”.

Using Audit Log Filtering Functions

Before using the audit log functions, install them according to the instructions provided inSection 8.4.5.2, “Installing or Uninstalling MySQL Enterprise Audit”. TheAUDIT_ADMIN orSUPER privilege is required to use any of these functions.

The audit log filtering functions enable filtering control by providing an interface to create, modify, and remove filter definitions and assign filters to user accounts.

Filter definitions are JSON values. For information about usingJSON data in MySQL, seeSection 13.5, “The JSON Data Type”. This section shows some simple filter definitions. For more information about filter definitions, see Section 8.4.5.8, “Writing Audit Log Filter Definitions”.

When a connection arrives, the audit log plugin determines which filter to use for the new session by searching for the user account name in the current filter assignments:

If a change-user operation occurs during a session (seemysql_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 theconnection 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 foruser1@localhost anduser2@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:

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.

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 fromuser1@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 themysql 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, andDELETE, but such changes do not affect filtering immediately. To flush your changes and make them operational, callaudit_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 theaudit_log plugin withUNINSTALL PLUGIN plusINSTALL 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.

To determine whether a filter is assigned to the current session, check the session value of the read-onlyaudit_log_filter_id system variable. If the value is 0, no filter is assigned. A nonzero value indicates the internally maintained ID of the assigned filter:

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