Documentation Home
Security in MySQL
Related Documentation Download this Excerpt
PDF (US Ltr) - 1.6Mb
PDF (A4) - 1.6Mb
HTML Download (TGZ) - 335.4Kb
HTML Download (Zip) - 342.8Kb

Security in MySQL  /  ...  /  Audit Log Filtering

6.5.6 Audit Log Filtering


This section describes how audit log filtering works as of MySQL 5.7.13 if the audit log plugin and the accompanying audit tables and UDFs are installed. If the plugin is installed but not the accompanying audit tables and UDFs, the plugin operates in legacy filtering mode, described in Section 6.5.7, “Legacy Mode Audit Log Filtering”. Legacy mode is filtering behavior as it was prior to MySQL 5.7.13; that is, before the introduction of rule-based filtering.

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

  • Audited events can be filtered using these characteristics:

    • User account

    • Audit event class

    • Audit event subclass

    • Value of event fields such as those that indicate operation status or SQL statement executed

  • Audit filtering is rule based:

    • A filter definition creates a set of auditing rules. Definitions can be configured to include or exclude events for logging based on the characteristics just described.

    • As of MySQL 5.7.20, filter rules have the capability of blocking (aborting) execution of qualifying events, in addition to existing capabilities for event logging.

    • Multiple filters can be defined, and any given filter can be assigned to any number of user accounts.

    • It is possible to define a default filter to use with any user account that has no explicitly assigned filter.

  • Audit filters can be defined, displayed, and modified using an SQL interface based on user-defined functions (UDFs).

  • Audit filter definitions are stored in the tables in the mysql system database.

  • Within a given session, the value of the read-only audit_log_filter_id system variable indicates whether a filter has been assigned to the session.


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

The following list briefly summarizes the UDFs that implement the SQL interface for audit filtering control:

For usage examples and complete details about the filtering functions, see Section, “Using Audit Log Filtering Functions”, and Section, “Audit Log Functions”.

Audit log filtering functions are subject to these constraints:

  • To use any filtering function, the audit_log plugin must be enabled or an error occurs. In addition, the audit tables must exist or an error occurs. To install the audit_log plugin and its accompanying UDFs and tables, see Section 6.5.2, “Installing or Uninstalling MySQL Enterprise Audit”.

  • To use any filtering function, a user must possess the SUPER privilege or an error occurs. To grant the SUPER privilege to a user account, use this statement:

    GRANT SUPER ON *.* TO user;

    Alternatively, should you prefer to avoid granting the SUPER 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 UDFs in Section, “Using General-Purpose Keyring Functions”; it can be adapted for use with filtering UDFs.

  • The audit_log plugin operates in legacy mode if it is installed but the accompanying audit tables and functions are not created. The plugin writes these messages to the error log at server startup:

    [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, filtering can be done based only on event account or status. For details, see Section 6.5.7, “Legacy Mode Audit Log Filtering”.