Percona’s MySQL Audit Log PluginThe need to audit who is using a database and their activities is crucial in many environments. This functionality is available in the free, open source Audit Plugin that is included in Percona Server for MySQL.

The prerequisite for installing Percona’s Audit Log Plugin is to first install Percona Server for MySQL. Percona Server for MySQL is a drop-in replacement for MySQL.

The Audit Log Plugin itself is easy to install requiring only a shared object library to be loaded from a MySQL client program with INSTALL PLUGIN audit_log SONAME ‘audit_log.so’;.

If you desire to double-check the installation, you need only query the Information Schema’s Plugins table.

The Audit Log will collect information on connections, queries, shutdowns, deletions, and other activities in JSON, CSV, or XML formats (your choice). The logging itself is highly configurable with settings to include or exclude specific users, data, or commands. And the audit trail can be sent to a file or SYSLOG.

If you have never used audit logs before please be advised that the output at first seems verbose. And the first record you will see in this log is the loading of the audit log shared object itself.

Example

To demonstrate how the Audit Plugin functions, consider the following where two accounts are created and then included in the variable audit_log_include_accounts.

What is recorded?

What is recorded is the activity on the server and this is configurable to include or exclude accounts, schemas, or actions. Connecting to the server with one of the new accounts generates two records – a Connect and a Query as can be seen below. Using MySQL Shell generates more entries as the shell requests more data at connection time to populate variables.

Maybe there are some commands that you do not care to audit. For instance, while you care deeply about tables being deleted, suppose that you do not want to audit table creation, SELECTS, or changes to a different database. You can pick the commands to audit with a command similar to SET GLOBAL audit_log_include_commands= ‘set_option,create_db’;. So how do you get a list of the commands that can be audited?

And you see your server’s equivalent to the following:

What to look for

Learning to read the audit log will not take a lot of time. A good place to start is to look at cases of people trying to log in but being unsuccessful. A bad login, where the password was ‘fat fingered’ (on purpose below), generates a slightly different pattern where a Quit is generated, not a connection.

A good login using mysql -u test1 -p davetest generated a Connect

I recommend starting with one type of action and surveying the log for all the occurrences. Maybe your instance is having a lot of ALTER TABLE activity or bad logins. Look for patterns that may need your attention.

Example

The recording of the creation of a simple table and a row of data likewise generate audit log entries.

Here we can see the exact query executed, timestamp data, connection information, and the command status.

The Percona Audit Plugin is highly configurable. You can include accounts by issuing a SET GLOBAL audit_log_include_accounts = ‘[email protected],[email protected]’; or exclude with SET GLOBAL audit_log_exclude_accounts = ‘[email protected],[email protected]’;. Likewise, you can include or exclude databases with SET GLOBAL audit_log_include_databases = ‘test,mysql,db1’;

You can also set the overall audit_log_policy too. This variable is used to specify which events should be logged, such as ALL – all events will be logged, LOGINS – only, Logins will be logged, QUERIES – only queries will be logged, and NONE – no events will be logged. Combined with the options listed above to include or exclude, it should be easy enough to concentrate down to the most critical events and resources that need to be monitored.

To save you from running out of disk space you also have control of when to rotate the audit log file itself when the file reaches the size you deem appropriate.

Conclusion

The Audit Log Plugin is an enterprise-level feature that comes included with the free, open source Percona Server for MySQL. If you wanted this feature for Oracle’s MySQL then you would need to purchase an Enterprise License. The settings for Percona’s Audit Login Plugin can be as encompassing or as granular as you need when you need that extra bit of security on your instance.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure, open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

Download Percona Distribution for MySQL Today

Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments