MariaDB Audit Plugin logging to syslog

Syslog is widely used for logging. It allows separation of the storage of generated messages from the system that generated them. A lot of solution exists to aggregate, monitor, query, analyze syslog data. Syslog separation of data from the source is a key element for security auditing as it make it more difficult to repudiate or wipe out logging data. Syslog data can be consolidated in a central repository with make it easier to correlate various events. Consolidated logs can go through reporting, analytics or artificial intelligence algorithms to detect patterns and alert customers of problems.

To set up the MariaDB Audit Plugin to logging to syslog is quite simple :

You download the MariaDB audit plugin (a single share library file) from :
http://www.skysql.com/downloads/mariadb-audit-plugin-beta
You copy the server_audit.so file to the lib/plugin directory in your MySQL/MariaDB install directory and activate it through :

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

By default login is done to file and you need to switch it to syslog :

MariaDB [test]> SET GLOBAL server_audit_output_type=SYSLOG;
MariaDB [test]> SET GLOBAL server_audit_events='CONNECT,QUERY';
MariaDB [test]> SET GLOBAL server_audit_logging=on;

You can then see all the variable allowing to configure it :

MariaDB [test]> show variables like '%audit%';
+-------------------------------------+-----------------------+
| Variable_name                       | Value                 |
+-------------------------------------+-----------------------+
| server_audit_events                 | CONNECT,QUERY         |
| 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                | ON                    |
| server_audit_mode                   | 0                     |
| server_audit_output_type            | syslog                |
| server_audit_syslog_facility        | LOG_USER              |
| server_audit_syslog_ident           | mysql-server_auditing |
| server_audit_syslog_info            |                       |
| server_audit_syslog_priority        | LOG_INFO              |
+-------------------------------------+-----------------------+
14 rows in set (0.00 sec)

you get a few status values:

MariaDB [test]> show status like '%audit%';
+------------------------------ +--------------+
| Variable_name                 | Value        |
+----------------------------- -+--------------+
| server_audit_active           | ON           |
| server_audit_current_log      | [SYSLOG]     |
| server_audit_last_error       |              |
| server_audit_writes_failed    | 0            |
+-------------------------------+--------------+
4 rows in set (0.00 sec)

Check that rsyslog is running :

[root@centos1 log]# service rsyslog restart
Shutting down system logger: [ OK ]
Starting system logger: [ OK ]

Now all connect and query MariaDB actions are written to the audit log in syslog.

[root@centos1 log]# tail -f /var/log/messages
Sep 21 00:07:07 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,10,QUERY,,'set global server_audit_logging=on',0
Sep 21 00:07:11 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,11,QUERY,,'show status like \'%audit%\'',0
Sep 21 00:07:21 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,12,QUERY,,'show variables like \'%audit%\'',0
Sep 21 00:10:06 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,13,QUERY,,'set global server_audit_events=\'CONNECT,QUERY\'',0
Sep 21 00:13:09 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,14,QUERY,,'SELECT DATABASE()',0
Sep 21 00:13:09 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,16,QUERY,test,'show databases',0
Sep 21 00:13:09 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,17,QUERY,test,'show tables',0
Sep 21 00:13:14 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,18,QUERY,test,'show tables',0

The fact that the log entries goes to /var/log/messages comes from the /etc/rsyslog.conf config file line

*.info;mail.none;authpriv.none;cron.none /var/log/messages

To log to a different file you just add a line to the /etc/rsyslog.conf config file

if $programname == 'mysql-server_auditing' then /var/log/mariadbaudit1

Using MariaDB Audit Plugin with the syslog system is a good choice for security and sys admins.

 

1 comment to MariaDB Audit Plugin logging to syslog

  • Antony Curtis

    I like this… It appears to be a much more functionally complete plugin than the quick/simple proof-of-concept syslog plugin which I had in 2008.

Leave a Reply

  

  

  


5 − two =

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" highlight="">