In order to spot database misuse and/or to prove compliance to popular regulations including GDPR, PCI DSS, HIPAA, … database administrators can be required to record and audit database activities. In this fifth episode of the MySQL Security series, we will see what MySQL Enterprise Audit provides to help organizations implement stronger security controls and satisfy regulatory compliance.

Audit plugin enables MySQL Server to produce a XML (default) or JSON log file (named audit.log) containing an audit record of server activity. The log contents include when clients connect and disconnect, and what actions they perform while connected, such as which databases and tables they access.

Installation

I’m using MySQL 5.7.21 Enterprise Edition :

MySQL Enterprise version

MySQL

1

2

3

4

5

6

SELECTVERSION();

+-------------------------------------------+

|VERSION()|

+-------------------------------------------+

|5.7.21-enterprise-commercial-advanced-log|

+-------------------------------------------+

Updated on 22nd of August 2018
Note: MySQL Enterprise Audit works with MySQL 8.0 as well. In other words examples below could be done with MySQL 8.0.12+

To install MySQL Enterprise Audit, look in the share directory of your MySQL installation and choose the script that is appropriate for your platform :

– audit_log_filter_win_install.sql : Choose this script for Windows systems that use .dll as the file name suffix.

– audit_log_filter_linux_install.sql : Choose this script for Linux and similar systems that use .so as the file name suffix.

Then run the script :

e.g.

Install MySQL Enterprise Audit

Shell

1

$mysql-uroot-p<audit_log_filter_linux_install.sql

You can verify the plugin installation examining the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement

The Audit plugin enables MySQL Server to produce a log file based in the datadir and named audit.log. By default, its contents is written in XML format, without compression nor encryption :

Looking for the MySQL data dir

MySQL

1

2

3

4

5

6

7

mysql>

SHOWVARIABLESLIKE'datadir';

+---------------+-----------------+

|Variable_name|Value|

+---------------+-----------------+

|datadir|/var/lib/mysql/|

+---------------+-----------------+

Audit log content

XHTML

1

2

3

4

5

6

7

8

9

10

11

12

13

$cat/var/lib/mysql/audit.log

<?xml version="1.0"encoding="UTF-8"?>

<AUDIT>

<AUDIT_RECORD>

<TIMESTAMP>2018-03-28T13:42:01 UTC</TIMESTAMP>

<RECORD_ID>1_2018-03-28T13:42:01</RECORD_ID>

<NAME>Audit</NAME>

<SERVER_ID>0</SERVER_ID>

<VERSION>1</VERSION>

<STARTUP_OPTIONS>mysqld</STARTUP_OPTIONS>

<OS_VERSION>x86_64-linux-glibc2.12</OS_VERSION>

<MYSQL_VERSION>5.7.21-enterprise-commercial-advanced</MYSQL_VERSION>

</AUDIT_RECORD>

Configuration

After MySQL Enterprise Audit is installed, you can use the audit_log option for subsequent server startups to control the Audit plugin activation.

e.g. Prevent the plugin from being removed at runtime, select JSON as logging format and force log file rotation when it reaches 1MB :

Audit log extra configuration

Vim

1

2

3

4

[mysqld]

audit_log=FORCE_PLUS_PERMANENT

audit_log_format=JSON

audit_log_rotate_on_size=1048576

Then restart the MySQL server.

JSON Audit log content

JavaScript

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

$cat/var/lib/mysql/audit.log

[

{

"timestamp":"2018-03-28 14:54:27",

"id":0,

"class":"audit",

"event":"startup",

"connection_id":0,

"startup_data":{

"server_id":0,

"os_version":"x86_64-linux-glibc2.12",

"mysql_version":"5.7.21-enterprise-commercial-advanced",

"args":[

"mysqld"

]

}

}

We have now a JSON format audit log file.

As you can see, by default, contents of audit log files produced by the audit log plugin are not encrypted and may contain sensitive information, such as the text of SQL statements. For security reasons, audit log files should be written to a directory accessible only to the MySQL server and to users with a legitimate reason to view the log.

To control whether audit log file encryption is enabled, set the audit_log_encryption system variable at server startup. Permitted values are NONE (no encryption; the default) and AES (AES-256-CBC cipher encryption).

To set or get the encryption password, use these user-defined functions (UDFs):

To set the encryption password, invoke audit_log_encryption_password_set(), which stores the password in the keyring, renames the current log file, and begins a new log file encrypted with the new password.

To get the current encryption password, invoke audit_log_encryption_password_get(), which retrieves the password from the keyring.

Now the default name of Audit log file is audit.log.gz instead of audit.log.
This new file is compressed with GNU Zip.

Different audit log formats

MySQL

1

2

3

4

$ls-laudit.*

...832Apr314:38audit.20180403T123833.log.enc

...803Apr314:49audit.20180403T124958.log

...20Apr314:50audit.log.gz

NoteIf both compression and encryption are enabled, compression occurs before encryption. To recover the original file manually, first decrypt it, then uncompress it.

If you don’t want to use compression anymore you’ll need to update audit_log_compression option in your MySQL configuration file.
e.g.

audit_log_compression=NONE

Vim

1

2

[mysqld]

audit_log_compression=NONE

Then restart the MySQL server

Audit Log Filtering

Another amazing feature is the audit log filtering functions. It enables filtering control in JSON format by providing an interface to create, modify, and remove filter definitions and assign filters to user accounts.

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.

By default, no accounts have a filter assigned, so no processing of auditable events occurs for any account.

The following list briefly summarizes the UDFs that implement the SQL interface for audit filtering control:

The filter assigned to % is used for connections from any account that has no explicitly assigned filter (which initially is true for all accounts).

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:

MySQL Enterprise Edition

MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime.

It reduces the risk, cost, and complexity in developing, deploying, and managing business-critical MySQL applications.