Documentation Home
Security in MySQL
Related Documentation Download this Excerpt
PDF (US Ltr) - 1.5Mb
PDF (A4) - 1.5Mb
HTML Download (TGZ) - 315.8Kb
HTML Download (Zip) - 324.5Kb


Security in MySQL  /  ...  /  Audit Log Filtering

7.5.6 Audit Log Filtering

Note

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

Prior to MySQL 5.7.13, the audit log plugin had the capability of controlling logging of audited events by filtering them based on the account from which events originate or event status. As of MySQL 5.7.13, filtering capabilities are extended:

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

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

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

  • audit_log_filter_set_filter(): Define a filter

  • audit_log_filter_remove_filter(): Remove a filter

  • audit_log_filter_set_user(): Start filtering a user account

  • audit_log_filter_remove_user(): Stop filtering a user account

  • audit_log_filter_flush(): Flush manual changes to the filter tables to affect ongoing filtering

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

Audit log filtering functions are subject to these constraints:

  • To use any filtering function, the audit_log plugin must be enabled. Otherwise, an error occurs:

    mysql> SELECT audit_log_filter_flush();
    +----------------------------------------------------------------------------+
    | audit_log_filter_flush()                                                   |
    +----------------------------------------------------------------------------+
    | ERROR: audit_log plugin has not been installed with INSTALL PLUGIN syntax. |
    +----------------------------------------------------------------------------+

    The audit tables must also exist or an error occurs:

    mysql> SELECT audit_log_filter_flush();
    +--------------------------------------------------+
    | audit_log_filter_flush()                         |
    +--------------------------------------------------+
    | ERROR: Could not reinitialize audit log filters. |
    +--------------------------------------------------+

    To install the audit_log plugin, see Section 7.5.2, “Installing or Uninstalling MySQL Enterprise Audit”.

  • To use any filtering function, a user must possess the SUPER privilege. Otherwise, an error occurs:

    mysql> SELECT audit_log_filter_flush()\G
    *************************** 1. row ***************************
    audit_log_filter_flush(): ERROR: Request ignored for 'user1'@'localhost'.
                              SUPER_ACL needed to perform operation

    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 7.4.8.2, “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 7.5.7, “Legacy Mode Audit Log Filtering”.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.