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!