MySQL Security – MySQL Enterprise Audit

April 4, 2018

When thinking about security within a MySQL installation, you should consider a wide range of possible procedures / best practices and how they affect the security of your MySQL server and related applications. MySQL provides many tools / features / plugins in order to protect your data including some advanced features like Transparent Data Encryption aka TDE, Data Masking & De-Identification, Firewall, Password Management, Password Validation Plugin, etc…

MySQL Security

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.

MySQL Enterprise Audit

MySQL Enterprise Edition includes MySQL Enterprise Audit which uses the open MySQL Audit API to enable standard, policy-based monitoring, logging, and blocking of connection and query activity executed on specific MySQL servers.

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.

MySQL Enterprise Audit

Installation

I’m using MySQL 5.7.21 Enterprise Edition :

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.

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

The Audit plugin installed a bunch of User-Defined Function (UDF) :

These functions are described here.

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 :

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 :

Then restart the MySQL server.

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.

For additional security, you can enable audit log file encryption.

Encryption

To encrypt your audit log file, the MySQL keyring must be enabled because audit logging uses it for password storage. Note that any keyring plugin can be used e.g. : file-based, encrypted file, OKV KMIP compatible, AWS, …

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.

More details here.

Let’s practice!

Keyring Plugin Installation : keyring_encrypted_file

Like I stated before many keyring plugins are available. I’ll use the keyring plugin : keyring_encrypted_file, that stores keyring data in an encrypted file local to the server host.

Warning
The keyring_encrypted_file plugin for encryption key management is not intended as a regulatory compliance solution. Security standards such as PCI, FIPS, and others require use of key management systems to secure, manage, and protect encryption keys in key vaults or hardware security modules (HSMs).

Update the MySQL configuration file and restart the MySQL server :

Warning
Because the my.cnf file stores a password when written as shown, it should have a restrictive mode and be accessible only to the account used to run the MySQL server.

Now the default name of Audit log file is audit.log.enc instead of audit.log.
And obviously this is not the only change. Guess what? this new file is… encrypted (AES-256-CBC cipher encryption)

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

Then restart the MySQL server

Compression

You can lowering the disk footprint of your audit logs with compression.

To control whether audit log file compression is enabled, set the audit_log_compression system variable at server startup.

Permitted values are NONE (no compression; the default) and GZIP (GNU Zip compression).

More details here.

Let’s practice!

Update the MySQL server configuration file :

Then restart the server :

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

Note
If 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.

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:

  • audit_log_filter_set_filter() : Define a filter
  • audit_log_filter_remove_filter() : Remove a filter
  • audit_log_filter_set_user() : Start filtering a user account
  • audit_log_filter_remove_user() : Stop filtering a user account
  • audit_log_filter_flush() : Flush manual changes to the filter tables to affect ongoing filtering

Detailed description here.

Examples

Users and Filters can be monitored respectively with mysql.audit_log_user and mysql.audit_log_filter tables

e.g.

Logging All Events

You can log all auditable events for all users by create a simple filter to enable logging and assign it to the default account:

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:

Note
Starting with MySQL 5.7.22, you can use JSON_PRETTY function that prints out a JSON value in a format that is easy to read.

Updated on 22nd of August 2018
For convenience, a JSON_PRETTY example with MySQL 8.0.12:

Remove a filter on a user account

I don’t want to audit all users anymore. Instead I’ll use the log_all filter to audit the user account olivier@%.

Note
Filtering of current sessions for the user remains unaffected. 

Assign a filter explicitly to a particular user account

The filter is already defined (log_all), let’s assign it to the olivier@% user account :

In another session connection with user olivier :

All commands from user olivier are audited :

Logging Specific Event Classes

Create a new filter : log_table_access_events

It allows to audit all table accesses. This filter is assigned to user olivier. So it replace any previous filter.

Connection with user olivier

Run a query on a table (table access)

This command is logged in Audit log file :

Other kinds of queries will not be logged :

The Audit log file did not changed

It’s also possible to select a specific event on these tables like INSERT, DELETE, UPDATE…

You’ll find all the details here.

Blocking Execution of Specific Events

Event items can include an abort item that indicates whether to prevent qualifying events from executing.

This is convenient  to block execution of specific SQL statements.

Let’s block INSERTs, UPDATEs and DELETEs on sakila.payment table :

When using user olivier :

Any forbidden queries will be blocked…

and logged by the MySQL Audit plugin.

And now Olivier is in big troubles!!! 🙂

MySQL Enterprise Audit plugin is very powerful!

Please read the documentation for more information.

Uninstallation

To remove MySQL Enterprise Audit, execute the following statements :

Voilà!

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.

MySQL Enterprise Edition server Trial Download (Note – Select Product Pack: MySQL Database).

MySQL Enterprise Edition

In order to go further

MySQL Security Series

  1. Password Validation Plugin
  2. Password Management
  3. User Account Locking
  4. The Connection-Control Plugins
  5. Enterprise Audit
  6. Enterprise Transparent Data Encryption (TDE)
  7. Enterprise Firewall
  8. Enterprise Data Masking and De-Identification

Reference Manual

MySQL Security

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

15 Responses to “MySQL Security – MySQL Enterprise Audit”

  1. […] MySQL Enterprise Audit […]

  2. […] Enterprise Audit […]

  3. […] Enterprise Audit […]

  4. […] Enterprise Audit […]

  5. […] tools / features / plugins in order to protect your data including some advanced features like Audit, TDE, Password Management, Password Validation Plugin, User Account Locking, […]

  6. […] tools / features / plugins in order to protect your data including some advanced features like  Audit,  Firewall, Password Management, Password Validation Plugin, User Account Locking, […]

  7. […] protect your data including some advanced features like Transparent Data Encryption aka TDE,  Audit, Firewall, Password Management, Password Validation Plugin, […]

  8. […] Enterprise Audit : Audit de la base de données afin de détecter une mauvaise utilisation et/ou de se conformer à la réglementation. […]

  9. […] to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Password Expiration Policy, Password Reuse Policy, […]

  10. […] to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Reuse Policy, […]

  11. […] to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration […]

  12. […] to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration […]

  13. […] to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration […]

  14. […] to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration […]

  15. Hello, although I set mysql audit as follows, all data is logged(select-insert-update-delete). However, I just want the deletion and update statements to be logged.
    Where am I making a mistake?
    Thank you from now.

    SELECT audit_log_filter_set_filter(‘test’, ‘{
    “filter”: {
    “class”: [
    {
    “name”: “connection”,
    “event”: [
    { “name”: “connect” },
    { “name”: “disconnect” }
    ]
    },
    { “name”: “general” },
    {
    “name”: “table_access”,
    “event”: [
    { “name”: “read”, “log”: false },
    { “name”: “insert”, “log”: false },
    { “name”: “delete”, “log”: true },
    { “name”: “update”, “log”: true }
    ]
    }
    ]
    }
    }’) AS ‘Result’;