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 :

SELECT VERSION();
+-------------------------------------------+
| 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.

$ mysql -u root -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

mysql> 
SELECT PLUGIN_NAME, PLUGIN_STATUS 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME LIKE 'audit%';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| audit_log   | ACTIVE        |
+-------------+---------------+
mysql> 
SHOW PLUGINS\G
... [snip] ...
*************************** 45. row ***************************
   Name: audit_log
 Status: ACTIVE
   Type: AUDIT
Library: audit_log.so
License: PROPRIETARY

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

mysql> 
-- Display all the UDFs installed by the Audit plugin
SELECT * FROM  mysql.func;
+-----------------------------------+-----+--------------+----------+
| name                              | ret | dl           | type     |
+-----------------------------------+-----+--------------+----------+
| audit_log_filter_set_filter       |   0 | audit_log.so | function |
| audit_log_filter_remove_filter    |   0 | audit_log.so | function |
| audit_log_filter_set_user         |   0 | audit_log.so | function |
| audit_log_filter_remove_user      |   0 | audit_log.so | function |
| audit_log_filter_flush            |   0 | audit_log.so | function |
| audit_log_read_bookmark           |   0 | audit_log.so | function |
| audit_log_read                    |   0 | audit_log.so | function |
| audit_log_encryption_password_set |   2 | audit_log.so | function |
| audit_log_encryption_password_get |   0 | audit_log.so | function |
+-----------------------------------+-----+--------------+----------+

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 :

mysql> 
SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
$ 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 :

[mysqld]
audit_log=FORCE_PLUS_PERMANENT
audit_log_format=JSON
audit_log_rotate_on_size=1048576

Then restart the MySQL server.

$ 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.

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 :

[mysqld]
audit_log=FORCE_PLUS_PERMANENT
audit_log_format=JSON
audit_log_rotate_on_size=1048576

audit_log_encryption=AES

early-plugin-load=keyring_encrypted_file.so
keyring_encrypted_file_data=/var/lib/mysql/keyring/keyring-encrypted
keyring_encrypted_file_password=myS3cureP4ssw%rd

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.

mysql> 
SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_LIBRARY, PLUGIN_DESCRIPTION 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME = 'keyring_encrypted_file'\G
*************************** 1. row ***************************
       PLUGIN_NAME: keyring_encrypted_file
     PLUGIN_STATUS: ACTIVE
    PLUGIN_LIBRARY: keyring_encrypted_file.so
PLUGIN_DESCRIPTION: store/fetch authentication data to/from an encrypted file

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)

$ strings  '/var/lib/mysql/audit.log.enc' 
Salted__
9,]`<Lrc}P
d.Zw
!U\?
v_l%
Oq}{/
d_qa
S7C0
...

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.

[mysqld]
audit_log_encryption=NONE

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!

SHOW VARIABLES LIKE 'audit_log_compression';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| audit_log_compression | NONE  |
+-----------------------+-------+

Update the MySQL server configuration file :

[mysqld]
audit_log=FORCE_PLUS_PERMANENT
audit_log_format=JSON
audit_log_rotate_on_size=1048576

audit_log_compression=GZIP

Then restart the server :

SHOW VARIABLES LIKE 'audit_log_compression';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| audit_log_compression | GZIP  |
+-----------------------+-------+

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

$ ls -l audit.*
... 832 Apr  3 14:38 audit.20180403T123833.log.enc
... 803 Apr  3 14:49 audit.20180403T124958.log
...  20 Apr  3 14:50 audit.log.gz

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.

[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:

  • 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.

mysql> 
SELECT * FROM mysql.audit_log_user\G
*************************** 1. row ***************************
      USER: %
      HOST: 
FILTERNAME: log_all


SELECT * FROM mysql.audit_log_filter\G
*************************** 1. row ***************************
  NAME: log_all
FILTER: {"filter": {"log": true}}

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:

mysql>
SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }');
+-------------------------------------------------------------------------+
| audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }') |
+-------------------------------------------------------------------------+
| OK                                                                      |
+-------------------------------------------------------------------------+


SELECT audit_log_filter_set_user('%', 'log_all');
+-------------------------------------------+
| audit_log_filter_set_user('%', 'log_all') |
+-------------------------------------------+
| OK                                        |
+-------------------------------------------+

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> 
-- Check if a filter has been assigned to the current session
SELECT @@audit_log_filter_id;
+-----------------------+
| @@audit_log_filter_id |
+-----------------------+
|                     0 |
+-----------------------+

-- Re-connection
connect
Connection id:    7
Current database: *** NONE ***

SELECT @@audit_log_filter_id;
+-----------------------+
| @@audit_log_filter_id |
+-----------------------+
|                     1 |
+-----------------------+


-- Execute a query of your choice e.g.
SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| drupal             |
... [snip] ...


-- Reads the most recently written audit log event from the audit log
SELECT audit_log_read(audit_log_read_bookmark())\G
*************************** 1. row ***************************
audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 15:34:11","id":0,"class":"general","event":"status","connection_id":7,"account":{"user":"root","host":"localhost"},"login":{"user":"root","os":"","ip":"","proxy":""},"general_data":{"command":"Query","sql_command":"show_databases","query":"SHOW SCHEMAS","status":0}}, null ]

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:

mysql>
SELECT VERSION();
+-------------------+
| VERSION()         |
+-------------------+
| 8.0.12-commercial |
+-------------------+


SELECT JSON_PRETTY(CONVERT(audit_log_read(audit_log_read_bookmark()) using utf8mb4))\G
*************************** 1. row ***************************
JSON_PRETTY(CONVERT(audit_log_read(audit_log_read_bookmark()) using utf8mb4)): [
  {
    "id": 0,
    "class": "general",
    "event": "status",
    "login": {
      "ip": "",
      "os": "",
      "user": "msandbox",
      "proxy": ""
    },
    "account": {
      "host": "localhost",
      "user": "msandbox"
    },
    "timestamp": "2018-08-22 13:19:54",
    "general_data": {
      "query": "SELECT CONVERT(audit_log_read(audit_log_read_bookmark()), JSON)",
      "status": 0,
      "command": "Query",
      "sql_command": "select"
    },
    "connection_id": 12
  },
  null
]

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@%.

-- Stop auditing all users 
SELECT audit_log_filter_remove_user('%');
+-----------------------------------+
| audit_log_filter_remove_user('%') |
+-----------------------------------+
| OK                                |
+-----------------------------------+


SELECT audit_log_read(audit_log_read_bookmark())\G
*************************** 1. row ***************************
audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 15:48:08","id":0,"class":"general","event":"status","connection_id":7,"account":{"user":"root","host":"localhost"},"login":{"user":"root","os":"","ip":"","proxy":""},"general_data":{"command":"Query","sql_command":"select","query":"SELECT audit_log_filter_remove_user('%')","status":0}}, null ]


-- Audit still enabled for the current session
SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+


SELECT audit_log_read(audit_log_read_bookmark())\G
*************************** 1. row ***************************
audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 15:50:42","id":0,"class":"general","event":"status","connection_id":7,"account":{"user":"root","host":"localhost"},"login":{"user":"root","os":"","ip":"","proxy":""},"general_data":{"command":"Query","sql_command":"select","query":"SELECT 1","status":0}}, null ]


-- Reset the session
connect
Connection id:    10
Current database: *** NONE ***


SELECT 2;
+---+
| 2 |
+---+
| 2 |
+---+

-- No more entries in the audit log
SELECT audit_log_read(audit_log_read_bookmark())\G
*************************** 1. row ***************************
audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 15:50:49","id":0,"class":"connection","event":"disconnect","connection_id":7,"account":{"user":"root","host":"localhost"},"login":{"user":"root","os":"","ip":"","proxy":""},"connection_data":{"connection_type":"socket"}}, null ]

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 :

mysql_root> 
SELECT audit_log_filter_set_user('olivier@%', 'log_all');
+---------------------------------------------------+
| audit_log_filter_set_user('olivier@%', 'log_all') |
+---------------------------------------------------+
| OK                                                |
+---------------------------------------------------+

In another session connection with user olivier :

$ mysql -uolivier -p

All commands from user olivier are audited :

mysql_root>
-- Root session
SELECT audit_log_read(audit_log_read_bookmark())\G
*************************** 1. row ***************************
audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 16:01:09","id":0,"class":"connection","event":"connect","connection_id":15,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"connection_data":{"connection_type":"socket","status":0,"db":""}}, {"timestamp":"2018-04-03 16:01:09","id":1,"class":"general","event":"status","connection_id":15,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"general_data":{"command":"Query","sql_command":"select","query":"select @@version_comment limit 1","status":0}}, {"timestamp":"2018-04-03 16:01:09","id":2,"class":"general","event":"status","connection_id":15,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"general_data":{"command":"Query","sql_command":"select","query":"select USER()","status":0}}, null ]


-- Olivier session
mysql_olivier>
SELECT 'Olivier';
+---------+
| Olivier |
+---------+
| Olivier |
+---------+


-- Back to root session
mysql_root>
SELECT audit_log_read(audit_log_read_bookmark())\G
*************************** 1. row ***************************
audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 16:17:17","id":0,"class":"general","event":"status","connection_id":15,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"general_data":{"command":"Query","sql_command":"select","query":"SELECT 'Olivier'","status":0}}, null ]

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.

mysql_root>
SELECT audit_log_filter_set_filter('log_table_access_events', '{ "filter": { "class": { "name": "table_access" } } }');
+-----------------------------------------------------------------------------------------------------------------+
| audit_log_filter_set_filter('log_table_access_events', '{ "filter": { "class": { "name": "table_access" } } }') |
+-----------------------------------------------------------------------------------------------------------------+
| OK                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------+

SELECT audit_log_filter_set_user('olivier@%', 'log_table_access_events');
+-------------------------------------------------------------------+
| audit_log_filter_set_user('olivier@%', 'log_table_access_events') |
+-------------------------------------------------------------------+
| OK                                                                |
+-------------------------------------------------------------------+

SELECT * FROM mysql.audit_log_user WHERE USER='olivier'\G
*************************** 1. row ***************************
USER: olivier
HOST: %
FILTERNAME: log_table_access_events


SELECT * FROM mysql.audit_log_filter WHERE NAME='log_table_access_events'\G
*************************** 1. row ***************************
NAME: log_table_access_events
FILTER: {"filter": {"class": {"name": "table_access"}}}

Connection with user olivier

$ mysql -u olivier -p

Run a query on a table (table access)

-- Olivier's session
mysql_olivier>
SELECT * FROM actor LIMIT 1\G
*************************** 1. row ***************************
actor_id: 1
first_name: PENELOPE
last_name: GUINESS
last_update: 2006-02-15 04:34:33

This command is logged in Audit log file :

mysql_root>
SELECT audit_log_read(audit_log_read_bookmark())\G
*************************** 1. row ***************************
audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 17:03:03","id":0,"class":"table_access","event":"read","connection_id":25,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"table_access_data":{"db":"sakila","table":"actor","query":"SELECT * FROM actor LIMIT 1","sql_command":"select"}}, null ]

Other kinds of queries will not be logged :

-- Olivier's session
mysql_olivier>
CREATE SCHEMA s1;
Query OK, 1 row affected (0,01 sec)

The Audit log file did not changed

mysql_root>
SELECT audit_log_read(audit_log_read_bookmark())\G
*************************** 1. row ***************************
audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 17:03:03","id":0,"class":"table_access","event":"read","connection_id":25,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"table_access_data":{"db":"sakila","table":"actor","query":"SELECT * FROM actor LIMIT 1","sql_command":"select"}}, null ]

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 :

mysql_root>
-- This filter blocks INSERT, UPDATE, and DELETE statements only for sakila.payment table
SET @filter = '{"filter": {"class": {"name": "table_access","event": {"name": [ "insert", "update", "delete" ],"abort": {"and": [{ "field": { "name": "table_database.str", "value": "sakila" } }, { "field": { "name": "table_name.str", "value": "payment" } }]}}}}}';
Query OK, 0 rows affected (0,00 sec)


SELECT audit_log_filter_set_filter('block_writes_on_table_payment_events', @filter);
+------------------------------------------------------------------------------+
| audit_log_filter_set_filter('block_writes_on_table_payment_events', @filter) |
+------------------------------------------------------------------------------+
| OK                                                                           |
+------------------------------------------------------------------------------+

SELECT audit_log_filter_set_user('olivier@%', 'block_writes_on_table_payment_events');
+--------------------------------------------------------------------------------+
| audit_log_filter_set_user('olivier@%', 'block_writes_on_table_payment_events') |
+--------------------------------------------------------------------------------+
| OK                                                                             |
+--------------------------------------------------------------------------------+

SELECT * FROM mysql.audit_log_user WHERE USER='olivier'\G
*************************** 1. row ***************************
USER: olivier
HOST: %
FILTERNAME: block_writes_on_table_payment_events


SELECT * FROM mysql.audit_log_filter WHERE NAME='block_writes_on_table_payment_events'\G
*************************** 1. row ***************************
NAME: block_writes_on_table_payment_events
FILTER: {"filter": {"class": {"name": "table_access", "event": {"name": ["insert", "update", "delete"], "abort": {"and": [{"field": {"name": "table_database.str", "value": "sakila"}}, {"field": {"name": "table_name.str", "value": "payment"}}]}}}}}

When using user olivier :

# Connection with user olivier
$ mysql -u olivier -p

Any forbidden queries will be blocked…

mysql_olivier>
SELECT COUNT(*) FROM sakila.payment;
+----------+
| COUNT(*) |
+----------+
|    16049 |
+----------+


-- MySQL Audit plugin prevents writes on sakila.payment
DELETE FROM sakila.payment;
ERROR 1045 (28000): Statement was aborted by an audit log filter


SELECT COUNT(*) FROM sakila.payment;
+----------+
| COUNT(*) |
+----------+
|    16049 |
+----------+

and logged by the MySQL Audit plugin.

mysql_root> 
SELECT audit_log_read(audit_log_read_bookmark())\G
*************************** 1. row ***************************
audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 17:29:22","id":0,"class":"table_access","event":"delete","connection_id":28,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"table_access_data":{"db":"sakila","table":"payment","query":"DELETE FROM sakila.payment","sql_command":"delete"}}, null ]
$ tail /var/lib/mysql/audit.log
{ "timestamp": "2018-04-03 17:28:00", "id": 0, "class": "table_access", "event": "read", "connection_id": 25, "account": { "user": "olivier", "host": "localhost" }, "login": { "user": "olivier", "os": "", "ip": "", "proxy": "" }, "table_access_data": { "db": "sakila", "table": "payment", "query": "SELECT COUNT(*) FROM sakila.payment", "sql_command": "select" } },
{ "timestamp": "2018-04-03 17:29:22", "id": 0, "class": "table_access", "event": "delete", "connection_id": 28, "account": { "user": "olivier", "host": "localhost" }, "login": { "user": "olivier", "os": "", "ip": "", "proxy": "" }, "table_access_data": { "db": "sakila", "table": "payment", "query": "DELETE FROM sakila.payment", "sql_command": "delete" } }

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 :

DROP TABLE IF EXISTS mysql.audit_log_filter;
DROP TABLE IF EXISTS mysql.audit_log_user;
UNINSTALL PLUGIN audit_log;
DROP FUNCTION audit_log_filter_set_filter;
DROP FUNCTION audit_log_filter_remove_filter;
DROP FUNCTION audit_log_filter_set_user;
DROP FUNCTION audit_log_filter_remove_user;
DROP FUNCTION audit_log_filter_flush;
DROP FUNCTION audit_log_encryption_password_get;
DROP FUNCTION audit_log_encryption_password_set;
DROP FUNCTION audit_log_read;
DROP FUNCTION audit_log_read_bookmark;

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 Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & 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’;