Documentation Home
Security in MySQL
Related Documentation Download this Excerpt
PDF (US Ltr) - 1.7Mb
PDF (A4) - 1.7Mb
HTML Download (TGZ) - 349.3Kb
HTML Download (Zip) - 357.8Kb

Security in MySQL  /  ...  /  Using Audit Log Filtering Functions Using Audit Log Filtering Functions

Before using the audit log user-defined functions (UDFs), install them according to the instructions provided in Section 7.5.2, “Installing or Uninstalling MySQL Enterprise Audit”. The SUPER 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 using JSON data in MySQL, see The JSON Data Type. This section shows some simple filter definitions. For more information about filter definitions, see Section, “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 filter is assigned to the user, the audit log uses that filter.

  • 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 instead you want 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:

  • 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.

  • 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 has been assigned, including any current sessions for those users.

The filtering UDFs just described affect audit filtering immediately and update the audit log tables in the mysql system database that store filters and user accounts (see Section, “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();

To determine whether a filter has been assigned to the current session, check the session value of the read-only audit_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 |