Hi everyone,
i have a small issue.
I’d like to log only record of root, but no configuration works, can anyone help?
SHOW variables LIKE ‘audit%’;
+-----------------------------+--------------------------+
| Variable_name | Value |
+-----------------------------+--------------------------+
| audit_log_buffer_size | 1048576 |
| audit_log_exclude_accounts | |
| audit_log_exclude_commands | set_option, commit |
| audit_log_exclude_databases | |
| audit_log_file | /var/log/mysql/audit.log |
| audit_log_flush | ON |
| audit_log_format | OLD |
| audit_log_handler | FILE |
| audit_log_include_accounts | root@localhost |
| 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 | SYNCHRONOUS |
| audit_log_syslog_facility | LOG_USER |
| audit_log_syslog_ident | percona-audit |
| audit_log_syslog_priority | LOG_INFO |
+-----------------------------+--------------------------+
select host, user from mysql.user;
+-------------+----------------------+
| host | user |
+-------------+----------------------+
| 127.0.0.1 | user1 |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | repl |
| localhost | root |
| localhost | user1 |
+-------------+----------------------+
audit.log
<AUDIT_RECORD
NAME="Query"
RECORD="46563543_2022-08-11T13:07:34"
TIMESTAMP="2022-08-11T13:15:21Z"
COMMAND_CLASS="select"
CONNECTION_ID="10"
STATUS="0"
SQLTEXT="SELECT ROW_COUNT()"
USER="user1[user1] @ [127.0.0.1]"
HOST=""
OS_USER=""
IP="127.0.0.1"
DB=""
/>
1 Like
Today i find out, the new messages was produce from a dotnet service. If i login with the user, nothing ist logging. That is wired, have anyone an solution?
1 Like
Hello Evolution,
Would you mind checking the permission and owner of the directory for the file of the audit log?
It might cause this issue.
/var/log/mysql/audit.log
Plus, it will be super helpful if you can share a version of the MySQL server you are using.
Regards,
Denis Subbota.
Managed Services, Percona.
Hi Denis,
the permission on this Dir is
drwxr-x--- 2 mysql adm 4.0K Jan 25 09:12 mysql
The Version of Mysql are:
mysql Ver 8.0.30-22 for Linux on x86_64 (Percona Server (GPL), Release '22', Revision '7e301439b65')
Hi Evolution,
Thank you for checking.
I verified from my end that your settings for the audit plugin are working correctly for 8.0.30-22, and my lab has no issues with it.
The issue might be with the permission to access the file.
Could you please share the output of the below command:
ls -alhrt /var/log/mysql/
And just for testing purposes could you please update:
set global audit_log_include_accounts ="user1@localhost"
- run some select statements from this user and verify that you have new entries in audit log file.
Also, I want you to set explicitly on your running node:
set global audit_log_exclude_accounts = NULL;
Regards,
Denis Subbota.
Managed Services, Percona.
Hi,
this is the output of ls -alhrt /var/log/mysql/
-rw-r----- 1 mysql mysql 2.3K May 9 2020 mysql-slow.log
-rw-r----- 1 mysql mysql 1.1K Jan 10 2022 error.log.2.gz
-rw-r----- 1 mysql mysql 3.4K Jan 16 2022 error.log.1
drwxr-xr-x 13 root root 4.0K Jan 24 00:00 ..
-rw-r----- 1 mysql mysql 820 Jan 25 00:00 slow-query.log.2.gz
-rw-r----- 1 mysql mysql 801 Jan 26 00:00 slow-query.log.1
-rw-r----- 1 mysql mysql 1.9K Jan 26 01:19 slow-query.log
-rw-r----- 1 mysql mysql 11M Jan 26 16:16 audit.log.1
drwxr-x--- 2 mysql adm 4.0K Jan 26 16:16 .
-rw-r----- 1 mysql mysql 7.7M Jan 26 16:22 audit.log
I set the statements and i verify new entries in audit.log.
My problem is that not filtering if the csharp Application (with pomelo) runs with a separate User. This fill the audit.log despite inlcude on root user
Hi Evolution,
I have tried many possible changes in the config, but I can’t reproduce your issue.
But I noticed one behavior that might help you to figure out and maybe fix your issue.
If you update audit_log_include_accounts global variable, it will not apply to all open sessions immediately.
So to apply new changes it’s required to close the existing session and open it one more time. In that case, you will not see any unexpected entries in audit log.
Let me know if this help.
Regards,
Denis Subbota.
Managed Services, Percona.
Hi Denis,
thank you for your response. I make on every change an restart or a SESSION KILL X.
From the beginning:
We have an .net Core application with CronJobs and some more.
We use dotnet 6.0 and Pomelo.EntityFrameworkCore.MySql to connect to Percona.
The config of Percona see on the first Post.
If i connect with a specific user - all is fine. If the dotnet application run, the filter is not working.
Here are a small tail of the audit.log
"Query","109762668_2023-01-11T10:04:35","2023-01-31T10:13:54Z","begin","4029",0,"start transaction","core[core] @ [127.0.0.1]","","","127.0.0.1",""
"Query","109762669_2023-01-11T10:04:35","2023-01-31T10:13:54Z","begin","4044",0,"start transaction","core[core] @ [127.0.0.1]","","","127.0.0.1",""
The boys from Palemo say, that is an issue on Percona.
Hi Evolution,
Unfortunately, for now, I can’t test dotnet to reproduce this situation as I don’t have experience with this solution.
Regards,
Denis Subbota.
Managed Services, Percona.
Okay, thanks anyway.
Thank you for your time.