Audit Log Plugin audit_log_exclude_accounts setting incompatible when host contains ip address

[eroomydna]

If we set the value of option audit_log_exclude_accounts to user@ipaddr the audit plugin will ignore and continue to log the account(s) activity.

###### 5.6.35 ######
mysql> select @@version;
+-------------+
| @@version |
+-------------+
| 5.6.35-81.0 |
+-------------+
1 row in set (0.00 sec)

mysql> set global audit_log_exclude_accounts='andrew@::1';
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'audit';
Empty set (0.00 sec)

mysql> show global variables like 'audit%';
+----------------------------+--------------------------+
| Variable_name | Value |
+----------------------------+--------------------------+
| audit_log_buffer_size | 1048576 |
| audit_log_exclude_accounts | andrew@::1 |
| audit_log_exclude_commands | set_option,create_db |
| audit_log_file | /var/log/mysql/audit.log |
| audit_log_flush | OFF |
| audit_log_format | JSON |
| audit_log_handler | FILE |
| audit_log_include_accounts | |
| audit_log_include_commands | |
| audit_log_policy | ALL |
| audit_log_rotate_on_size | 1073741824 |
| audit_log_rotations | 10 |
| audit_log_strategy | PERFORMANCE |
| audit_log_syslog_facility | LOG_USER |
| audit_log_syslog_ident | percona-audit |
| audit_log_syslog_priority | LOG_INFO |
+----------------------------+--------------------------+
16 rows in set (0.00 sec)

mysql> exit
Bye
mysql@c5ff2759530c:/$ mysql -uandrew -ppass --protocol=tcp
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.6.35-81.0 Percona Server (GPL), Release 81.0, Revision c96c427

Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

##### /var/log/mysql/audit.log ######
...
{"audit_record":{"name":"Query","record":"6850515_2017-03-30T10:29:04","timestamp":"2017-03-30T11:06:33 UTC","command_class":"select","connection_id":"16","status":0,"sqltext":"select @@version","user":"andrew[andrew] @ [::1]","host":"","os_user":"","ip":"::1","db":""}}

This does not occur in 5.7.16+ Audit Plugin.

Semi-Workaround is possible by omitting the ipaddr if your intentions are for all accounts with the username to be excluded from audit logging.

setting audit_log_exclude_accounts to filter on 'user@' will cover that requirement.

[Jericho Rivera (jericho-rivera)]

Still reproducible in latest 5.6.36 builds

+------+------------+
| user | host |
+------+------------+
| aud | 10.0.3.194 |
| aud1 | centos7 |
+------+------------+

+----------------------------+----------------+
| Variable_name | Value |
+----------------------------+----------------+
| audit_log_buffer_size | 1048576 |
| audit_log_exclude_accounts | aud@10.0.3.194 |
| audit_log_exclude_commands | |
| audit_log_file | audit.log |
| audit_log_flush | OFF |
| audit_log_format | OLD |
| audit_log_handler | FILE |
| audit_log_include_accounts | |
| audit_log_include_commands | |
| audit_log_policy | ALL |
| audit_log_rotate_on_size | 0 |
| audit_log_rotations | 0 |
| audit_log_strategy | ASYNCHRONOUS |
| audit_log_syslog_facility | LOG_USER |
| audit_log_syslog_ident | percona-audit |
| audit_log_syslog_priority | LOG_INFO |
+----------------------------+----------------+

User with IP in hostname is logged while user with hostname is not logged, hence exclude_accounts only works when non-IP addresses are used.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3677