Audit plugin include/exclude accounts doesn't seem to work

I have tried to configure my audit plugin to exclude application users but to include all other users. This is the output from MySQL show variables like ‘%audit%’;

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2160760

Server version: 5.7.31-34 Percona Server (GPL), Release 34, Revision 2e68637

Copyright © 2009-2020 Percona LLC and/or its affiliates

Copyright © 2000, 2020, 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 variables like ‘%audit%’ ;

±----------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Variable_name | Value |

±----------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| audit_log_buffer_size | 1048576 |

| audit_log_exclude_accounts | |

| audit_log_exclude_commands | |

| audit_log_exclude_databases | |

| audit_log_file | audit.log |

| audit_log_flush | OFF |

| audit_log_format | JSON |

| audit_log_handler | FILE |

| audit_log_include_accounts | users1@localhost,user2@localhost |

| audit_log_include_commands | delete,delete_multi,execute_sql,insert,load,truncate,update,update_multi,select,replace_select,insert_select |

| audit_log_include_databases | talksoft_qa |

| audit_log_policy | QUERIES |

| audit_log_rotate_on_size | 1073741824 |

| audit_log_rotations | 5 |

| audit_log_strategy | PERFORMANCE |

| audit_log_syslog_facility | LOG_USER |

| audit_log_syslog_ident | percona-audit |

| audit_log_syslog_priority | LOG_INFO |

±----------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

18 rows in set (0.00 sec)

I am still getting log entries from users NOT on this list. Is this a known bug or is there a fix for this?

Hi, did you restart the server after making the changes? changes to filtering rules do not apply to existing server connections.

@igroene , thanks for that answer! So with that logic, any change to the audit plugin filter, like adding or removing a user requires an outage / restart of MySQL? If that is the case, the documentation does not suggest that, unless “existing connections” in the documentation means any running database instance. I will try that, but if this is the case, an update to the documentation should be considered. Thanks though for your help and I will try making changes to the configuration file and restarting MySQL at my first opportunity.

Hi, you don’t need to restart the database. Eventually as app/user connections are dropped and established, the new ones will start being audited as expected. Also this fact is mentioned here as a note: https://www.percona.com/doc/percona-server/8.0/management/audit_log_plugin.html#filtering-by-user

hope that helps

If I am to conclude that each line in the audit.log file is a new connection, then there are many newly created connections from a user that should be excluded from logging in my log file now. I made my changes about a day ago now and I am still seeing users that have not been included in my configuration. Without a restart, how will I know when the configuration changes to these dynamic settings have taken affect?

As I mentioned, the dynamic change is immediate for new user/app connections. Existing connections will continue to be logged until they disconnect gracefully, or they are killed (or server restarts).

I am still having a problem EXCLUDING users. I have been able to INCLUDE users to be logged with the following syntax;

audit_log_include_accounts=user1@localhost,user2@localhost…

What I really need to do is to EXCLUDE users, but the usernames in the log files comes in the following format;

user1[databasename] @ hostname.domanainname.com [ip address]

This is the format of what I want to exclude, but there are many users with many usernames, database names and hostnames.

How to I use wild cards in the exclude entry in the config file to exclude common usernames from ANY systems that contains spaces and special characters?

Unfortunately you cannot combine both include and exclude accounts functionality. You need to use the same user/host combination as they exist in mysql.user table.

https://www.percona.com/doc/percona-server/8.0/management/audit_log_plugin.html#filtering-by-user for more info and some examples

@igroene Thanks for that answer, but can I use a wild card for a user name. for instance, can I exclude “user1@%”? Meaning “user1”’ from any host.