MariaDB Audit Plugin

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

 

The post MariaDB Audit Plugin appeared first on MySQL Consulting, Support and Remote DBA Services By MinervaDB.

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569