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

Archived Projects Percona Monitoring Plugins
1

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 (c) 2009-2020 Percona LLC and/or its affiliates

Copyright (c) 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?

2

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

3

@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.

4

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

5

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?

6

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).

7

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?

8

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

9

@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.