Here I have been looking into using the MySQL Enterprise Edition Audit Log plugin for 5.7. We have many options to audit (filters, encryption, compression, Workbench, rotation & purging, viewing the log, etc.) and it’s quite clear cut on what we’re auditing and not when active.

If you’re looking to go deep into the Audit Plugin, as part of the Enterprise Edition, you’ll want to look at the following Support note:

Master Note for MySQL Enterprise Audit Log Plugin (Doc ID 2299419.1)

And if you’re looking for other Audit Plugin examples, I’d recommend Tony Darnell’s blog post:

https://scriptingmysql.wordpress.com/2014/03/14/installing-and-testing-the-mysql-enterprise-audit-plugin/

Install

Venturing onwards, have a read of the install (or upgrade) steps:

https://dev.mysql.com/doc/refman/8.0/en/audit-log-installation.html

and then what a “filter”is:

https://dev.mysql.com/doc/refman/8.0/en/audit-log-filtering.html

That said, although I started with a new install, it’s more than likely you won’t. So let’s install the plugin accordingly.

Remember, this is the Audit Plugin only available with the Enterprise Edition binaries. So we will need to download the MySQL Server from http://edelivery.oracle.com or from http://support.oracle.com, “Patches & Updates”.

Prepare the env

mkdir -p /opt/mysql/audit

ls -lrt /usr/local/mysql/mysql-advanced-5.7.18-linux-glibc2.5-x86_64
ls -lrt /usr/local/mysql/mysql-commercial-8.0.12-linux-glibc2.12-x86_64

(we’ll use the 8.0.12 binaries later)

cd /usr/local/mysql/mysql-advanced-5.7.18-linux-glibc2.5-x86_64

Edit the my.cnf commenting out the audit log params which we will use later.

vi my_audit.cnf
..
port=3357
..
[mysqld]
#plugin-load =audit_log.so
#audit-log =FORCE_PLUS_PERMANENT
..
basedir =/usr/local/mysql/mysql-advanced-5.7.18-linux-glibc2.5-x86_64
..

Initialize & startup

bin/mysqld --defaults-file=my_audit.cnf --initialize-insecure

Yes, using –initialize-insecure defeats the whole object of auditing, but here we’re testing. I expect the environment you’ll be using has already some minimum security in place.

bin/mysqld --defaults-file=my_audit.cnf &

bin/mysql --defaults-file=my_audit.cnf -uroot

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'audit%';

SELECT @@audit_log_filter_id;

To install the audit log plugin, we have to run:

bin/mysql -uroot -S /opt/mysql/audit/mysql_audit.sock < /usr/local/mysql/mysql-advanced-5.7.18-linux-glibc2.5-x86_64/share/audit_log_filter_linux_install.sql
Result
OK

Now in another window:

tail -100f /opt/mysql/audit/data/audit.log

Confirm which version has the audit_log tables in InnoDB or MyISAM (latter won’t work on GR/IdC for obvious reasons):
– 5.7.18 -> MyISAM
– 8.0.12 -> InnoDB

bin/mysql -uroot -S /opt/mysql/audit/mysql_audit.sock
show create table mysql.audit_log_user;

Auditing time has come

Check if any user account is being audited:

SELECT * from mysql.audit_log_user;

And what filter, if any, is active:

SELECT @@audit_log_filter_id;

As we haven’t created anything yet, it’s all empty.

Now to create a user to audit (Thanks Tony Darnell!):

CREATE USER 'audit_test_user'@'localhost' IDENTIFIED BY 'audittest123';
GRANT ALL PRIVILEGES ON *.* TO 'audit_test_user'@'localhost';

Create an audit filter to log only the connections of the previously created user:
either

SELECT audit_log_filter_set_filter('log_connection', '{ "filter": { "class": { "name": "connection" } } }');

or

SELECT audit_log_filter_set_filter('log_connection', '{ "filter": { "log": false ,"class": { "log": true, "name": "connection" } }}');

And assign the filter just created to the user account we want to audit:

SELECT audit_log_filter_set_user('audit_test_user@localhost', 'log_connection');

Make sure that all auditing changes have been committed and set in proverbial stone:

SELECT audit_log_filter_flush()\G

So what filter did we create or do we have?

SELECT * from mysql.audit_log_filter;

Now login with that user and run some SQL, whilst another window has a tail -100f running on the audit.log:

bin/mysql -uaudit_test_user -paudittest123 -S /opt/mysql/audit/mysql_audit.sock

SELECT @@audit_log_filter_id;
SELECT * from mysql.audit_log_user;

Now exit and reconnect to see in the tail of the audit.log the disconnect & connect.
In the window with a “tail -100f audit.log” we will only see:

<AUDIT_RECORD>
<TIMESTAMP>2018-08-21T14:58:34 UTC</TIMESTAMP>
<RECORD_ID>2_2018-08-21T14:50:37</RECORD_ID>
<NAME>Connect</NAME>
<CONNECTION_ID>6</CONNECTION_ID>
<STATUS>0</STATUS>
<STATUS_CODE>0</STATUS_CODE>
<USER>audit_test_user</USER>
<OS_LOGIN/>
<HOST>localhost</HOST>
<IP/>
<COMMAND_CLASS>connect</COMMAND_CLASS>
<CONNECTION_TYPE>Socket</CONNECTION_TYPE>
<PRIV_USER>audit_test_user</PRIV_USER>
<PROXY_USER/>
<DB/>
</AUDIT_RECORD>

but no sql being audited.

Let’s create a filter just for sql queries, without logging connections as we already how to create a filter for that:

SELECT audit_log_filter_set_filter('log_sql', '{ "filter": { "log": true ,"class": { "log": false, "name": "connection" } }}');
SELECT audit_log_filter_set_user('audit_test_user@localhost', 'log_sql');
SELECT audit_log_filter_flush()\G

Run some selects on any table to view the result in the audit.log.

Now activate logging of I/U/D but not for Selects / Reads:

SELECT audit_log_filter_set_filter('log_IUD', '{
  "filter": {
    "class": {
      "name": "table_access",
        "event": {
          "name": [ "insert", "update", "delete" ]
        }
    }
  }
 }');

Lets apply it to the user:

SELECT audit_log_filter_set_user('audit_test_user@localhost', 'log_IUD');
SELECT audit_log_filter_flush()\G

Let’s confirm the user has the new filter applied:

SELECT * from mysql.audit_log_user;

Let’s create a table and test some I/U/D:

create database nexus;
use nexus;
create table replicant (
`First name` varchar(40) not null default '',
`Last name` varchar(40) not null default '',
`Replicant` enum('Yes','No') not null default 'Yes'
) engine=InnoDB row_format=COMPACT;
INSERT INTO `replicant` (`First name`,`Last name`,`Replicant`)
VALUES
('Roy','Hauer','Yes'),
('Rutger','Batty','Yes'),
('Voight','Kampff','Yes'),
('Pris','Hannah','Yes'),
('Daryl','Stratton','Yes'),
('Rachael','Young','Yes'),
('Sean','Tyrell','Yes'),
('Rick','Ford','No'),
('Harrison','Deckard','Yes');

DELETE FROM replicant where `First name`='Rick';
UPDATE replicant set `Replicant` = 'No' where `First name` = 'Harrison';
INSERT INTO replicant (`First name`,`Last name`,`Replicant`) VALUES ('Rick','Ford','No');
UPDATE replicant set `Replicant` = 'Yes' where `First name` = 'Harrison';

Create a filter for both login connections & I/U/D actions:

SELECT audit_log_filter_set_filter('log_connIUD', '{
  "filter": {
    "class": [
      {"name": "connection" },
      {"name": "table_access",
         "event": {
           "name": [ "insert", "update", "delete" ]
         }
      }
    ]
  }
 }');

Apply it / make it stick and then confirm:

SELECT audit_log_filter_set_user('audit_test_user@localhost', 'log_connIUD');
SELECT audit_log_filter_flush()\G
SELECT * from mysql.audit_log_user;

Now re-run the I/U/D & exit/connect and view the audit.log.

Upon assigning a filter to a specific account (user+host),  the previous filter is automatically replaced.
So let’s apply the log_connection filter to all users, i.e. “%”:

SELECT audit_log_filter_set_filter('log_connection', '{ "filter": { "class": { "name": "connection" } } }');
SELECT audit_log_filter_set_user('%', 'log_connection');
SELECT audit_log_filter_flush()\G
SELECT * from mysql.audit_log_user;

Although we have just assigned the log_connection filter to all users, the audit_test_user has the log_IUD filter assigned specifically, which means that no logins for this user are being recorded in the audit.log. We would have to use the log_connIUD filter for that.

So maybe we don’t want to log anything for the root user. So we can remove logging of root from the log_connection filter

SELECT audit_log_filter_remove_user('root@localhost');
SELECT audit_log_filter_flush()\G
SELECT * from mysql.audit_log_user;

If we log off and log back on we’ll observe that the root user is removed just for that session. Logging is enabled again via the generic filter for the root user once logged on again.

Given that the previous is only per session, we’ll now create a “log_nothing” filter and apply it to the user accounts that we don’t want anthing to be logged:

SELECT audit_log_filter_set_filter('log_nothing', '{ "filter": { "log": false } }');
SELECT audit_log_filter_set_user('root@localhost', 'log_nothing');
SELECT audit_log_filter_flush()\G
SELECT * from mysql.audit_log_user;

Try logging on:

mysql -uroot -S /opt/mysql/audit/mysql_audit.sock

and view the audit.log tail output. “root” is no longer being logged.

I hope this has helped give an insight into some examples of how to audit MySQL. There are many many more examples, i.e. no logging for DDL, logging just specific tables and/or schemas. It is entirely up to you what you log… or not.

Happy auditing!