Using the MariaDB Audit Plugin for Database Security

There are different ways to keep your data safe. Practices such as controlling database access, securing configuration, upgrading your system, and more are part of database security. It is even possible that you have security issues and don’t realize it (until it is too late), that’s why monitoring is a key piece to ensure that if something unexpected happens, you will be able to catch it. This includes not only your system, but also your databases.

Auditing is a way to know what is happening in your database, and it is also required for many security regulations or standards (e.g. PCI – Payment Card Industry).

MariaDB Server, one of the most popular open-source database servers, has its own Audit Plugin (which also works on MySQL), in order to help with this auditing task. In this blog, you will see how to install and use this useful MariaDB Audit Plugin.

We’ll also introduce MariaDB’s more advanced version of the plugin for MariaDB Enterprise Audit, which is included in Enterprise installations 10.6, 10.5, and 10.4. But more on that later.

What is MariaDB Audit Plugin?

The Audit Plugin is developed by MariaDB to meet the requirements to record user access to be in compliance with auditing regulations.

For each client session, it records, in a log file (or syslog), who connected to the server, what queries executed, which tables were accessed, and server variables changed.

It works with MariaDB, MySQL, and Percona Server. MariaDB started including by default the Audit Plugin from versions 10.0.10 and 5.5.37, and it can be installed in any version from MariaDB 5.5.20.

MariaDB Audit Plugin Installation

The plugin file (server_audit.so) is installed by default during the MariaDB installation in the plugins directory /usr/lib/mysql/plugin/:

$ ls -lah /usr/lib/mysql/plugin/ |grep server_audit

-rw-r--r-- 1 root  root  63K May  9 19:33 server_audit.so

So, you just need to add it into the MariaDB instance:

MariaDB [(none)]> INSTALL SONAME 'server_audit';

Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> SHOW PLUGINS;

+--------------+--------+-------+-----------------+---------+

| Name         | Status | Type  | Library         | License |

+--------------+--------+-------+-----------------+---------+

| SERVER_AUDIT | ACTIVE | AUDIT | server_audit.so | GPL     |

+--------------+--------+-------+-----------------+---------+

And enable it using the SET GLOBAL command:

MariaDB [(none)]> SET GLOBAL server_audit_logging=ON;

Query OK, 0 rows affected (0.000 sec)

Or make it persistent in the my.cnf configuration file to start to audit:

[MYSQLD]

server_audit_logging=ON

Another way to add it into the MariaDB instance is by adding the plugin_load_add parameter in the my.cnf configuration file:

[mariadb]

plugin_load_add = server_audit

It is also recommended to add the FORCE_PLUS_PERMANENT to avoid uninstalling it:

[mariadb]

plugin_load_add = server_audit

server_audit=FORCE_PLUS_PERMANENT

Now you have the MariaDB Audit Plugin installed, let’s see how to configure it.

MariaDB Audit Plugin Configuration

To check the current configuration you can see the value of the “server_audit%” global variables by running the following command:

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE "server_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_logging          | OFF                   |

| server_audit_mode             | 0                     |

| 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              |

+-------------------------------+-----------------------+

15 rows in set (0.001 sec)

You can modify these variables using the SET GLOBAL command or make it persistent in the my.cnf configuration file under the [mysqld] section.

Let’s describe some of the most important variables:

• server_audit_logging:  Enables audit logging.
• server_audit_events: Specifies the events that you want to record. By default, the value is empty, which means that all events are recorded. The options are CONNECTION, QUERY, and TABLE.
• server_audit_excl_users, server_audit_incl_users: These variables specify which users’ activity should be excluded or included in the audit log file. By default, all users’ activity is recorded.
• server_audit_output_type: By default auditing output is sent to a file. The other option is syslog, meaning all entries go to the syslog facility.
• server_audit_syslog_facility, server_audit_syslog_priority: Specifies the syslog facility and the priority of the events that should go to syslog.

After configuring it, you can see the audit events in the specified log file (or syslog). Let’s see how it looks.

MariaDB Audit Plugin Log

To see the events registered by the Audit Log Plugin, you can check the specified log file (by default server_audit.log).

$ tail -f /var/lib/mysql/server_audit.log

20200703 19:07:04,MariaDB1,cmon,10.10.10.116,64,915239,QUERY,information_schema,'FLUSH /*!50500 SLOW */ LOGS',0

20200703 19:07:05,MariaDB1,cmon,10.10.10.116,61,915240,QUERY,information_schema,'SHOW GLOBAL STATUS',0

20200703 19:07:05,MariaDB1,cmon,10.10.10.116,64,915241,WRITE,mysql,slow_log,

20200703 19:07:05,MariaDB1,cmon,10.10.10.116,64,915241,QUERY,information_schema,'SET GLOBAL SLOW_QUERY_LOG=1',0

20200703 19:07:06,MariaDB1,cmon,10.10.10.116,61,915242,QUERY,information_schema,'SHOW GLOBAL STATUS',0

20200703 19:15:42,MariaDB1,root,localhost,124,0,CONNECT,,,0

20200703 19:15:42,MariaDB1,root,localhost,124,917042,QUERY,,'select @@version_comment limit 1',0

20200703 19:15:48,MariaDB1,root,localhost,124,0,DISCONNECT,,,0

20200703 19:57:41,MariaDB1,root,localhost,135,925831,QUERY,,'create database test1',0

20200703 19:58:05,MariaDB1,root,127.0.0.1,136,0,FAILED_CONNECT,,,1045

20200703 19:58:05,MariaDB1,root,127.0.0.1,136,0,DISCONNECT,,,0

20200703 19:58:49,MariaDB1,root,localhost,137,926073,QUERY,,'SELECT DATABASE()',0

20200703 19:58:49,MariaDB1,root,localhost,137,926075,QUERY,test1,'show databases',0

20200703 19:58:49,MariaDB1,root,localhost,137,926076,QUERY,test1,'show tables',0

20200703 19:59:20,MariaDB1,root,localhost,137,926182,CREATE,test1,t1,

20200703 19:59:20,MariaDB1,root,localhost,137,926182,QUERY,test1,'create table t1 (id int, message text)',0

20200703 19:59:48,MariaDB1,root,localhost,137,926287,QUERY,test1,'insert into t1 values (4,'message 1')',0

As you can see in the above log, you will have events about database connections and the queries running there, depending on the server_audit_events configuration.

Using the MariaDB Audit Plugin in ClusterControl

In order to avoid manual configuration, you can enable the Audit Plugin from the ClusterControl UI. For this, you only need to go to ClusterControl -> Select the MariaDB Cluster -> Security -> Audit Log:

And you will have the plugin enabled without any manual installation nor configuration.

Using ClusterControl you can also take advantage of different features, not only security but also monitoring, managing, and backing up, among other useful features.

Introducing MariaDB Enterprise Audit Plugin, server_audit2

MariaDB continues to evolve and that includes plugin improvements. Included in 10.6, 10.5, and 10.4 MariaDB Enterprise Server installations, their latest plugin for MariaDB Enterprise Audit is  server_audit2.so. In a nutshell, it enables users to store information necessary for compliance audits.

The plugin introduces system tables for filter templates and users, a default filter, and also some per-user account level filters. The new default filter allows people to define auditing rules for any user without a defined filter. The table consists of a filtername (required to be set to “default”), and rule column. Rule sets need to be “refreshed” by running a query like SET GLOBAL server_audit_reload_filters=ON;

Users can also store filternames applicable to a certain user and audit the plugin itself, so if a change in the configuration of the audit plugin occurs, it’s logged in the AUDIT_CONFIG event (according to MariaDB, information can also be added to a new audit log file to log the current settings).

Additionally, the server_audit_users table can now be used in server_audit2.so. plugin to store user-related information.

To ensure that your MariaDB installation has this plugin, search for server_audit2.so in the directory defined in the plugin_dir system variable or simply query @@plugin_dir if you don’t know the directory and don’t want to spend time going through variables in my.cnf.

Conclusion

Auditing is required for many security regulations and it is also useful if you want to know what happened in your database, and when and who was responsible for that.

The MariaDB Audit Plugin, and it’s enterprise variant, is an excellent way to audit your databases without using any external tool, and it is also compatible with MySQL and Percona Server. If you want to avoid configuring it manually, you can use ClusterControl to enable the Audit Plugin in an easy way from the UI.