MySQL : Auditing using Percona XDB plugin

Share via:

Dear Readers,

In this article, we will see Auditing using Percona XDB plugin.

Database auditing is the monitoring of selected actions of database users. It doesn’t protect the database in case privileges are set incorrectly, but it can help the administrator detect mistakes Audits are needed for security. You can track data access and be alerted to suspicious activity. Audits are required for data integrity. They are the only way to validate that changes made to data are correct and legal.
There are several auditing plugin available for MySQL and some does support Percona Xtradb. Each plugin differ in record format, filtering capabilities and verbosity of log records.
MySQL Enterprise Audit Plugin :
👉 This plugin is not open source and is only available with MySQL Enterprise, which has a significant cost attached to it.
👉 It is the most stable and robust.
Percona Audit Log Plugin :
👉 Percona provides an open source auditing solution that installs with Percona Server 5.5.37+ and 5.6.17+.
👉 This plugin has quite a few output features as it outputs XML, JSON and to syslog.
👉 Percona’s implementation is the first to be a drop-in replacement for MySQL Enterprise Audit Plugin.
👉 As it has some internal hooks to the server to be feature-compatible with Oracle’s plugin, it is not available as a standalone for other versions of MySQL.
👉 This plugin is actively maintained by Percona.
McAfee MySQL Audit Plugin :
👉 Around the longest and has been used widely.
👉 It is open source and robust, while not using the official auditing API.
👉 It isn’t updated as often as one may like. There hasn’t been any new features in some time.
👉 It was recently updated to support MySQL 5.7.
MariaDB Audit Plugin :
👉 The only plugin that claims to support MySQL, Percona Server and MariaDB.
👉 It is open source and constantly upgraded with new versions of MariaDB.
👉 It is installed with MariaDB or available as a plugin for Percona Server and MySQL.
👉 MariaDB audit plugin has fine level of audit logging.
👉 It can log Connections, Queries, DDL, DML and DCL operations all together or just specific ones.
👉 In this topic we’ll discuss about installing and configuring Percona Audit plugin to log all DDL operations.

Install the plugin:

Verify plugin installation:

Set audit logging parameters to log DDL operations:

Verify:

Test the audit plugin:

Changing to different logging format:
The log format choice is controlled by audit_log_format variable and supports four log formats: OLD, NEW, JSON, and CSV

#Example of the OLD format:

#Example of the NEW format:

#Example of the JSON format:

#Example of the CSV format:

Enabling the auditing permanently:
If you want the auditing plugin to be enabled permanently, after installing the plugin, add the changes to my.cnf and restart Percona Xtradb.

 

Thank you for giving your valuable time to read the above information.
Follow us on 
Website               www.ktexperts.com
Facebook Page KTexperts
Linkedin Page   : KT EXPERTS

Follow Me On
Linkedin :Anban Malarvendan

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment