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

Archived Projects Percona Monitoring Plugins
1

Hi,

I’m testing the audit plugin on percona-server 5.7.22 and i want to exclude a user from the logs, however audit_log_exclude_accounts/audit_log_include_accounts parameters doesn’t seem to change the logging.

log example:
{“audit_record”:{“name”:“Query”,“record”:“703416_2018-11-26T13:16:09”,“timestamp”:“2018-11-26T13:18:28 UTC”,“command_class”:“create_procedure”,“connection_id”:“96794”,“status”:0,“sqltext”:“insert into schema.table (id, master_ts, update_by) values (1, NOW(), ‘event_scheduler’) on duplicate key update master_ts=NOW(), update_by=VALUES(update_by)”,“user”:“user_test[user_test] @ localhost [localhost]”,“host”:“localhost”,“os_user”:“”,“ip”:“localhost”,“db”:“”}}

i tried several variations on “user_test[user_test] @ localhost [localhost]”
then i tried to do audit_log_include_accounts = “my_admin@localhost” to log only queries from my_admin user, also without effect

configuration:
audit_log_format = JSON
audit_log_file = /var/mysql/prod/audit.log
audit_log_exclude_databases = test_db
audit_log_exclude_accounts = user_test@localhost
audit_log_policy = QUERIES

Anyone managed to filter by user?

Thanks

2

Hello, I’m seeing this same issue with version 5.7.24-27. Any feedback on this yet?

3

Could you please send output of SHOW VARIABLES LIKE ‘%audit%’? I want to check if settings from the configuration file applied to the variables.

4

mysql> show variables like ‘%audit%’;
±----------------------------±---------------------------------------------+
| Variable_name | Value |
±----------------------------±---------------------------------------------+
| audit_log_buffer_size | 1048576 |
| audit_log_exclude_accounts | someuser@ |
| audit_log_exclude_commands | |
| audit_log_exclude_databases | |
| audit_log_file | /opt/sw/dbinstall/mysql/mysql-logs/audit.log |
| audit_log_flush | OFF |
| audit_log_format | OLD |
| audit_log_handler | FILE |
| audit_log_include_accounts | |
| audit_log_include_commands | |
| audit_log_include_databases | |
| 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 |
±----------------------------±---------------------------------------------+
18 rows in set (0.03 sec)

5

I am having the same problem, Does this feature wok or is this a known bug?

6

Hello,

I have the same problem. Have someone any solution or it is permanently bug?

7

For me, making the change to the configuration file and restarting MySQL worked.

8

@grosenthal I am sorry: missed your reply. But in your case configuration changes were not applied to the plugin variables. Please check if you restarted the server after changing configuration file and if yes: if the configuration file located in the location which mysqld reads at the startup.

Others who experience the same problem, please, make same checks: what the query “show variables like ‘%audit%’;” outputs and if it is the same as you expect.

9

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?

10

@hspinks first, you cannot use both audit_log_include_accounts and audit_log_exclude_accounts: they are mutually exclusive. Second, you can use wildcards in usernames, but only if they match records in the mysql.user table. See also https://www.percona.com/doc/percona-server/8.0/management/audit_log_plugin.html#filtering-by-user If you need more smart filtering: file a feature request at https://jira.percona.com