The 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’;.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | stoker@testbox:~/Downloads$ mysql -u root -p mysql Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 9 Server version: 8.0.30-22 Percona Server (GPL), Release '22', Revision '7e301439b65' Copyright (c) 2009-2022 Percona LLC and/or its affiliates Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so'; Query OK, 0 rows affected (0.06 sec) mysql> |
If you desire to double-check the installation, you need only query the Information Schema’s Plugins table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%'G *************************** 1. row *************************** PLUGIN_NAME: audit_log PLUGIN_VERSION: 0.2 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: AUDIT PLUGIN_TYPE_VERSION: 4.1 PLUGIN_LIBRARY: audit_log.so PLUGIN_LIBRARY_VERSION: 1.11 PLUGIN_AUTHOR: Percona LLC and/or its affiliates. PLUGIN_DESCRIPTION: Audit log PLUGIN_LICENSE: GPL LOAD_OPTION: ON 1 row in set (0.01 sec) mysql> |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | stoker@testbox:$ sudo cat /var/lib/mysql/audit.log <?xml version="1.0" encoding="UTF-8"?> <AUDIT> <AUDIT_RECORD NAME="Audit" RECORD="1_2022-11-21T18:39:45" TIMESTAMP="2022-11-21T18:39:45Z" MYSQL_VERSION="8.0.30-22" STARTUP_OPTIONS="" OS_VERSION="x86_64-Linux" /> <AUDIT_RECORD NAME="Query" RECORD="2_2022-11-21T18:39:45" TIMESTAMP="2022-11-21T18:39:45Z" COMMAND_CLASS="install_plugin" CONNECTION_ID="9" STATUS="0" SQLTEXT="INSTALL PLUGIN audit_log SONAME 'audit_log.so'" USER="root[root] @ localhost []" HOST="localhost" OS_USER="" IP="" DB="" /> |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> CREATE USER 'test1'@'%' IDENTIFIED BY '1Test1'; Query OK, 0 rows affected (0.13 sec) mysql> CREATE USER 'test2'@'%' IDENTIFIED BY '2Test2'; Query OK, 0 rows affected (0.14 sec) mysql> SET GLOBAL audit_log_include_accounts ='test1@%,test2@%'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@audit_log_include_accounts; +------------------------------+ | @@audit_log_include_accounts | +------------------------------+ | test1@%,test2@% | +------------------------------+ 1 row in set (0.00 sec) mysql> |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | <AUDIT_RECORD NAME="Connect" RECORD="11_2022-11-21T18:39:45" TIMESTAMP="2022-11-21T18:51:48Z" CONNECTION_ID="10" STATUS="0" USER="test1" PRIV_USER="test1" OS_LOGIN="" PROXY_USER="" HOST="localhost" IP="" DB="" /> <AUDIT_RECORD NAME="Query" RECORD="12_2022-11-21T18:39:45" TIMESTAMP="2022-11-21T18:51:48Z" COMMAND_CLASS="select" CONNECTION_ID="10" STATUS="0" SQLTEXT="select @@version_comment limit 1" USER="test1[test1] @ localhost []" HOST="localhost" OS_USER="" IP="" DB="" /> |
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?
1 | mysql> SELECT name FROM performance_schema.setup_instruments WHERE name LIKE "statement/sql/%" ORDER BY name; |
And you see your server’s equivalent to the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | +------------------------------------------+ | name | +------------------------------------------+ | statement/sql/alter_db | | statement/sql/alter_db_upgrade | | statement/sql/alter_event | | statement/sql/alter_function | | statement/sql/alter_procedure | | statement/sql/alter_server | | statement/sql/alter_table | | statement/sql/alter_tablespace | | statement/sql/alter_user | | statement/sql/analyze | | statement/sql/assign_to_keycache | | statement/sql/begin | | statement/sql/binlog | | statement/sql/call_procedure | | statement/sql/change_db | | statement/sql/change_master | ... | statement/sql/xa_rollback | | statement/sql/xa_start | +------------------------------------------+ 145 rows in set (0.00 sec) |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | <AUDIT_RECORD NAME="Quit" RECORD="43_2022-11-21T18:39:45" TIMESTAMP="2022-11-21T18:57:48Z" CONNECTION_ID="16" STATUS="0" USER="test1" PRIV_USER="test1" OS_LOGIN="" PROXY_USER="" HOST="localhost" IP="" DB="davetest" /> <AUDIT_RECORD NAME="Connect" RECORD="44_2022-11-21T18:39:45" TIMESTAMP="2022-11-21T18:57:54Z" CONNECTION_ID="17" STATUS="1045" USER="test1" PRIV_USER="test1" OS_LOGIN="" PROXY_USER="" HOST="localhost" IP="" DB="" /> |
A good login using mysql -u test1 -p davetest generated a Connect
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | <AUDIT_RECORD NAME="Connect" RECORD="45_2022-11-21T18:39:45" TIMESTAMP="2022-11-21T18:58:34Z" CONNECTION_ID="18" STATUS="0" USER="test1" PRIV_USER="test1" OS_LOGIN="" PROXY_USER="" HOST="localhost" IP="" DB="davetest" /> <AUDIT_RECORD NAME="Query" RECORD="46_2022-11-21T18:39:45" TIMESTAMP="2022-11-21T18:58:34Z" COMMAND_CLASS="show_databases" CONNECTION_ID="18" STATUS="0" SQLTEXT="show databases" USER="test1[test1] @ localhost []" HOST="localhost" OS_USER="" IP="" DB="davetest" /> <AUDIT_RECORD NAME="Query" RECORD="47_2022-11-21T18:39:45" TIMESTAMP="2022-11-21T18:58:34Z" COMMAND_CLASS="show_tables" CONNECTION_ID="18" STATUS="0" SQLTEXT="show tables" USER="test1[test1] @ localhost []" HOST="localhost" OS_USER="" IP="" DB="davetest" /> <AUDIT_RECORD NAME="Query" RECORD="48_2022-11-21T18:39:45" TIMESTAMP="2022-11-21T18:58:34Z" COMMAND_CLASS="select" CONNECTION_ID="18" STATUS="0" SQLTEXT="select @@version_comment limit 1" USER="test1[test1] @ localhost []" HOST="localhost" OS_USER="" IP="" DB="davetest" /> |
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.
1 2 3 4 5 6 7 | mysql> create table t1 (id int unsigned not null auto_increment primary key, c1 int, extra JSON); Query OK, 0 rows affected (0.63 sec) mysql> insert into t1 (c1,extra) values (101,'{ "Name": "Dave"}'); Query OK, 1 row affected (0.10 sec) mysql> |
Here we can see the exact query executed, timestamp data, connection information, and the command status.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | <AUDIT_RECORD NAME="Query" RECORD="50_2022-11-21T18:39:45" TIMESTAMP="2022-11-21T19:04:09Z" COMMAND_CLASS="create_table" CONNECTION_ID="18" STATUS="0" SQLTEXT="create table t1 (id int unsigned not null auto_increment primary key, c1 int, extra JSON)" USER="test1[test1] @ localhost []" HOST="localhost" OS_USER="" IP="" DB="davetest" /> <AUDIT_RECORD NAME="Query" RECORD="51_2022-11-21T18:39:45" TIMESTAMP="2022-11-21T19:05:20Z" COMMAND_CLASS="insert" CONNECTION_ID="18" STATUS="0" SQLTEXT="insert into t1 (c1,extra) values (101,'{ "Name": "Dave"}')" USER="test1[test1] @ localhost []" HOST="localhost" OS_USER="" IP="" DB="davetest" /> |
The Percona Audit Plugin is highly configurable. You can include accounts by issuing a SET GLOBAL audit_log_include_accounts = ‘user1@host,root@host’; or exclude with SET GLOBAL audit_log_exclude_accounts = ‘user1@host,root@host’;. 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!
ERROR 1126 (HY000): Can’t open shared library ‘/usr/lib64/mysql/plugin/audit_log.so’ (errno: 2 /usr/lib64/mysql/plugin/audit_log.so: cannot open shared object file: No such file or directory)