Percona MYSQL 8.0.36 audit log compression and encryption

MySQL & MariaDB
,
1

HI Guys,

am trying to perform the compression and encryption on percona mysql 8.0.36.

refer to : Audit Log Filter compression and encryption - Percona Server for MySQL

Compression

You can enable compression for any [format] by setting the audit_log_filter_compression system variable when the server starts.

The audit_log_filter_compression variable can be either of the following:

  • NONE (no compression) - the default value
  • GZIP - uses the GNU Zip compression

Encryption[¶]

You can encrypt any audit log filter file in any [format] The audit log filter plugin generates the initial password, but you can use user-defined passwords after that. The plugin stores the passwords in the keyring, so that feature must be enabled.

Set the audit_log_filter_encryption system variable with the server starts. The allowed values are the following:

  • NONE - no encryption, the default value
  • AES - AES-256-CBC (Cipher Block Chaining) encryption

But can’t find out related variables on instance:

root@localhost:mysql_jasonDB.sock [performance_schema]> select @@audit_log_filter_compression;

select @@audit_log_filter_compression

ERROR 1193 (HY000): Unknown system variable ‘audit_log_filter_compression’
root@localhost:mysql_jasonDB.sock [performance_schema]> select @@audit_log_filter_encryption;

select @@audit_log_filter_encryption

ERROR 1193 (HY000): Unknown system variable ‘audit_log_filter_encryption’
root@localhost:mysql_jasonDB.sock [performance_schema]> select audit_log_encryption_password_get();

select audit_log_encryption_password_get()

ERROR 1305 (42000): FUNCTION performance_schema.audit_log_encryption_password_get does not exist

Is there a guide to enable them on audit log?

Thanks
Jason

2

Hi jason_chen,

Have you already installed the audit plugin? If not you can do so folllowing the instructions from:

If after installing the audit plugin you still cannot find the above mentioned variables then please provide:
SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE ‘%audit%’;
SHOW variables LIKE ‘audit%’;

Regards

3

Hi C Tutte,

Thanks for your quick reply!

Here is my results:

root@localhost:mysql_jasonDB.sock [performance_schema]> select @@version;

select @@version

±----------+
| @@version |
±----------+
| 8.0.36-28 |
±----------+
1 row in set (0.00 sec)

root@localhost:mysql_jasonDB.sock [performance_schema]> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE ‘%audit%’;

SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE ‘%audit%’

*************************** 1. row ***************************
PLUGIN_NAME: audit_log
PLUGIN_VERSION: 0.2
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUDIT
PLUGIN_TYPE_VERSION: 4.1
PLUGIN_LIBRARY: audit_log.so
PLUGIN_LIBRARY_VERSION: 1.11
PLUGIN_AUTHOR: Percona LLC and/or its affiliates.
PLUGIN_DESCRIPTION: Audit log
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
1 row in set (0.00 sec)

root@localhost:mysql_jasonDB.sock [performance_schema]> SHOW variables LIKE ‘audit%’;

SHOW variables LIKE ‘audit%’

*************************** 1. row ***************************
Variable_name: audit_log_buffer_size
Value: 1048576
*************************** 2. row ***************************
Variable_name: audit_log_exclude_accounts
Value: proxy_monitor@10.29.234.18,proxy_monitor@10.29.234.19,proxy_monitor@10.29.234.20,mgr_repl_user@10.29.234.18,mgr_repl_user@10.29.234.19,mgr_repl_user@10.29.234.20
*************************** 3. row ***************************
Variable_name: audit_log_exclude_commands
Value:
*************************** 4. row ***************************
Variable_name: audit_log_exclude_databases
Value:
*************************** 5. row ***************************
Variable_name: audit_log_file
Value: /data/jasonDB/audit/audit.log
*************************** 6. row ***************************
Variable_name: audit_log_flush
Value: OFF
*************************** 7. row ***************************
Variable_name: audit_log_format
Value: JSON
*************************** 8. row ***************************
Variable_name: audit_log_handler
Value: FILE
*************************** 9. row ***************************
Variable_name: audit_log_include_accounts
Value:
*************************** 10. row ***************************
Variable_name: audit_log_include_commands
Value:
*************************** 11. row ***************************
Variable_name: audit_log_include_databases
Value:
*************************** 12. row ***************************
Variable_name: audit_log_policy
Value: ALL
*************************** 13. row ***************************
Variable_name: audit_log_rotate_on_size
Value: 209715200
*************************** 14. row ***************************
Variable_name: audit_log_rotations
Value: 200
*************************** 15. row ***************************
Variable_name: audit_log_strategy
Value: ASYNCHRONOUS
*************************** 16. row ***************************
Variable_name: audit_log_syslog_facility
Value: LOG_USER
*************************** 17. row ***************************
Variable_name: audit_log_syslog_ident
Value: percona-audit
*************************** 18. row ***************************
Variable_name: audit_log_syslog_priority
Value: LOG_INFO
18 rows in set (0.01 sec)

4

@CTutte Is that related to the PLUGIN_LIBRARY_VERSION ?

official doc writes: PLUGIN_LIBRARY_VERSION: 1.7
My env is: PLUGIN_LIBRARY_VERSION: 1.11

5

Hi again!

I was told that you are missing the audit log filter plugin:

Try installing that one and let us know if it works!

6

I would recommend uninstalling the audit log plugin before installing the audit log filter plugin.

1 Like
7

@pbirch @CTutte

Hello guys,

Sorry for response late!

Seems it works on mysql side. Thank you very much!

Installed the Audit Log Filter as below steps:

1.Create a audit database to store the JSON audit table

create database db_audit;

2.install the script: audit_log_filter_linux_install.sql
Find script:
INFRA [mysql@dc02psqldbuat04 share]# pwd
/data/percona8.0.36/share
INFRA [mysql@dc02psqldbuat04 share]# ls -l | grep audit_log_filter_linux_install.sql
-rw------- 1 mysql mysql 2316 Dec 21 15:35 audit_log_filter_linux_install.sql
Run script:
/data/percona8.0.35/bin/mysql --login-path=rootjasonDB -D db_audit < /data/percona8.0.36/share/audit_log_filter_linux_install.sql

3.Verify plugin related to audit filter:

root@localhost:mysql_jasonDB.sock [(none)]> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE ‘audit%’;
±-----------------±--------------+
| PLUGIN_NAME | PLUGIN_STATUS |
±-----------------±--------------+
| audit_log | ACTIVE |
| audit_log_filter | ACTIVE |
±-----------------±--------------+
2 rows in set (0.00 sec)

4.Verify vaiables related to audit filter:

root@localhost:mysql_jasonDB.sock [(none)]> select @@audit_log_filter_compression;
±-------------------------------+
| @@audit_log_filter_compression |
±-------------------------------+
| NONE |
±-------------------------------+
1 row in set (0.00 sec)

root@localhost:mysql_jasonDB.sock [(none)]> select @@audit_log_filter_encryption;
±------------------------------+
| @@audit_log_filter_encryption |
±------------------------------+
| AES |
±------------------------------+
1 row in set (0.00 sec)

Thanks
Jason