Percona Audit Plugin

In this blog i will show how to install and use the percona audit plugin.

Why audit log?

Audit logs are useful to monitor the happenings inside the database server. It is location where to keep the track of who did what to the DB. It captures the user from which host he connected at what time which query was executed.

Percona Audit Log Plugin provides monitoring and logging of connection and query activity that were performed on specific server.

All the information can be stored in the XML log file,CSV,JSON formats where each event will have its NAME field, its own unique RECORD_ID field and a TIMESTAMP field and information about query.

Installation

Along with percona server audit plugin is available , but it is not installed by default. We need to install the plugin to enable it.

We can check with below query if audit log is enabled or not.

mysql> select * from information_schema.PLUGINS where PLUGIN_NAME like '%audit%'; Empty set (0.01 sec) mysql> show variables like '%audit%'; Empty set (0.00 sec) mysql>

To install the plugin run the below query on mysql command line.

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

Before executing the above command it is a good approach to add the audit log related configuration on the mysql configuration file (my.cnf). Adding these variables will not make you to restart the mysql service or else you will require a mysql restart for the audit log to work.

####Percona Audit###### plugin-load = audit_log.so audit_log_file = /var/log/mysql/audit.log audit_log_format = CSV audit_log_policy = QUERIES audit_log_handler = FILE

plugin-load

it is the socket file for audit log

audit_log_file

To specify the filename that’s going to store the audit log

audit_log_format

To specify the audit log format. The audit log plugin supports four log formats: OLD, NEW, JSON, and CSV

audit_log_policy

To specify which events should be logged. Values are:  ALL, LOGINS, QUERIS, NONE

audit_log_handler

To configure where the audit log will be written ie, syslog or normal file

After adding the above variables you can run the command to install audit log.

mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so'; Query OK, 0 rows affected (0.02 sec)

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.7 PLUGIN_AUTHOR: Percona LLC and/or its affiliates. PLUGIN_DESCRIPTION: Audit log PLUGIN_LICENSE: GPL LOAD_OPTION: ON 1 row in set (0.00 sec) mysql>

mysql> show variables like '%audit%'; +-----------------------------+--------------------------+ | Variable_name               | Value                    | +-----------------------------+--------------------------+ | audit_log_buffer_size       | 1048576                  | | audit_log_exclude_accounts  |                          | | audit_log_exclude_commands  |                          | | audit_log_exclude_databases |                          | | audit_log_file              | /var/log/mysql/audit.log | | audit_log_flush             | OFF                      | | audit_log_format            | CSV                      | | audit_log_handler           | FILE                     | | audit_log_include_accounts  |                          | | audit_log_include_commands  |                          | | audit_log_include_databases |                          | | audit_log_policy            | QUERIES                  | | audit_log_rotate_on_size    | 0                        | | audit_log_rotations         | 0                        | | audit_log_strategy          | ASYNCHRONOUS             | | audit_log_syslog_facility   | LOG_USER                 | | audit_log_syslog_ident      | percona-audit            | | audit_log_syslog_priority   | LOG_INFO                 | +-----------------------------+--------------------------+ 18 rows in set (0.01 sec) mysql>

Below is the entry of audit log file , how it looks.

[root@localhost ~]# cat /var/log/mysql/audit.log "Query","39914_2019-09-24T11:35:49","2019-09-24T11:37:47 UTC","show_databases","2",0,"show databases","root[root] @ localhost []","localhost","","","" "Query","39916_2019-09-24T11:35:49","2019-09-24T11:37:59 UTC","create_db","2",0,"create database test","root[root] @ localhost []","localhost","","","" "Query","39917_2019-09-24T11:35:49","2019-09-24T11:38:01 UTC","select","2",0,"SELECT DATABASE()","root[root] @ localhost []","localhost","","","" "Query","39921_2019-09-24T11:35:49","2019-09-24T11:38:03 UTC","show_tables","2",0,"show tables","root[root] @ localhost []","localhost","","",""

Now we have enabled the audit log for our mysql database.
IN percona audit log there are variables  which can control what type of queries to log and not to log.

audit_log_include_commands

Used to specify the list of commands for which Filtering by SQL command type is applied. The value can be NULL or comma separated list of commands. 
If this variable is set, then audit_log_exclude_commands must be unset, and vice versa.

audit_log_exclude_commands

Used to specify the list of commands for which Filtering by SQL command type is applied to exclude them ie, those kind of sql queries wont be logged. The value can be NULL or comma separated list of commands. 

If this variable is set, then audit_log_include_commands must be unset, and vice versa.

Either we can use one variable in our audit log.

For example if you want to log all the DML queries expect SELECT your audit_log_include_commands variable should look like below.

audit_log_include_commands='insert,insert_select,delete,delete_multi,update,update_multi'

The above will make sure to log only the insert, delete and updates.

You can set it globally too

mysql> show variables like 'audit_log_include_commands'; +----------------------------+-------+ | Variable_name              | Value | +----------------------------+-------+ | audit_log_include_commands |       | +----------------------------+-------+ 1 row in set (0.01 sec) mysql> set global audit_log_include_commands='insert,insert_select,delete,delete_multi,update,update_multi'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'audit_log_include_commands'; +----------------------------+--------------------------------------------------------------+ | Variable_name              | Value                                                        | +----------------------------+--------------------------------------------------------------+ | audit_log_include_commands | insert,insert_select,delete,delete_multi,update,update_multi | +----------------------------+--------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>

I will be executing some insert, delete update and select to check what is being logged in the log file.

From the above logs you can see i have executed select queries too, but they are not logged. In this way you can restrict your audit log file to capture the required events.