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:
1 2 3 |
shell> mysql –user root < audit_log_filter_linux_install.sql Result OK |
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:
1 |
2016-05-13T10:55:06.483277Z 2 [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.' |
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:
1 2 3 4 5 6 7 8 9 |
mysql> CREATE USER 'audit_log_user'; mysql> GRANT ALL ON *.* TO 'audit_log_user'; mysql> SELECT user, host FROM mysql.user WHERE user = 'audit_log_user'; +----------------+------+ | user | host | +----------------+------+ | audit_log_user | % | +----------------+------+ 1 row in set (0.00 sec) |
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:
1 2 3 4 5 6 7 |
mysql> SELECT audit_log_filter_set_filter('all_enabled', '{ "filter": { "log": true } }') AS 'Result'; +--------+ | Result | +--------+ | OK | +--------+ 1 row in set (0.00 sec) |
This creates a filter named 'all_enabled'
and enables logging everything with a simple JSON:
1 |
{ "filter": { "log": true } } |
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:
1 |
mysql> SELECT audit_log_filter_set_user('audit_log_user@%', 'all_enabled') AS 'Result'; |
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:
1 2 3 4 5 6 |
shell> mysql –-user audit_log_user mysql> CREATE DATABASE audit_log_test_db; mysql> USE audit_log_test_db; mysql> CREATE TABLE audit_log_test_table (val INT); mysql> INSERT INTO audit_log_test_table VALUES(1); mysql> exit |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
<?xml version="1.0" encoding="UTF-8"?> <AUDIT> <AUDIT_RECORD> <NAME>Connect</NAME> <USER>audit_log_user</USER> <HOST>localhost</HOST> <COMMAND_CLASS>connect</COMMAND_CLASS> <CONNECTION_TYPE>SSL/TLS</CONNECTION_TYPE> <PRIV_USER>audit_log_user</PRIV_USER> </AUDIT_RECORD> <AUDIT_RECORD> <NAME>Query</NAME> <COMMAND_CLASS>create_db</COMMAND_CLASS> <SQLTEXT>CREATE DATABASE audit_log_test_db</SQLTEXT> </AUDIT_RECORD> <AUDIT_RECORD> <NAME>Init DB</NAME> <COMMAND_CLASS/> </AUDIT_RECORD> <AUDIT_RECORD> <NAME>Query</NAME> <COMMAND_CLASS>create_table</COMMAND_CLASS> <SQLTEXT>CREATE TABLE audit_log_test_table (val INT)</SQLTEXT> </AUDIT_RECORD> <AUDIT_RECORD> <NAME>TableInsert</NAME> <COMMAND_CLASS>insert</COMMAND_CLASS> <SQLTEXT>INSERT INTO audit_log_test_table VALUES(1)</SQLTEXT> <DB>audit_log_test_db</DB> <TABLE>audit_log_test_table</TABLE> </AUDIT_RECORD> <AUDIT_RECORD> <NAME>Query</NAME> <COMMAND_CLASS>insert</COMMAND_CLASS> <SQLTEXT>INSERT INTO audit_log_test_table VALUES(1)</SQLTEXT> </AUDIT_RECORD> <AUDIT_RECORD> <NAME>Quit</NAME> <USER>audit_log_user</USER> <COMMAND_CLASS>connect</COMMAND_CLASS> <CONNECTION_TYPE/> </AUDIT_RECORD> </AUDIT> |
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:
1 |
mysql> SELECT audit_log_filter_set_filter('all_disabled', '{ "filter": { "log": false } }') AS 'Result'; |
Followed by the assignment of the audit_log_user
to the newly created filter:
1 |
mysql> SELECT audit_log_filter_set_user('audit_log_user@%', 'all_disabled') AS 'Result'; |
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:
1 |
mysql> SELECT audit_log_filter_remove_user('audit_log_user@%') AS 'Result'; |
Another way is to remove the filter using the audit_log_filter_remove_filter
UDF:
1 |
mysql> SELECT audit_log_filter_remove_filter('all_disabled') AS 'Result'; |
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:
1 |
{ "filter": { "class": { "name": "connection", "log": true } } } |
Logging events of more than one class can be done by listing classes in the JSON array element:
1 2 3 |
{ "filter": { "class": [ { "name": "connection", "log": true }, { "name": "general", "log": true }, { "name": "table_access", "log": true } ] } } |
For readability purposes, the "log": true
element can be omitted. It is always assumed that the element is always logged, when explicitly specified:
1 2 3 |
{ "filter": { "class": [ { "name": "connection" }, { "name": "general" }, { "name": "table_access" } ] } } |
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.
1 2 3 4 |
{ "filter": { "class": [ { "name": "connection", "event": { "name": [ "connect", "disconnect" ] } }, { "name": "table_access", "event": { "name": "insert" } } ] } } |
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):
1 2 3 4 |
{ "filter": { "class": { "name": "connection", "event": { "name": "connect", "log": { "field": { "name": "connection_type", "value": "::tcp/ip" } } } } } } |
A filter definition that logs insertions into an bank_account
table combines table_access
class, insert
event and table_name
field name:
1 2 3 4 |
{ "filter": { "class": { "name": "table_access", "event": { "name": "insert", "log": { "field": { "name": "table_name.str", "value": "bank_account" } } } } } } |
The two filters presented above could be combined into a single JSON definition:
1 2 3 4 5 6 7 8 |
{ "filter": { "class": [ { "name": "connection", "event": { "name": "connect", "log": { "field": { "name": "connection_type", "value": "::tcp/ip" } } } }, { "name": "table_access", "event": { "name": "insert", "log": { "field": { "name": "table_name.str", "value": "bank_account" } } } } ] } } |
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:
1 2 3 4 5 6 |
{ "filter": { "class": { "name": "connection", "event": { "name": "connect", "log": { "or": [ { "field": { "name": "connection_type", "value": "::tcp/ip" } }, { "field": { "name": "connection_type", "value": "::socket" } } ] } } } } } |
Utilize and
operator to log SSL connections from certain IP:
1 2 3 4 5 6 |
{ "filter": { "class": { "name": "connection", "event": { "name": "connect", "log": { "and": [ { "field": { "name": "connection_type", "value": "::ssl" } }, { "field": { "name": "ip.str", "value": "127.0.0.1" } } ] } } } } } |
Slight modification of the filter and we can log SLL connections from other than loopback IP, thanks to not
operator:
1 2 3 4 5 6 |
{ "filter": { "class": { "name": "connection", "event": { "name": "connect", "log": { "and": [ { "field": { "name": "connection_type", "value": "::ssl" } }, { "not": { "field": { "name": "ip.str", "value": "127.0.0.1" } } } ] } } } } } |
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:
1 2 |
mysql> SELECT audit_log_filter_set_filter('default_filter', '{ "filter": { "class": { "name": "connection" } } }') AS 'Result'; mysql> SELECT audit_log_filter_set_user('%', 'default_filter') AS 'Result'; |
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:
1 2 3 4 |
mysql> SELECT audit_log_filter_set_filter('root_filter', '{ "filter": { "log": false } }') AS 'Result'; mysql> SELECT audit_log_filter_set_user('root@127.0.0.1', 'root_filter') AS 'Result'; mysql> SELECT audit_log_filter_set_user('root@::1', 'root_filter') AS 'Result'; mysql> SELECT audit_log_filter_set_user('root@localhost', 'root_filter') AS 'Result'; |
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:
1 2 |
mysql> SELECT audit_log_filter_set_filter('root_filter', '{ "filter": { "class": { "name": "connection" } } }') AS 'Result'; mysql> SELECT audit_log_filter_set_filter('default_filter', '{ "filter": { "log": false } }') AS 'Result'; |
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:
1 2 3 4 |
mysql> USE mysql; mysql> DELETE FROM audit_log_filter; mysql> DELETE FROM audit_log_user; mysql> SELECT audit_log_filter_flush() AS ‘Result’; |
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!
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.
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.
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?
Regard.
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.
The if Linux then..if Windows then.. reminds me of this RFE:
https://bugs.mysql.com/bug.php?id=73802
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;
+————–+—————-+————+
| USER | HOST | FILTERNAME |
+————–+—————-+————+
| audit_target | % | log_all | <– This is not work.
| audit_target | 192.168.56.109 | 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
51353_2016-06-16T01:15:44
Query
13
0
0
audit_target[audit_target] @ misc02 [192.168.56.109]
misc02
192.168.56.109
select
select user(),current_user()
Best Regard
Unfortunately this is a bug. It has been already fixed, I hope to get it into the next maintenance release. Sorry for inconvenience.
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
http://dev.mysql.com/doc/refman/5.7/en/user-names.html
MySQL user names can be up to 32 characters long (16 characters before MySQL 5.7.8).
Regard
Shinya
Only for enterprise edition?