Rechercher dans le manuel MySQL

6.4.5.6 Audit Log Filtering

Contents
Note

This section describes how audit log filtering works 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.4.5.8, “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.

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

  1. SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }');
  2. 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 Using Audit Log Filtering Functions, and 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.4.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:

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

Using Audit Log Filtering Functions

Before using the audit log user-defined functions (UDFs), install them according to the instructions provided in Section 6.4.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 Section 11.6, “The JSON Data Type”. This section shows some simple filter definitions. For more information about filter definitions, see Section 6.4.5.7, “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 Section 28.7.7.3, “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:

  1. SET @f = '{ "filter": { "class": { "name": "connection" } } }';
  2. SELECT audit_log_filter_set_filter('log_conn_events', @f);
  3. 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:

  1. SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }');
  2. SELECT audit_log_filter_set_user('user1@localhost', 'log_all');
  3. 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:

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

    1. SELECT audit_log_filter_set_filter('log_nothing', '{ "filter": { "log": false } }');
    2. 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:

  1. 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 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():

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

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:

  1. mysql> SELECT @@audit_log_filter_id;
  2. +-----------------------+
  3. | @@audit_log_filter_id |
  4. +-----------------------+
  5. |                     2 |
  6. +-----------------------+

Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-audit-log-filtering.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

  1. View the html document Language of the document:en Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut