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.\u00a0MariaDB Audit Plugin is provided as a dynamic library: server_audit.so (server_audit.dll for Windows).\u00a0 The plugin must be located in the plugin directory, the directory containing all plugin libraries for MariaDB.<\/p>\n

MariaDB [(none)]> select @@plugin_dir; \r\n+--------------------------+\r\n| @@plugin_dir             |\r\n+--------------------------+\r\n| \/usr\/lib64\/mysql\/plugin\/ |\r\n+--------------------------+\r\n1 row in set (0.000 sec)\r\n<\/pre>\n
There are two ways you can install MariaDB Audit Plugin:<\/p>\n
INSTALL SONAME statement while logged into MariaDB, You need to use administrative account which has INSERT privilege for the mysql.plugin table.<\/p>\n
MariaDB [(none)]> INSTALL SONAME 'server_audit';\r\nQuery OK, 0 rows affected (0.012 sec)\r\n\r\nMariaDB [(none)]> \r\n<\/pre>\n
Load Plugin at Start-Up\u00a0<\/strong><\/p>\n
The plugin can be loaded by setting -plugin_load\u00a0<\/em>system variable in my.cnf (my.ini in windows)<\/p>\n
[mysqld]\r\n#\r\n# include all files from the config directory\r\n#\r\n!includedir \/etc\/my.cnf.d\r\n\r\nplugin_load=server_audit=server_audit.so<\/pre>\n
System variables to configure MariaDB Audit Plugin<\/strong><\/p>\n
MariaDB Audit Plugin is highly configurable, Please find below the\u00a0system variables\u00a0<\/em>available for MariaDB Audit Plugin:<\/p>\n
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%server_audit%';\r\n+-------------------------------+-----------------------+\r\n| Variable_name                 | Value                 |\r\n+-------------------------------+-----------------------+\r\n| server_audit_events           |                       |\r\n| server_audit_excl_users       |                       |\r\n| server_audit_file_path        | server_audit.log      |\r\n| server_audit_file_rotate_now  | OFF                   |\r\n| server_audit_file_rotate_size | 1000000               |\r\n| server_audit_file_rotations   | 9                     |\r\n| server_audit_incl_users       |                       |\r\n| server_audit_logging          | OFF                   |\r\n| server_audit_mode             | 0                     |\r\n| server_audit_output_type      | file                  |\r\n| server_audit_query_log_limit  | 1024                  |\r\n| server_audit_syslog_facility  | LOG_USER              |\r\n| server_audit_syslog_ident     | mysql-server_auditing |\r\n| server_audit_syslog_info      |                       |\r\n| server_audit_syslog_priority  | LOG_INFO              |\r\n+-------------------------------+-----------------------+\r\n15 rows in set (0.001 sec)\r\n<\/pre>\n
configure\u00a0system variable\u00a0server_audit_events\u00a0<\/em>for auditing MariaDB transaction events:<\/p>\n
MariaDB [(none)]> SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE';\r\nQuery OK, 0 rows affected (0.008 sec)\r\n<\/pre>\n
Enable MariaDB Audit Plugin<\/p>\n
MariaDB [(none)]> set global server_audit_logging=on;\r\nQuery OK, 0 rows affected (0.007 sec)\r\n<\/pre>\n
MariaDB Audit Plugin creates audit log file “server_audit.log” on path \u00a0\/var\/lib\/mysql\/\u00a0<\/em><\/p>\n
Testing MariaDB Audit Plugin\u00a0<\/strong><\/p>\n
MariaDB [employees]> update employees\r\n    -> set last_name='Gupta'\r\n    -> where emp_no= 499999;\r\nQuery OK, 1 row affected (0.010 sec)\r\nRows matched: 1  Changed: 1  Warnings: 0\r\n<\/pre>\n
[root@localhost mysql]# tail -f  server_audit.log\r\n20180612 20:32:07,localhost.localdomain,root,localhost,16,433,QUERY,,'SHOW GLOBAL VARIABLES LIKE \\'%server_audit%\\'',0\r\n20180612 20:32:26,localhost.localdomain,root,localhost,16,434,QUERY,,'update employees set last_name=\\'Gupta\\' where emp_no= 499999',1046\r\n20180612 20:32:37,localhost.localdomain,root,localhost,16,435,QUERY,,'SELECT DATABASE()',0\r\n20180612 20:32:37,localhost.localdomain,root,localhost,16,437,QUERY,employees,'show databases',0\r\n20180612 20:32:37,localhost.localdomain,root,localhost,16,438,QUERY,employees,'show tables',0\r\n20180612 20:32:41,localhost.localdomain,root,localhost,16,447,WRITE,employees,employees,\r\n20180612 20:32:41,localhost.localdomain,root,localhost,16,447,READ,employees,dept_emp,\r\n20180612 20:32:41,localhost.localdomain,root,localhost,16,447,READ,employees,dept_manager,\r\n20180612 20:32:41,localhost.localdomain,root,localhost,16,447,QUERY,employees,'update employees set last_name=\\'Gupta\\' where emp_no= 499999',0<\/pre>\n
How can we block UNINSTALL PLUGIN ?<\/strong><\/p>\n
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:<\/p>\n
[mysqld]\r\n#\r\n# include all files from the config directory\r\n#\r\n!includedir \/etc\/my.cnf.d\r\n\r\nplugin_load=server_audit=server_audit.so\r\nserver_audit=FORCE_PLUS_PERMANENT<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
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.\u00a0MariaDB Audit Plugin is provided […]<\/a><\/p>\n<\/div>","protected":false},"author":10,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[92,137,2],"tags":[96,138,5],"yoast_head":"\n