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
andaudit_log_user
tables in themysql
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!