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:
|
1 2 |
mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so'; Query OK, 0 rows affected (0.01 sec) |
Verify plugin installation:
|
1 2 3 4 5 6 |
mysql> SHOW PLUGINS; +-----------------------------+----------+--------------------+--------------+---------+ | Name | Status | Type | Library | License | +-----------------------------+----------+--------------------+--------------+---------+ | audit_log | ACTIVE | AUDIT | audit_log.so | GPL | +-----------------------------+----------+--------------------+--------------+---------+ |
Set audit logging parameters to log DDL operations:
|
1 2 |
mysql> SET GLOBAL audit_log_include_commands= 'create_db, create_event, create_func, create_proc, create_table, create_trigger, drop_compression_dictionary, truncate, rename_user, rename_table, drop_db, drop_event, drop_function, drop_index, drop_procedure, drop_server, drop_table, drop_trigger, drop_user, drop_view, alter_db, alter_db_upgrade, alter_event, alter_function, alter_procedure, alter_server, alter_table, alter_tablespace, alter_user'; Query OK, 0 rows affected (0.00 sec) |
Verify:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
mysql> show global variables like 'audit%'\G; *************************** 1. row *************************** Variable_name: audit_log_buffer_size Value: 1048576 *************************** 2. row *************************** Variable_name: audit_log_exclude_accounts Value: *************************** 3. row *************************** Variable_name: audit_log_exclude_commands Value: *************************** 4. row *************************** Variable_name: audit_log_file Value: audit.log *************************** 5. row *************************** Variable_name: audit_log_flush Value: OFF *************************** 6. row *************************** Variable_name: audit_log_format Value: OLD *************************** 7. row *************************** Variable_name: audit_log_handler Value: FILE *************************** 8. row *************************** Variable_name: audit_log_include_accounts Value: *************************** 9. row *************************** Variable_name: audit_log_include_commands Value: create_db, create_event, create_func, create_proc, create_table, create_trigger, drop_compression_dictionary, truncate, rename_user, rename_table, drop_db, drop_event, drop_function, drop_index, drop_procedure, drop_server, drop_table, drop_trigger, drop_user, drop_view, alter_db, alter_db_upgrade, alter_event, alter_function, alter_procedure, alter_server, alter_table, alter_tablespace, alter_user *************************** 10. row *************************** Variable_name: audit_log_policy Value: ALL *************************** 11. row *************************** Variable_name: audit_log_rotate_on_size Value: 0 *************************** 12. row *************************** Variable_name: audit_log_rotations Value: 0 *************************** 13. row *************************** Variable_name: audit_log_strategy Value: ASYNCHRONOUS *************************** 14. row *************************** Variable_name: audit_log_syslog_facility Value: LOG_USER *************************** 15. row *************************** Variable_name: audit_log_syslog_ident Value: percona-audit *************************** 16. row *************************** Variable_name: audit_log_syslog_priority Value: LOG_INFO 16 rows in set (0.00 sec) |
Test the audit plugin:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> create database 'audit_test'; mysql> exit tail -50 /var/lib/mysql/audit.log <AUDIT_RECORD NAME="Query" RECORD="16_2020-11-12T09:00:15" TIMESTAMP="2020-11-13T04:05:54 UTC" COMMAND_CLASS="create_db" CONNECTION_ID="4" STATUS="0" SQLTEXT="create database audit_test" USER="root[root] @ localhost []" HOST="localhost" OS_USER="" IP="" DB="" /> |
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
|
1 |
mysql> SET GLOBAL audit_log_format = 'JSON'; |
#Example of the OLD format:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<AUDIT_RECORD "NAME"="Query" "RECORD"="2_2014-04-28T09:29:40" "TIMESTAMP"="2014-04-28T09:29:40 UTC" "COMMAND_CLASS"="install_plugin" "CONNECTION_ID"="47" "STATUS"="0" "SQLTEXT"="INSTALL PLUGIN audit_log SONAME 'audit_log.so'" "USER"="root[root] @ localhost []" "HOST"="localhost" "OS_USER"="" "IP"="" /> |
#Example of the NEW format:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
<AUDIT_RECORD> <NAME>Quit</NAME> <RECORD>10902_2014-04-28T11:02:54</RECORD> <TIMESTAMP>2014-04-28T11:02:59 UTC</TIMESTAMP> <CONNECTION_ID>36</CONNECTION_ID> <STATUS>0</STATUS> <USER></USER> <PRIV_USER></PRIV_USER> <OS_LOGIN></OS_LOGIN> <PROXY_USER></PROXY_USER> <HOST></HOST> <IP></IP> <DB></DB> </AUDIT_RECORD> |
#Example of the JSON format:
|
1 |
{"audit_record":{"name":"Query","record":"4707_2014-08-27T10:43:52","timestamp":"2014-08-27T10:44:19 UTC","command_class":"show_databases","connection_id":"37","status":0,"sqltext":"show databases","user":"root[root] @ localhost []","host":"localhost","os_user":"","ip":""}} |
#Example of the CSV format:
|
1 |
"Query","49284_2014-08-27T10:47:11","2014-08-27T10:47:23 UTC","show_databases","37",0,"show databases","root[root] @ localhost []","localhost","","" |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# log only queries audit_log_policy=QUERIES # log events in JSON format audit_log_format=JSON # log file path audit_log_file=/var/lib/mysql/audit.log # rotate log once it hits 1GB audit_log_rotate_on_size=1024M # retain the recent 10 files audit_log_rotations=10 # log all DDL operations audit_log_include_commands= 'create_db, create_event, create_func, create_proc, create_table, create_trigger, drop_compression_dictionary, truncate, rename_user, rename_table, drop_db, drop_event, drop_function, drop_index, drop_procedure, drop_server, drop_table, drop_trigger, drop_user, drop_view, alter_db, alter_db_upgrade, alter_event, alter_function, alter_procedure, alter_server, alter_table, alter_tablespace, alter_user'; |
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
(2 votes, average: 4.50 out of 5)
Loading...
