MariaDB DBAs are accountable for auditing database infrastructure operations to proactively troubleshoot performance and operational issues, MariaDB Audit Plugin is capable of auditing the database operations of both MariaDB and MySQL. MariaDB Audit Plugin is provided as a dynamic library: server_audit.so (server_audit.dll for Windows). The plugin must be located in the plugin directory, the directory containing all plugin libraries for MariaDB.
MariaDB [(none)]> select @@plugin_dir; +--------------------------+ | @@plugin_dir | +--------------------------+ | /usr/lib64/mysql/plugin/ | +--------------------------+ 1 row in set (0.000 sec)
There are two ways you can install MariaDB Audit Plugin:
INSTALL SONAME statement while logged into MariaDB, You need to use administrative account which has INSERT privilege for the mysql.plugin table.
MariaDB [(none)]> INSTALL SONAME 'server_audit'; Query OK, 0 rows affected (0.012 sec) MariaDB [(none)]>
Load Plugin at Start-Up
The plugin can be loaded by setting -plugin_load system variable in my.cnf (my.ini in windows)
[mysqld] # # include all files from the config directory # !includedir /etc/my.cnf.d plugin_load=server_audit=server_audit.so
System variables to configure MariaDB Audit Plugin
MariaDB Audit Plugin is highly configurable, Please find below the system variables available for MariaDB Audit Plugin:
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)
configure system variable server_audit_events for auditing MariaDB transaction events:
MariaDB [(none)]> SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE'; Query OK, 0 rows affected (0.008 sec)
Enable MariaDB Audit Plugin
MariaDB [(none)]> set global server_audit_logging=on; Query OK, 0 rows affected (0.007 sec)
MariaDB Audit Plugin creates audit log file “server_audit.log” on path /var/lib/mysql/
Testing MariaDB Audit Plugin
MariaDB [employees]> update employees
-> set last_name='Gupta'
-> where emp_no= 499999;
Query OK, 1 row affected (0.010 sec)
Rows matched: 1 Changed: 1 Warnings: 0
[root@localhost mysql]# tail -f server_audit.log 20180612 20:32:07,localhost.localdomain,root,localhost,16,433,QUERY,,'SHOW GLOBAL VARIABLES LIKE \'%server_audit%\'',0 20180612 20:32:26,localhost.localdomain,root,localhost,16,434,QUERY,,'update employees set last_name=\'Gupta\' where emp_no= 499999',1046 20180612 20:32:37,localhost.localdomain,root,localhost,16,435,QUERY,,'SELECT DATABASE()',0 20180612 20:32:37,localhost.localdomain,root,localhost,16,437,QUERY,employees,'show databases',0 20180612 20:32:37,localhost.localdomain,root,localhost,16,438,QUERY,employees,'show tables',0 20180612 20:32:41,localhost.localdomain,root,localhost,16,447,WRITE,employees,employees, 20180612 20:32:41,localhost.localdomain,root,localhost,16,447,READ,employees,dept_emp, 20180612 20:32:41,localhost.localdomain,root,localhost,16,447,READ,employees,dept_manager, 20180612 20:32:41,localhost.localdomain,root,localhost,16,447,QUERY,employees,'update employees set last_name=\'Gupta\' where emp_no= 499999',0
How can we block UNINSTALL PLUGIN ?
The INSTALL PLUGIN statement can be used to uninstall a plugin but you can disable this by adding following line in my.cnf after plugin is loaded once:
[mysqld] # # include all files from the config directory # !includedir /etc/my.cnf.d plugin_load=server_audit=server_audit.so server_audit=FORCE_PLUS_PERMANENT
