MySQL community audit logging
Introduction
This time, we are going to discuss various options for MySQL community edition authentication audit logging.
Authentication audit is certainly an important part of continuous monitoring. If a hacker can get the credentials of the database from elsewhere (e.g. compromise of another machine), then the adversary may also be able to directly access the database. Therefore, we may catch attackers earlier using these kinds of IoC.
Testing Environments:
During our research work, we have selected the following 3 environments:
A. Red Hat Enterprise Linux (RHEL) 7.2 & MySQL community server 5.7.19B. Red Hat 7.4 & MySQL community server 5.7.29C. Redhat 8.0 & MySQL community server 8.0.16
Available solutions for MySQL community audit logging:
Before we go in-depth for technical details, we will first list available solutions. However, We will not discuss the MySQL Enterprise audit logging plugin because it only supports MySQL Enterprise.
Native MySQL general_log configurationMySQL Enterprise audit logging plugin (audit_log.so)MariaDB audit logging plugin (server_audit.so)Mcafee audit logging plugin (libaudit_plugin.so)Percona audit logging plugin (audit_log.so)
Support Matrix for MySQL community audit logging:
Check out the following compatibility matrix so that you can select the solutions suitable for your environment:
RHEL 7.2 & MySQL community server 5.7.19 | RHEL 7.4 & MySQL community server 5.7.29 | Red Hat 8.0 & MySQL community server 8.0.16 | |
Native MySQL general_log configuration | ✔️ | ✔️ | ✔️ |
MySQL Enterprise audit logging plugin (audit_log.so) | ❌ | ❌ | ❌ |
MariaDB audit logging plugin (server_audit.so) | ✔️ | ✔️ | ❌ |
Mcafee audit logging plugin (libaudit_plugin.so) | ✔️ | ✔️ | ✔️ |
Percona audit logging plugin (audit_log.so) | ❌ | ❌ | ✔️ |
Description, Pros, and Cons of different MySQL community audit logging:
1. Native MySQL general_log configuration
- Description
- Natively supported by both MySQL community/enterprise version and MariaDB
- Logs both authentication and query without the option to filter
- Native app by Splunk, but the parsing needs to be fine-tuned.
- Pros
- Support both MySQL community server 5.7.X and 8.X
- Third-party plugin is not needed and therefore no compatibility concerns
- Cons
- May impact MySQL performance due to it logs ALL query
- May raise privacy concern due to SQL statement logged may contain unencrypted sensitive information
2. MySQL Enterprise audit logging plugin (audit_log.so)
- Description
- Introduced since MySQL Enterprise version 5.7.9
- Support full auditing as well as only log authentication-related events by using
--audit-log-policy=LOGINSoptions.
- Pros
- Natively comes with MySQL Enterprise edition, thus no compatibility concerns
- Cons
- Only supports MySQL Enterprise version
3. MariaDB audit logging plugin (server_audit.so)
- Description
- Audit logging plugins developed by MariaDB, which is another company contributed by original founder of MySQL.
- Support full auditing as well as only log authentication-related event
server_audit_events='CONNECT'options - Only supports MySQL community server 5.7.X, but it does not work since MySQL community v5.7.30
- Pros
- Less compatibility concern because MariaDB 5.5 is completely based on MySQL 5.x
- Cons
- Additional third-party plugin installation is needed
- MySQL community server 8.x is not supported.
4. Percona audit logging plugin (audit_log.so)
- Description
- Audit logging plugins developed by Percona, which is another drop-in replacement of MySQL server.
- Support full auditing in different formats (e.g. OLD XML, NEW, JSON, and CSV) as well as only log authentication-related events with
audit_log_policy = LOGINSoptions - Only support MySQL community server 8.x
- Pros
- Less compatibility concern due to Percona 8.0 is based on MySQL 8.0
- Cons
- Additional third-party plugin installation is required
- MySQL community server 5.7 is not supported.
5. Mcafee audit logging plugin (libaudit_plugin.so)
- Description
- Audit logging plugins developed by Mcafee, which has been a CyberSecurity Company for a long time.
- Support full auditing in JSON format as well as only log authentication-related events using
audit_record_cmds='connect,Failed Login,Quit'options
- Pros
- Support both MySQL community server 5.7.X and 8.X
- Cons
- Third-party plugin is needed
- This may introduce performance impact due to this plugin using non-standard API
- Some additional packages (
gdb,policycoreutils-devel) are needed to install - Additional efforts to deal with
SELINUXsettings - Additional effort to deal with process offset using
GDB - Introduce additional complexity during MySQL upgrade as process offset may change after each MySQL upgrade
Conclusion and Recommendation:
- In general, we recommend using the MariaDB audit logging plugin for MySQL community 5.7.x, and use Percona audit logging plugin for MySQL community 8.x.
- If you have MySQL community version > 5.7.30, then you can consider both Native MySQL general_log configuration or Mcafee audit logging plugin.
- If you choose Native MySQL general_log configuration, then you should consider to encrypt the partition/mount point where logs resides. In addition, check out our solution only includes authentication logs sent to Splunk.
Installation instruction for MySQL community audit logging:
Although some audit logging plugins support various formats, the configuration format mentioned in this article aligned with our Splunk Apps.
Redhat 7.4 & MySQL community server 5.7.29
Since there is no difference between the configuration of Redhat 7.2 & MySQL community server 5.7.19 and Redhat 7.4 & MySQL community server 5.7.29, we will list only one here.
Native logging using general_log settings
Enter MySQL console and show current log settings:
[root@myredhat74 ~]# mysql -uroot -p -hlocalhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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> SHOW VARIABLES LIKE "general_log%";
+------------------+-------------------------------+
| Variable_name | Value |
+------------------+-------------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/myredhat74.log |
+------------------+-------------------------------+
2 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE "log_output";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE "log_warnings";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings | 2 |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
While still in MySQL console, we can enable log settings at runtime.
mysql> SET global general_log_file='/var/log/mysql/mysql_general.log';
Query OK, 0 rows affected (0.00 sec)
mysql> SET global general_log = on;
Query OK, 0 rows affected (0.01 sec)
mysql> SET global log_output = 'file';
Query OK, 0 rows affected (0.00 sec)
mysql>
We will need to edit /etc/my.cnf to enable persistent log settings. The general_log settings will log all successful and failed attempts as well as queries.
[mysqld]
general_log = on
general_log_file=/var/log/mysql/mysql_general.logIt is possible to disable DNS lookups so that MySQL will log source IP addresses instead of hostname. However, you will need to grant permissions using IP addresses rather than a hostname.
skip-name-resolveFinally, we will need to create a log directory and restart MySQL daemon.
[root@myredhat74 ~]# mkdir -p /var/log/mysql/
[root@myredhat74 ~]# chown -R mysql:mysql /var/log/mysql
[root@myredhat74 ~]# systemctl restart mysqldMariaDB audit logging plugin (server_audit.so) settings
For MariaDB audit logging plugin, we will need to download the MariaDB binary file and then extract it.
[root@myredhat74 ~]# wget https://downloads.mariadb.org/f/mariadb-5.5.68/bintar-linux-x86_64/mariadb-5.5.68-linux-x86_64.tar.gz/from/http%3A//mirror.mephi.ru/mariadb/?serve -O mariadb-5.5.68-linux-x86_64.tar.gz
[root@myredhat74 ~]# tar -zvxf mariadb-5.5.68-linux-x86_64.tar.gzEnter MySQL console and check plugin directory, this directory is default to /usr/lib64/mysql/plugin/ for rpm installation.
[root@myredhat74 ~]# mysql -uroot -p -hlocalhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| plugin_dir | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)
mysql>
After confirming the correct plugin directory, we will need to copy the plugin library to MySQL plugin directory.
[root@myredhat74 ~]# cp ./mariadb-5.5.68-linux-x86_64/lib/plugin/server_audit.so /usr/lib64/mysql/plugin/Enter MySQL console again, and install the plugin.
[root@myredhat74 ~]# mysql -uroot -p -hlocalhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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 server_audit SONAME 'server_audit.so';
Query OK, 0 rows affected (0.02 sec)
mysql> show variables like '%audit%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| server_audit_events | |
| server_audit_excl_users | |
| server_audit_file_path | server_audit.log |
| server_audit_file_rotate_now | OFF |
| server_audit_file_rotate_size | 1000000 |
| server_audit_file_rotations | 9 |
| server_audit_incl_users | |
| server_audit_loc_info | |
| server_audit_logging | OFF |
| server_audit_mode | 1 |
| server_audit_output_type | file |
| server_audit_query_log_limit | 1024 |
| server_audit_syslog_facility | LOG_USER |
| server_audit_syslog_ident | mysql-server_auditing |
| server_audit_syslog_info | |
| server_audit_syslog_priority | LOG_INFO |
+-------------------------------+-----------------------+
16 rows in set (0.00 sec)
mysql>Now, we can add log settings to [mysqld] section in my.cnf (or configuration file used by MySQL)
server_audit_events='CONNECT'
server_audit_logging=on
server_audit_file_path = /var/log/mysql/mysql_mariadb_audit.log
server_audit_file_rotate_size=200000000
server_audit_file_rotations=200
server_audit_file_rotate_now=ONFinally, we will need to create a log directory and restart MySQL daemon.
[root@myredhat74 ~]# mkdir -p /var/log/mysql/
[root@myredhat74 ~]# chown -R mysql:mysql /var/log/mysql
[root@myredhat74 ~]# systemctl restart mysqldLet’s check the final result using Splunk.
Mcafee audit logging plugin (libaudit_plugin.so) settings
For Mcafee audit logging plugin, we will need to download the Mcafee binary file and then extract it. Check out the correct version you need here: https://github.com/mcafee/mysql-audit/releases.
[root@myredhat74 ~]# wget https://bintray.com/mcafee/mysql-audit-plugin/download_file?file_path=audit-plugin-mysql-5.7-1.1.7-913-linux-x86_64.zip -O audit-plugin-mysql-5.7-1.1.7-913-linux-x86_64.zip
[root@myredhat74 ~]# unzip audit-plugin-mysql-5.7-1.1.7-913-linux-x86_64.zip
[root@myredhat74 ~]# cp audit-plugin-mysql-5.7-1.1.7-913/lib/libaudit_plugin.so /usr/lib64/mysql/plugin/Check plugin dir, default /usr/lib64/mysql/plugin/ for Enter MySQL console and check plugin directory, this directory is default to /usr/lib64/mysql/plugin/ for rpm installation.
[root@myredhat74 ~]# mysql -uroot -p -hlocalhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| plugin_dir | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)
mysql>After confirming the correct plugin directory, we will need to copy the plugin library to MySQL plugin directory.
[root@myredhat74 ~]# cp audit-plugin-mysql-5.7-1.1.7-913/lib/libaudit_plugin.so /usr/lib64/mysql/plugin/We also need an extra shell script to get the offset of MySQL binary file.
[root@myredhat74 ~]# wget https://raw.github.com/mcafee/mysql-audit/master/offset-extract/offset-extract.sh
[root@myredhat74 ~]# chmod +x offset-extract.shThen, we can install gdb and run offset-extract.sh to retrieve the offset.
[root@myredhat74 ~]# yum -y install gdb
[root@myredhat74 ~]# ./offset-extract.sh /usr/sbin/mysqld
//offsets for: /usr/sbin/mysqld (5.7.29)
{"5.7.29","00b4b7c8931e964887789044c56346fa", 7824, 7872, 3632, 4792, 456, 360, 0, 32, 64, 160, 536, 7988, 4360, 3648, 3656, 3660, 6072, 2072, 8, 7056, 7096, 7080, 13472, 148, 672, 0},
The output from offset-extract.sh is needed under [mysqld] section of my.cnf.
plugin-load=AUDIT=libaudit_plugin.so
audit_offsets = 7824, 7872, 3632, 4792, 456, 360, 0, 32, 64, 160, 536, 7988, 4360, 3648, 3656, 3660, 6072, 2072, 8, 7056, 7096, 7080, 13472, 148, 672, 0
audit_json_file=1
audit_json_log_file=/var/log/mysql/mysql-audit.json
audit_record_cmds='connect,Failed Login,Quit'if SELINUX is enabled, then you will need to configure SELINUX Policy as well.
[root@myredhat74 ~]# yum -y install policycoreutils-devel
[root@myredhat74 ~]# semanage fcontext -a -t textrel_shlib_t /usr/lib64/mysql/plugin/libaudit_plugin.so
[root@myredhat74 ~]# restorecon -v /usr/lib64/mysql/plugin/libaudit_plugin.so
[root@myredhat74 ~]# mkdir /root/mcafee-selinux-module
[root@myredhat74 ~]# cd /root/mcafee-selinux-module
[root@myredhat74 ~]# cat <<EOT >> mysql_libaudit.te
module mysql_libaudit 1.0;
require {
type mysqld_exec_t;
type mysqld_t;
class process execmem;
class file execmod;
}
#============= mysqld_t ==============
allow mysqld_t mysqld_exec_t:file execmod;
allow mysqld_t self:process execmem;
EOT
[root@myredhat74 ~]# make -f /usr/share/selinux/devel/Makefile
[root@myredhat74 ~]# semodule -i mysql_libaudit.pp
[root@myredhat74 ~]# cd /root/mcafee-selinux-module
[root@myredhat74 ~]# grep mysqld /var/log/audit/audit.log | grep -v lib_t | audit2allow -M mysql_libaudit
[root@myredhat74 ~]# semodule -i mysql_libaudit.pp
[root@myredhat74 ~]# cd /root
[root@myredhat74 ~]# rm -rf /root/mcafee-selinux-moduleNext, we will need to create a log directory and restart MySQL daemon.
[root@myredhat74 ~]# mkdir -p /var/log/mysql/
[root@myredhat74 ~]# chown -R mysql:mysql /var/log/mysql
[root@myredhat74 ~]# systemctl restart mysqldFinally, we can check whether the Mcafee Audit library is auto-loaded.
[root@myredhat74 ~]# mysql -uroot -p -hlocalhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| AUDIT | ACTIVE | AUDIT | libaudit_plugin.so | GPL |
| SERVER_AUDIT | ACTIVE | AUDIT | server_audit.so | GPL |
| validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
mysql> show global status like 'AUDIT_version';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Audit_version | 1.1.7-913 |
+---------------+-----------+
1 row in set (0.00 sec)
If the library is not auto-loaded, we can enter MySQL console and install the plugin.
mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
Let’s check the final result using Splunk.
C. Redhat 8.0 + MySQL community server 8.0.16-2
Native logging using general_log settings
Enter MySQL console and show current log settings:
[root@myredhat8 ~]# mysql -u root -p -h localhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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> SHOW VARIABLES LIKE "general_log%";
+------------------+------------------------------+
| Variable_name | Value |
+------------------+------------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/myredhat8.log |
+------------------+------------------------------+
2 rows in set (0.01 sec)
mysql> SHOW VARIABLES LIKE "log_output";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE "log_warnings";
Empty set (0.00 sec)
mysql>
While still in MySQL console, we can enable log settings at runtime.
mysql> SET global general_log_file='/var/log/mysql/mysql_general.log';
Query OK, 0 rows affected (0.00 sec)
mysql> SET global general_log = on;
Query OK, 0 rows affected (0.01 sec)
mysql> SET global log_output = 'file';
Query OK, 0 rows affected (0.00 sec)
We will need to edit /etc/my.cnf to enable persistent log settings. The general_log settings will log all successful and failed attempts as well as queries.
[mysqld]
general_log = on
general_log_file=/var/log/mysql/mysql_general.logIt is possible to disable DNS lookups so that MySQL will log source IP addresses instead of hostname. However, you will need to grant permissions using IP addresses rather than a hostname.
skip-name-resolveFinally, we will need to create a log directory and restart MySQL daemon.
mkdir -p /var/log/mysql/
chown -R mysql:mysql /var/log/mysql
systemctl restart mysqldPercona audit logging plugin (audit_log.so) settings
For Percona audit logging plugin, we will need to download the Percona binary file and then extract it.
[root@myredhat8 ~]# wget https://downloads.percona.com/downloads/Percona-Server-LATEST/Percona-Server-8.0.16-7/binary/redhat/7/x86_64/percona-server-server-8.0.16-7.1.el7.x86_64.rpm
[root@myredhat8 ~]# mkdir Percona-Server
[root@myredhat8 ~]# mv percona-server-server-8.0.16-7.1.el7.x86_64.rpm Percona-Server/
[root@myredhat8 ~]# cd Percona-Server/
[root@myredhat8 ~]# rpm2cpio percona-server-server-8.0.16-7.1.el7.x86_64.rpm | cpio -idmvEnter MySQL console and check plugin directory, this directory is default to /usr/lib64/mysql/plugin/ for rpm installation.
[root@myredhat8 ~]# mysql -u root -p -h localhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| plugin_dir | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)
mysql> After confirming the correct plugin directory, we will need to copy the plugin library to MySQL plugin directory.
[root@myredhat8 ~]# cp ./usr/lib64/mysql/plugin/audit_log.so /usr/lib64/mysql/plugin/Enter MySQL console again, and install the plugin.
[root@myredhat8 ~]# mysql -u root -p -h localhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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.02 sec)Now, we can add log settings to [mysqld] section in my.cnf (or configuration file used by MySQL)
plugin-load = audit_log.so
audit_log_file = /var/log/mysql/audit.log
audit_log_format = CSV
audit_log_policy = LOGINS
audit_log_handler = FILEFinally, we will need to create a log directory and restart MySQL daemon.
[root@myredhat8 ~]# mkdir -p /var/log/mysql/
[root@myredhat8 ~]# chown -R mysql:mysql /var/log/mysql
[root@myredhat8 ~]# systemctl restart mysqldLet’s check the final result using Splunk.
Mcafee audit logging plugin (libaudit_plugin.so)
For Mcafee audit logging plugin, we will need to download the Mcafee binary file and then extract it. Check out the correct version you need here: https://github.com/mcafee/mysql-audit/releases.
[root@myredhat8 ~]# wget https://bintray.com/mcafee/mysql-audit-plugin/download_file?file_path=audit-plugin-mysql-8.0-1.1.7-913-linux-x86_64.zip -O audit-plugin-mysql-8.0-1.1.7-913-linux-x86_64.zip
[root@myredhat8 ~]# unzip audit-plugin-mysql-8.0-1.1.7-913-linux-x86_64.zipCheck plugin dir, default /usr/lib64/mysql/plugin/ for Enter MySQL console and check plugin directory, this directory is default to /usr/lib64/mysql/plugin/ for rpm installation.
[root@myredhat8 ~]# mysql -u root -p -h localhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| plugin_dir | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)
mysql> After confirming the correct plugin directory, we will need to copy the plugin library to MySQL plugin directory.
[root@myredhat8 ~]# cp audit-plugin-mysql-8.0-1.1.7-913/lib/libaudit_plugin.so /usr/lib64/mysql/plugin/
We also need an extra shell script to get the offset of MySQL binary file.
[root@myredhat8 ~]# wget https://raw.github.com/mcafee/mysql-audit/master/offset-extract/offset-extract.sh
chmod +x offset-extract.shThen, we can install gdb and run offset-extract.sh to retrieve the offset.
[root@myredhat8 ~]# yum -y install gdb
[root@myredhat8 ~]# ./offset-extract.sh /usr/sbin/mysqld
//offsets for: /usr/sbin/mysqld (8.0.16)
{"8.0.16","9d238d46151cd5f41fef859c5026f7a0", 8360, 8408, 3912, 5352, 520, 0, 0, 32, 64, 160, 600, 8524, 4984, 4000, 4008, 4012, 6656, 1456, 40, 7616, 7656, 7640, 11416, 140, 664, 328}, The output from offset-extract.sh is needed under [mysqld] section of my.cnf.
plugin-load=AUDIT=libaudit_plugin.so
audit_offsets = 8360, 8408, 3912, 5352, 520, 0, 0, 32, 64, 160, 600, 8524, 4984, 4000, 4008, 4012, 6656, 1456, 40, 7616, 7656, 7640, 11416, 140, 664, 328
audit_json_file=1
audit_json_log_file=/var/log/mysql/mysql-audit.json
audit_record_cmds='connect,Failed Login,Quit'if SELINUX is enabled, then you will need to configure SELINUX Policy as well.
[root@myredhat8 ~]# yum -y install policycoreutils-devel
[root@myredhat8 ~]# semanage fcontext -a -t textrel_shlib_t
/usr/lib64/mysql/plugin/libaudit_plugin.so
[root@myredhat8 ~]# restorecon -v /usr/lib64/mysql/plugin/libaudit_plugin.so
[root@myredhat8 ~]# mkdir /root/mcafee-selinux-module
[root@myredhat8 ~]# cd /root/mcafee-selinux-module
[root@myredhat8 ~]# cat <<EOT >> mysql_libaudit.te
module mysql_libaudit 1.0;
require {
type mysqld_t;
class process execmem;
}
#============= mysqld_t ==============
allow mysqld_t self:process execmem;
EOT
[root@myredhat8 ~]# make -f /usr/share/selinux/devel/Makefile
[root@myredhat8 ~]# semodule -i mysql_libaudit.pp
[root@myredhat8 ~]# cd /root/mcafee-selinux-module
[root@myredhat8 ~]# grep mysqld /var/log/audit/audit.log | grep -v lib_t | audit2allow -M mysql_libaudit
[root@myredhat8 ~]# semodule -i mysql_libaudit.pp
[root@myredhat8 ~]# cd /root
[root@myredhat8 ~]# rm -rf /root/mcafee-selinux-moduleNext, we will need to create a log directory and restart MySQL daemon.
[root@myredhat8 ~]# mkdir -p /var/log/mysql/
[root@myredhat8 ~]# chown -R mysql:mysql /var/log/mysql
[root@myredhat8 ~]# systemctl restart mysqldFinally, we can check whether the Mcafee Audit library is auto-loaded.
[root@myredhat8 ~]# mysql -u root -p -h localhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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> show plugins;
+---------------------------------+----------+--------------------+--------------+---------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+--------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| mysqlx | ACTIVE | DAEMON | NULL | GPL |
| mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
|
+---------------------------------+----------+--------------------+--------------+---------+
44 rows in set (0.01 sec)
If the library is not auto-loaded, we can enter MySQL console and install the plugin.
mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
Query OK, 0 rows affected (1.25 sec)
mysql> show global status like 'AUDIT_version';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Audit_version | 1.1.7-913 |
+---------------+-----------+
1 row in set (0.01 sec)
mysql>
Let’s check the final result using Splunk.
Native MySQL general_log filtering using Splunk
Lastly, we will introduce our solution to filter and only send authentication-related logs to Splunk. In order to simplify things, you can make the below configurations on Splunk universal forwarder, Splunk Heavy Forwarder/Indexer, and Search Head. In addition, you will also need MySQL Splunk app, and ingest MySQL general log using the sourcetype mysql:generallog:all.
Basically, the configuration will first change the sourcetype of authentication logs to mysql:generalQueryLog, and then drop other logs.
props.conf
[mysql:generallog:all]
# Splunk magic 8 props
SHOULD_LINEMERGE = false
LINE_BREAKER = ([\r\n]+)
TIME_PREFIX = ^
MAX_TIMESTAMP_LOOKAHEAD = 27
TIME_FORMAT=%Y-%m-%dT%H:%M:%S.%6QZ
# 700 is enough for authentication log
# TRUNCATE = 700
# For_Load_Balancing_On_UF
EVENT_BREAKER_ENABLE = true
EVENT_BREAKER = ([\r\n]+)
TRANSFORMS-mysql_generallog = set_mysql_generallog_auth_sourcetype,set_mysql_generallog_nonauth_null
[mysql:generalQueryLog]
EVAL-action = case((Command="Connect" AND like(Argument,"%Access denied for user%")), "failure", (Command="Query" AND Argument=="select @@version_comment limit 1"), "success", true(), null)
EVAL-src = client_host
EVAL-src_ip = if(cidrmatch("0.0.0.0/0",client_host), client_host, null())
transforms.conf
[set_mysql_generallog_auth_sourcetype]
REGEX = (?:^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}.\d{6}Z\s+\d+\s+(?:Query\s+select @@version_comment\s+limit\s+1|Connect\s+.*?@.*))$
DEST_KEY = MetaData:Sourcetype
FORMAT = sourcetype::mysql:generalQueryLog
[set_mysql_generallog_nonauth_null]
SOURCE_KEY = MetaData:Sourcetype
REGEX = mysql:generallog:all
DEST_KEY = queue
FORMAT = nullQueue
Let’s check the final result:
We have another post about MSSQL monitoring, feel free to visit here.
Reference:
