Steps to reproduce:

0. configure clean instance of Zabbix 7.0.4, install agent2, install MariaDB 10.5.22
1. create user in DB engine with use of this: https://www.zabbix.com/integrations/mysql#mysql_agent2
2. after creation of user, add new host:

• hostname: your choice
• template: MySQL by zabbix agent2
• agent: 127.0.0.1:10050 (I've been using agent2 installed on server machine)
  go to macros choose "Inherited and host macros", and set:
  {$MYSQL.DSN} value: (in my case) tcp://192.168.99.21:3306
  {$MYSQL.PASSWORD} value: password
  {$MYSQL.USER} value: zbx_monitor

3. click add and go to host --> discovery rules and run discovery rules

Result:
In result in agent2.logs we would see this entry:

2024/10/23 15:31:57.823833 [Mysql] Cannot fetch data: Error 1227 (42000): Access denied; you need (at least one of) the SUPER, SLAVE MONITOR privilege(s) for this operation.