MySQL Audit Plugin changes not applied

MySQL & MariaDB
1

Hi, we have percona server MySQL 5.7, we have enabled default audit plugin.We update exclude accounts and commands variables but changes do not applied, looks like restart will help but it is production database and restarts are something we try to avoid.Any other way to make audit plugin recognize changes?

1 Like
2

Hi, the include/exclude variables are dynamic so you should be able to change them without a restart. Can you describe the exact steps you are following and what is not working as expected?

1 Like
3

Well, I use following commands to set variables.

set global audit_log_exclude_accounts='appuser1,appuser2';
set global audit_log_exclude_commands='select';

and show variables like “audit%” command shows following.But when I check logs these users queries are logged, all select queries also logged.

'audit_log_exclude_accounts': 'appuser1,appuser2'
'audit_log_exclude_commands': 'select'
1 Like
4

I think what might be happening is that the parameter changes you made are not affecting pre-existing user connections. This is mentioned as a warning in the documentation page. You can restart app servers in a rolling fashion or kill user connections and problem should go away.

1 Like
5

Well I have tried it with my user on workbench, Added my user to exclude then termiate my session, open again .Then did some select queries and they have logged. Any other ideas? am I only person has this problem?

1 Like
6

Ok, can you post the complete list of variables related to the plugin? feel free to obfuscate any confidential information:

show global variables like ‘%audit%’;

Also just noticed that you are not specifying the complete user@host format as it is seen in mysql.user table. Try that as well!

1 Like
7

Here you are

'audit_log_buffer_size', '1048576'
'audit_log_exclude_accounts', 'appuser1,appuser2'
'audit_log_exclude_commands', 'select'
'audit_log_exclude_databases', ''
'audit_log_file', 'audit.log'
'audit_log_flush', 'OFF'
'audit_log_format', 'NEW'
'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', '524288000'
'audit_log_rotations', '30'
'audit_log_strategy', 'ASYNCHRONOUS'
'audit_log_syslog_facility', 'LOG_USER'
'audit_log_syslog_ident', 'percona-audit'
'audit_log_syslog_priority', 'LOG_INFO'
1 Like
8

Ok, config looks fine to me except the audit_log_exclude_accounts which I mentioned should be for example:

audit_log_exclude_accounts=‘appuser1@host,appuser2@%’

the entries have to match what is in SELECT * from mysql.user;

1 Like
9

Great!, Thank you.Now with new session my user`s commands are not logged.I also check select carefully with other user, selects not logged just some show commands because of Workbench. Thank you Igroene

1 Like
10

You are most welcome! glad to hear it worked

1 Like