Using the MariaDB Audit Plugin with MySQL

Geoff MonteeMariaDB, MySQL, Security8 Comments

The MariaDB audit plugin is an audit plugin that is bundled with MariaDB server. However, even though it is bundled with MariaDB, the plugin is actually compatible with MySQL as well. In this blog post, I will describe how to install the plugin with MySQL.

Install the plugin

Unfortunately, neither MariaDB Corporation nor MariaDB Foundation currently distribute a standalone binary for the MariaDB audit plugin. That means that if you want to use this plugin with MySQL, you will have to obtain the plugin from a MariaDB server package. We can check this table to determine what version of MariaDB server that we should use. The table says that the latest version of the plugin is 1.4.0, and that this version is present in MariaDB 10.1.11. The latest release of MariaDB 10.1 is currently 10.1.19, so let’s just grab that, since that should also have the plugin:

$ wget https://downloads.mariadb.org/interstitial/mariadb-10.1.19/bintar-linux-x86_64/mariadb-10.1.19-linux-x86_64.tar.gz

Let’s extract the tarball and copy the plugin library from the tarball’s plugin directory to MySQL’s plugin directory:

$ tar -xzf mariadb-10.1.19-linux-x86_64.tar.gz
$ ls -l mariadb-10.1.19-linux-x86_64/lib/plugin/ | grep "audit"
-rwxr-xr-x 1 ec2-user ec2-user 176024 Nov 4 09:37 server_audit.so
$ sudo install mariadb-10.1.19-linux-x86_64/lib/plugin/server_audit.so /usr/lib64/mysql/plugin/

Now that the plugin library is in MySQL’s plugin directory, we can tell MySQL to install it:

$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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)

Configure the plugin

Now that the plugin is installed, we can configure it. For example, if we want to log all 6 event types, but we want to exclude the user named root, then we could add the following to MySQL’s configuration file:

server_audit_logging=ON
server_audit_events=connect,query,table,query_ddl,query_dml,query_dcl
server_audit_excl_users=root

And then restart the server:

$ sudo systemctl restart mysqld

At that point, audit logging will be enabled!

For more information on configuring MariaDB’s audit plugin, see this documentation page.

Has anyone used the MariaDB audit plugin with MySQL?

8 Comments on “Using the MariaDB Audit Plugin with MySQL”

  1. i followed your method.version: mariadb 10.1.19,mysql 5.7.15。i got an error while connecting to server via mysql:ERROR 2013 (HY000): Lost connection to MySQL server at ‘reading authorization packet’, system error: 95. i do not know how to solve the problem,would you please give me some advice,thans!

    1. Hi Andy,

      I’m not sure why you are seeing that message. Does the MySQL error log contain any useful information that could provide some clues about what went wrong?

      This may also help: https://dev.mysql.com/doc/refman/5.7/en/error-lost-connection.html

  2. It is possible that mysqld could use up to
    key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 206863 K bytes of memory
    Hope that’s ok; if not, decrease some variables in the equation.

    Thread pointer: 0x7f2624000fa0
    Attempting backtrace. You can use the following information to find out
    where mysqld died. If you see no messages after this, something went
    terribly wrong…
    stack_bottom = 7f263c4f5ea8 thread_stack 0x40000
    /usr/local/mysql/bin/mysqld(my_print_stacktrace+0x35)[0xf1e045]
    /usr/local/mysql/bin/mysqld(handle_fatal_signal+0x4a4)[0x79d404]
    /lib64/libpthread.so.0(+0xf100)[0x7f266085d100]
    /usr/local/mysql/lib/plugin/server_audit.so(auditing+0x4f4)[0x7f264409c324]
    /usr/local/mysql/lib/plugin/server_audit.so(+0x940d)[0x7f264409f40d]
    /usr/local/mysql/bin/mysqld(_Z18mysql_audit_notifyP3THD30mysql_event_general_subclass_tiPKcm+0x262)[0x79e032]
    /usr/local/mysql/bin/mysqld(_ZN12Query_logger17general_log_printEP3THD19enum_server_commandPKcz+0xad)[0xc4267d]
    /usr/local/mysql/bin/mysqld(_Z15acl_log_connectPKcS0_S0_S0_P3THD19enum_server_command+0xa5)[0x7a3e95]
    /usr/local/mysql/bin/mysqld(_Z16acl_authenticateP3THD19enum_server_command+0xaa1)[0x7a5ec1]
    /usr/local/mysql/bin/mysqld[0xcb8475]
    /usr/local/mysql/bin/mysqld(_Z22thd_prepare_connectionP3THD+0x5e)[0xcb88de]
    /usr/local/mysql/bin/mysqld(handle_connection+0x279)[0xdc5a39]
    /usr/local/mysql/bin/mysqld(pfs_spawn_thread+0x174)[0x11d6ae4]
    /lib64/libpthread.so.0(+0x7dc5)[0x7f2660855dc5]
    /lib64/libc.so.6(clone+0x6d)[0x7f265f51dced]

    Trying to get some variables.
    Some pointers may be invalid and cause the dump to abort.
    Query (0): is an invalid pointer
    Connection ID (thread ID): 4
    Status: NOT_KILLED

    1. Hi Andy,

      That looks like a bug. I created a bug report for it:

      https://jira.mariadb.org/browse/MDEV-11510

  3. Thank you. Geoff can I use it for monitoring specific user ? I want to know when He connected or what query executed this user ?

    1. Hi Hamoon,

      If you want to configure auditing for only specific user accounts, then you can specify those accounts in server_audit_incl_users.

  4. Hi Geoff Montee,

    any update on the bug that you raised?
    I’m also facing the same problem.
    Did you find any solution for that?
    I removed those new configuration lines from file, but still it is not reverting back to old state. Still giving the same issue.

    1. Hi Raja,

      The JIRA issue says that the bug has been fixed, and that the fix will be present in MariaDB 5.5.54. MariaDB 5.5 fixes are merged upwards, so this fix will also most likely be present in 10.0.29 and 10.1.21.

      If you want to uninstall the plugin until then, it might be worth trying to start the server with skip-grant-tables, which will prevent the plugin from being loaded. Then you can use UNINSTALL PLUGIN to uninstall it.

      http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_skip-grant-tables

      https://dev.mysql.com/doc/refman/5.6/en/uninstall-plugin.html

Leave a Reply

Your email address will not be published. Required fields are marked *

1,822 Spambots Blocked by Simple Comments