Get the Auditors in: MySQL Enterprise Audit.

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!

This entry was posted in InnoDB, MySQL, MySQL Enterprise Edition, Oracle, Uncategorized, upgrade and tagged , , , , , , . Bookmark the permalink.

4 Responses to Get the Auditors in: MySQL Enterprise Audit.

  1. Pingback: Upgrading MySQL to 8.0.12 with Audit plugin. | MySQL-Med

  2. Pingback: Upgrading MySQL to 8.0.12 with Audit plugin.

  3. naresh g says:

    Thank you for the article.
    I am implementing auditing in mysql and I want to see the log in IST format,
    is there anywat that we can log in IST format instead of UTC?

    • Hi Naresh, Well, it depends on how you’re going to exploit the audit data, whether it’s via the log itself (xml or json) or using Workbench or whatever. However, for a solution from within the MySQL Server you could use audit_log_read() and create a bookmark that converts to IST via convert_tz(). There’s a support note that could help: “How to Read Audit Log Events Using audit_log_read() in MySQL 5.7.21 and Later? (Doc ID 2270426.1)”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s