Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Saturday, 19 May 2018

MySQL Auditing with MariaDB Audit Plugin

MySQL Auditing for community edition with
MariaDB Audit Plug-in

MySQL Enterprise Edition has in-build feature for database Auditing. But if you needed to record user access to be in compliance with auditing regulations for MySQL Community Edition , You have to deploy some third party plug-in because of Auditing features is currently not available with MySQL Community Edition.

MariaDB Audit Plug-in is one of the solutions developed by MariaDB Corporation to achieve Auditing for MySQL Community Edition.


How to deploy MariaDB Audit Plugin

The 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 MySQL. You can follow below instruction one by one...

ØDownload the latest Plugin from https://downloads.mariadb.com/Audit-Plugin/MariaDB-Audit-Plugin 

ØExtract "server_audit.so" file and put it on Plugin directory

ØCopy “server_audit.so” in above Plugin directory

ØAdd below parameter  in my.cnf file 
    [mysqld]
    plugin_load=server_audit=server_audit.so 

ØRestart mysqld service ;

ØShow plugins;

Check for plugin server_audit   if plugin still not loaded then you need to install manual one time using below INSTALL PLUGIN command.

ØINSTALL PLUGIN server_audit SONAME 'server_audit.so';

ØAgain Show plugins;

ØCreate Audit Log Directory

[alok@localhost ~]$ sudo mkdir /var/lib/mysql/AuditLog

ØProvide full permission to log directory

[alok@localhost ~]$ sudo chmod -R 777 /var/lib/mysql/AuditLog

ØNow add others plugin parameter as per need in my.cnf

(Kindly refer and read all the parameter meaning and config from https://mariadb.com/kb/en/library/mariadb-audit-plugin/ )

[mysqld]
plugin_load=server_audit=server_audit.so
server_audit=FORCE_PLUS_PERMANENT
server_audit_events=CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML,QUERY_DCL
server-audit-file-path=/var/lib/mysql/AuditLog/audit.csv     {if not set then it will create server_audit.log in Data Dir}
server_audit_excl_users=root
#server_audit_incl_users=aloktest
server_audit_file_rotate_size= 104857600    {in Byte ‘Herein 100MB defined}
server_audit_file_rotations= 500 {Max number of files after that it will overwrite}

server_audit_logging=ON

[Note: You must have full permission (777) in audit directory (/var/lib/mysql/AuditLog) otherwise Plugin failed to create audit file.]

ØRestart mysqld service;

mysql> SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='SERVER_AUDIT' \G;
*************************** 1. row ******************
PLUGIN_NAME: SERVER_AUDIT
PLUGIN_VERSION: 1.4
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUDIT
PLUGIN_TYPE_VERSION: 3.2
PLUGIN_LIBRARY: server_audit.so
PLUGIN_LIBRARY_VERSION: 1.4
PLUGIN_AUTHOR:  Alexey Botchkov (MariaDB Corporation)
PLUGIN_DESCRIPTION: Audit the server activity
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
1 row in set (0.00 sec)

=====================================================

ØCheck all configured variables is working or not.


ØNow MariaDB Audit Plugin has been deployed. Let’s check log files and observe the same.












Status Monitoring
You may want to monitor routinely the status of the auditing plugin. The SHOW GLOBAL STATUS statement can assist you in this. On the server, execute the


Idea Configuration for Server

[mysqld]

#explicit_defaults_for_timestamp = 1
plugin_load=server_audit=server_audit.so
server_audit=FORCE_PLUS_PERMANENT
server_audit_events=CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML,QUERY_DCL
server-audit-file-path=/var/lib/mysql/AuditLog/audit.csv
server_audit_excl_users=root
#server_audit_incl_users=aloksingh
server_audit_file_rotate_size= 104857600
server_audit_file_rotations= 500
server_audit_logging=ON

No comments:

Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...