“The purpose of the MariaDB Audit Plugin is to log the server’s activity. Records about who connected to the server, what queries ran and what tables were touched can be stored to the rotating log file or sent to the local syslogd.” – MariaDB. The MariaDB Audit Plugin works for MariaDB, MySQL and Percona server.
Links and Basics
Let’s see how to install the MariaDB audit plugin to retrieve all activity on the database server. I am actually testing it on a MariaDB 5.5 series database. You can use a 10.1 series if you want. For MariaDB installation there are articles which i have posted some times back such as MariaDB Galera cluster installation, MariaDB and its improved security features, Master-Master replication on MariaDB and a Master-Slave replication on MariaDB. I think those articles are pretty straight forward to dive into the installation procedures. Imagine having so many users connected on a database performing so many queries. One on the way to trace those requests are through the binlogs or by activating the MariaDB audit log. Let’s see how to activate the MariaDB Audit log.
Verification and Prerequisites
After installing your Database server, get into the console and launch the following command SHOW GLOBAL VARIABLES LIKE ‘plugin_dir’ ; It should prompt you something like this:
If you are using a 32-bit system if would be found in /usr/lib/mysql/plugin. Get into that directory and checked if you got a file called server_audit.so By default, on new MariaDB, its already available. However, if you are using MySQL-Server, or an old MariaDB or Percona server you would need to install the server_audit plugin. Download it from this link: http://www.skysql.com/downloads/mariadb-audit-plugin-beta [updated in 2023 to https://mariadb.com/docs/server/ref/mdb/plugins/SERVER_AUDIT,server_audit.so/ ]Once downloaded, extract it and copy the file server_audit.so to the plugin dir value path [See screenshot above].
[google_ad data_ad_slot=” data_ad_format=’rectangle’]
This does not means its already activated. You can verify it using the following commands SELECT * FROM information_schema.plugins WHERE plugin_name=’server_audit’; If it returns an emply set, it means its not yet activated otherwise you should have something like this:
Activating the Plugin
To activate the plugin, you can restart the service. However, there is another option to prevent any MySQL downtime by launching this command INSTALL PLUGIN server_audit SONAME ‘server_audit.so’;
Plugin configuration
After activating the plugin and if you are going to launch the command SHOW GLOBAL VARIABLES LIKE ‘server_audit%’; by default it would output you the following parameters.
These default values would normally create a log file called server_audit.log in the data directory. The values are self explicit. For example the parameter server_audit_file_rotate_size with value 1000000 means that when the size of the log is going to reach 1000000 bytes, its going to be rotated and nine files will be used before the log file will be overwritten. You also need to choose which type of events you want to log. Here is an example when activating all CONNECT, QUERY and TABLE event. If you want to audit only the CONNECT even, do set the variable to CONNECT only.
You can also turn off the plugin using the command SET GLOBAL server_audit_logging=OFF; As mentioned previously, the logs are saved at /var/lib/mysql/server_audit.log Here is an example of a log.
[google_ad data_ad_slot=” data_ad_format=’rectangle’]
Here are some of the most important variables:
server_audit_logging– Enables audit logging; if it’s not set to ON, audit events will not be recorded and the audit plugin will not do anything.server_audit_events– Specifies the events you wish to have in the log. By default the value is empty, which means that all events are recorded. The options are:CONNECTION(users connecting and disconnecting),QUERY(queries and their result), andTABLE(which tables are affected by the queries).server_audit_excl_users,server_audit_incl_users– These variables specify which users’ activity should be excluded from or included in the audit.server_audit_incl_usershas the higher priority. By default, all users’ activity is recorded.server_audit_output_type– By default auditing output is sent to a file. The other option issyslog, 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.
Log File Examination
Log file can also be examined. The audit is performed in such a way that if even one user connect and disconnect the the MySQL it would be easily detected. A connect and disconnect would usually appears as this:
Queries woud look like this. Even if there are errors on the query, it would keep it in the log
The server_audit_events variable specifies which of the five events to log, taking a comma-separated list of the event types as an argument. There are six types of log records:
The audit log format looks like this:
[timestamp],[serverhost],[username],[host],[connectionid],
[queryid],[operation],[database],[object],[retcode]
Other Tips and Tricks
- To avoid a heavy load on the machine, you can exclude a specific user using the parameter server_audit_excl_users=test,toto
- If the server_audit_output_type variable is set to
SYSLOGinstead of the default,FILE, the audit log file format will be as follows:[timestamp][syslog_host][syslog_ident]:[syslog_info][serverhost],[username],[host], [connectionid],[queryid],[operation],[database],[object],[retcode] - Be aware, though, that passwords given with functions PASSWORD() or OLD_PASSWORD() in DML statements will still be logged as plain text in queries. Key strings used with encrypt functions likeENCODE() and AES_ENCRYPT() are also still logged in plain text.
- DDL and DML statements can also be audited.
