MySQL 5.7: New Audit Log Filtering Feature – Part 1

Security auditing plays important role in the process of securing a database system. Thanks to the MySQL Enterprise Audit extension, we can record all activities, such as client connections and execution of queries into a single log file, for later inspection. New audit log filtering feature brings certain benefits:

  • Significant decrease of the audit log file size;
  • Minimized performance overhead;
  • Improved filtering capabilities.

To minimize performance overhead of the audit log, event filtering must be properly configured, taking into consideration our security audit objectives. This allows logging only events, interesting from our perspective, which also minimize audit log file size. Logging every single server operation can significantly affect its performance.

Audit Log Filtering Installation

Audit log filtering feature must be installed before the first use. The installation can be done by running a script contained within the MySQL package:

Windows installation requires use of the audit_log_filter_win_install.sql script.
Please make sure that all instructions contained within the installation script have succeeded.
Installation consists of the following phases:

  • Creation of audit_log_filter and audit_log_user tables in the mysql database. All changes made in the filtering configuration are permanently stored in these tables. Configuration is automatically reloaded on the server startup.
  • Creation of UDFs that expose filtering configuration interface.
  • Instalation of the audit log plugin using the INSTALL PLUGIN syntax.

Installation of the audit log plugin with the INSTALL PLUGIN syntax is still possible without the need of running the script, which creates the tables and the UDFs. In result the server will generate a warning that filtering has not been activated and the audit log will behave according to the legacy specification described here:

Although the audit log plugin will work in the legacy mode, it is highly recommended to turn on the filtering capability. The legacy mode will be removed in the future release.

Capture Everything

Before we start configuring the audit log filtering, let’s create an ‘audit_log_user’ account:

Please note that a newly created account string is ‘audit_log_user’@’%’. Audit log filtering bases on account strings, not the user name and host name specified when connecting to the server.
First of all we have to create a filter using the audit_log_filter_set_filter UDF:

This creates a filter named 'all_enabled' and enables logging everything with a simple JSON:

Now it’s time to assign the filter to the ‘audit_log_user’@’%’ account. This can be done using the audit_log_fitler_set_user UDF:

Creating these two elements guarantees that all events related to ‘audit_log_user’@’%’ are written into audit log file.

Testing a Filter

After creating a filter for the 'audit_log_user'@'%' account, we can examine, whether the filter works as intended. Use the new account to login, execute some queries and disconnect the client:

The audit.log file, which is located by default in the MySQL data directory, contains entries that corresponds to the actions did using the 'audit_log_user'@'%' account.

Presented XML log has been simplified for readability purposes.

Disable Logging

After the installation of the audit log filtering, using the script, logging is disabled by default. Logging can be disabled explicitly by defining a new all_disabled filter:

Followed by the assignment of the audit_log_user to the newly created filter:

Performing any activities, using the audit_log_user account, will not be reflected in the audit log file from now on.

Filter Management

Two filter were created so far (all_enabled and all_disabled) and only one of them is in use (associated with the account). The association of the filter can be removed from the audit_log_user account in two ways. The first one is removing the association using the audit_log_filter_remove_user UDF:

Another way is to remove the filter using the audit_log_filter_remove_filter UDF:

Where the audit_log_filter_remove_user removes the account to filter association only, leaving the filter for a later reuse, the audit_log_filter_remove_filter removes a filter and the account to filter association as well.

Event Classes

Logging of all events may not be too practical. Large audit log files are difficult for post processing, not mentioning the affected server’s performance that suffers from dumping high volume of data into a log file. We can limit it to log only events of the specific class. There are three classes that we may select:

Class name Description
connection Client connection related events.
general Command execution status event.
table_access Some SQL syntaxes generate this event to inform that a table is being accessed.

Events of the specified class can be logged by using the class element. All connection related events can be logged with the following filter:

Logging events of more than one class can be done by listing classes in the JSON array element:

For readability purposes, the "log": true element can be omitted. It is always assumed that the element is always logged, when explicitly specified:

Events of the Event Classes

Event class is a high level specifier that groups other events (subevents). Audit log filtering allows fine granularity logging of the following events:

Class name Event name Description
connection connect Event that notificates an incoming connection. This event also notificates failed authentication attempts.
disconnect The client has just disconnected.
change_user User reauthenticates maintaining the current session.
general status Command execution status event.
table_access read Event generated during execution of the table read queries, e.g. SELECT, INSERT ... SELECT etc.
insert Event generated during execution of the table insert queries, e.g. INSERT, REPLACE etc.
update Event generated during execution of the table update queries, e.g. UPDATE and UPDATE ... WHERE.
delete Event generated during execution of the table delete queries, e.g. DELETE and TRUNCATE.

Event flow order is presented on the diagram below:
Events can be specified in the filter definition using the event JSON element. A filter that logs connect and disconnect events of connection class, as well as the insert event of the table_access class.

Event Fields

Event of the every class is generated with a certain set of information. Connection related events carry various user related information, such as a user name and the host name. Table access event is composed of a database name and the table name currently being accessed by a query. Full list of the available fields can be found here. Each field can be accessed using the field JSON element.
Defining a filter that logs only TCP/IP connections can be achieved by referring a connection_type field and comparing it against ::tcp/ip value (value JSON element):

A filter definition that logs insertions into an bank_account table combines table_access class, insert event and table_name field name:

The two filters presented above could be combined into a single JSON definition:

Logical Operators

Verification of the single field may be not enough. Comparison of a field against multiple values or comparison of multiple fields can be done thanks to and, or and not logical operators.
A filter that use or operator that allows logging of the incoming TCP/IP or Unix socket file connections:

Utilize and operator to log SSL connections from certain IP:

Slight modification of the filter and we can log SLL connections from other than loopback IP, thanks to not operator:

Default User

All filters created so far were bounded to the 'audit_log_user'@'%' account. Filter can be assigned to other accounts, not specified explicitly, by using a percentage sign (%) as the account argument. Logging all connections related events of all user accounts can be done in a following way:

Thanks to the default user (%) we can specify very useful filtering configurations. For example, we can enable incoming connection logging of all users (already done above), and disable logging of every activity of 'root' accounts:

We can swap the configuration and log 'root' account connections. This can be done by changing default_filter and root_filter definitions only, where account assignments remain unchanged:

Cleanup, Backup, Restore

audit_log_filter and audit_log_user tables are created in the mysql database during installation of the audit log filtering. Every modification in filtering configuration is reflected in these tables. We can also modify tables directly and then reload the configuration using the audit_log_filter_flush UDF. Thanks to this we can backup, restore or simply do a cleanup:

This strategy can be widely applied in writing the scripts that backup and restore the filtering configuration.

We hope that audit log filtering techniques, presented in this post, will help to create your own, useful audit filtering configuration.

As always, THANK YOU for using MySQL!

9 thoughts on “MySQL 5.7: New Audit Log Filtering Feature – Part 1

  1. This is good stuff – I’ve been waiting for table_access event classes to be supported! Early versions of Server 5.7 exposed the table_access class, but plugins which tried to register against that class were given an error, saying it was unsupported. I assume that has changed? If so, can you identify the specific version where these events become supported in Server 5.7?

    Are read and insert events part of the general or table_access class? The table shows them being part of the general class, while the state diagram shows them in the table_access class.

    1. Good to see your comment Todd! table_access events are fully supported starting from 5.7.11. read, insert, update and delete events are of table_access class. This can be confusing due to minimalist table CSS style.

  2. Nice function!! Thanks.

    By the way, I’m just wondering…..
    Both audit_log_filter and audit_log_user tables are using storage engine as MyISAM.
    Is there any reason for that?


    1. Thank you for your comment. Very good question, indeed. There is no reason of using MyISAM engine, as it was already discussed, just before the release. It is likely to be changed to InnoDB in the future.

  3. Do we need to register specific ‘user()’ for auditing?
    It seems current _user() doesn’t recognized by audit plugin.

    If I want to audit user for misc02, I need to register user by following command.

    ■ Target MySQL Server
    audit_target@localhost [mysql]> SELECT audit_log_filter_set_user(‘audit_target@misc02’, ‘log_all’);
    | audit_log_filter_set_user(‘audit_target@misc02’, ‘log_all’) |
    | OK |
    1 row in set (0.00 sec)

    audit_target@localhost [mysql]> select * from audit_log_user;
    | audit_target | % | log_all | <– This is not work.
    | audit_target | | log_all | <– This is not work.
    | audit_target | misc02 | log_all | select user(),current_user();
    | user() | current_user() |
    | audit_target@misc02 | audit_target@% |

    ■ Specific User Log is written in Audit.log.

    2016-06-16T01:28:45 UTC
    audit_target[audit_target] @ misc02 []

    select user(),current_user()

    Best Regard

    1. Unfortunately this is a bug. It has been already fixed, I hope to get it into the next maintenance release. Sorry for inconvenience.

      1. Marek-san,

        Thank you for your confirmation.
        I appreciate it.

        One more request….
        Please expand USER length in future release.

        root@localhost [mysql]> desc audit_log_user\G
        *************************** 1. row ***************************
        Field: USER
        Type: varchar(16)
        Null: NO
        Key: PRI
        Default: NULL
        MySQL user names can be up to 32 characters long (16 characters before MySQL 5.7.8).


Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.