How to skip select or all read operation in audit_log_filter in percona 8.4

Uncategorized
1

Dear Friends,

Hope you are all doing well, like as of always, i need some help from you experts.

I am trying to create a filter in percona MySQL 8.4 to log only ddl and dml activities but somehow its logging all the events, kindly find the settings which are using

filter creation

SELECT audit_log_filter_set_filter(‘log’, ‘{
“filter”: {
“class”: [
{ “name”: [“connection”,“general”,“query”,“table_access”],
“event”:[“insert”,“update”, “delete”,“drop_db”,“create_db”,“alter_table”,“alter_user”,“drop_user”,“create_user”] }
]
}
}’
);

user assigning to filter

SELECT audit_log_filter_set_user(‘%’,‘log’);

mysql> select * from mysql.audit_log_filter;
±----------±-----±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| filter_id | name | filter |
±----------±-----±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | log | {“filter”: {“class”: [{“name”: [“connection”, “general”, “query”, “table_access”], “events”: [“insert”, “update”, “delete”, “drop_db”, “create_db”, “alter_table”, “alter_user”, “drop_user”, “create_user”]}]}} |
±----------±-----±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> select * from mysql.audit_log_user;
±---------±---------±-----------+
| username | userhost | filtername |
±---------±---------±-----------+
| % | % | log |
±---------±---------±-----------+
1 row in set (0.00 sec)

mysql> select @@version;
±----------+
| @@version |
±----------+
| 8.4.4-4 |
±----------+
1 row in set (0.00 sec)

log output

{
“timestamp”: “2025-05-14 10:16:15”,
“id”: 28,
“class”: “query”,
“event”: “query_status_end”,
“connection_id”: 11,
“query_data”: {
“query”: “select user,host from mysql.user”,
“status”: 0,
“sql_command”: “select”}
},
{
“timestamp”: “2025-05-14 10:16:15”,
“id”: 29,
“class”: “general”,
“event”: “result”,
“connection_id”: 11,
“account”: { “user”: “root[root] @ localhost ”, “host”: “localhost” },
“login”: { “user”: “root[root] @ localhost ”, “ip”: “”, “proxy”: “” },
“general_data”: { “status”: 0 }
},

Its still logging the all logs

Where i am going wrong

2

Hello @redssr,
Have you searched this forums for similar issues? I seem to recall several posts earlier this year on this topic. If you can’t find anything, reply here and we can look more closely at your issue.

3

Dear @matthewb ,
Thanks for your response. Basically before posting the thread i already gone through Defining rules for audit log filter component (v8.0 / 8.4) - #2 by dba_S4dscjz
Write audit_log_filter definitons and percona-server/mysql-test/suite/component_audit_log_filter/t/filter_definition_function_string_find.test at 8.4 · percona/percona-server · GitHub threads, but no luck, hence tried my way

If possible, can you please make it simpler for me, i want to achive below things,

  1. should capture username who is firing the command
  2. should print the command ( only ddl dcl dml queries) no select or show queries should be logged
4

Hello @redssr,

The plugin component records log messages by “class” and the class that captures the queries doesn’t display the username who is running the queries but displays a connection identifier. You therefore need at least 2 classes in your filter definition to do what your’re looking for.

May be you can test this filter :

{
  "filter": {
    "class": [
      { "name": "connection", "log": true },
      {
        "name": "query",
        "event": {
          "name": ["start", "status_end"],
          "log": {
            "not": {
              "or": [
                { "function": {
                  "name": "string_find",
                  "args": [{"string": {"field": "sql_command_id"}}, {"string": {"string": "show_"}}]
                }},
                { "field": { "name": "sql_command_id", "value": "select"} }
              ]
            }
          }
        }
      }
    ]
  }
}
5

Dear @dba_S4dscjz

Thank you so much i really appreciate your help, it worked,

To log username i did a trick, instead of connection i used general, it’s drawback it’s logging log for every event but fine as instead of only connection id username is imp for me,

here is the output of log
{
“timestamp”: “2025-05-17 10:17:11”,
“id”: 40,
“class”: “general”,
“event”: “log”,
“connection_id”: 11,
“account”: { “user”: “root[root] @ localhost ”, “host”: “localhost” },
“login”: { “user”: “root[root] @ localhost ”, “ip”: “”, “proxy”: “” },
“general_data”: { “status”: 0 }
},
{
“timestamp”: “2025-05-17 10:17:11”,
“id”: 41,
“class”: “query”,
“event”: “query_start”,
“connection_id”: 11,
“query_data”: {
“query”: “create database ssr”,
“status”: 0,
“sql_command”: “create_db”}
},
{
“timestamp”: “2025-05-17 10:17:11”,
“id”: 42,
“class”: “query”,
“event”: “query_status_end”,
“connection_id”: 11,
“query_data”: {
“query”: “create database ssr”,
“status”: 0,
“sql_command”: “create_db”}
},

6

Finally here is my updated filter and it’s really worth

[code]

SELECT audit_log_filter_set_filter(‘log’, ‘{
“filter”: {
“class”: [
{
“name”: “general”,
“event”: {
“name”: “status”,
“log”: {
“not”: {
“or”: [
{ “field”: { “name”: “general_sql_command.str”, “value”: “error” } },
{
“and”: [
{ “field”: { “name”: “general_error_code”, “value”: 0 } },
{
“or”: [
{ “field”: { “name”: “general_command.str”, “value”: “Quit” } },
{ “field”: { “name”: “general_command.str”, “value”: “Init DB” } },
{ “field”: { “name”: “general_command.str”, “value”: “Ping” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “set_option” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “select” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_variables” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_databases” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_tables” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_status” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_fields” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_create_table” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_warnings” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_engine_status” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_grants” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_binlogs” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_replica_status” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_binary_log_status” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_processlist” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “replica_start” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “replica_stop” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_charsets” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “change_db” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_storage_engines” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_collations” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_procedure_status” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_function_status” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_keys” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_create_proc” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_triggers” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_events” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “unlock_tables” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “savepoint” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_table_status” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “rollback_to_savepoint” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “release_savepoint” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_create_event” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_create_func” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “begin” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “commit” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “show_plugins” } },
{ “field”: { “name”: “general_sql_command.str”, “value”: “kill” } }
]
}
]
}
]
}
}
}
},
{
“name”: “connection”,
“event”: {
“name”: “connect”,
“log”: {
“not”: { “field”: { “name”: “status”, “value”: 0 } }
}
}
}
]
}
}’);

[/code]

Sample Audit log file entry

[code]

<AUDIT_RECORD>
2026-08-25T20:29:15
<RECORD_ID>2_2026-08-25T20:29:15</RECORD_ID>
Query
<CONNECTION_ID>14</CONNECTION_ID>
0
<STATUS_CODE>0</STATUS_CODE>
root[root] @ localhost
<OS_LOGIN/>
localhost

<COMMAND_CLASS>drop_db</COMMAND_CLASS>
drop database ssr
</AUDIT_RECORD>
<AUDIT_RECORD>
2026-08-25T20:33:34
<RECORD_ID>3_2026-08-25T20:33:34</RECORD_ID>
Query
<CONNECTION_ID>15</CONNECTION_ID>
0
<STATUS_CODE>0</STATUS_CODE>
root[root] @ localhost
<OS_LOGIN/>
localhost

<COMMAND_CLASS>shutdown</COMMAND_CLASS>
shutdown
</AUDIT_RECORD>
<AUDIT_RECORD>
2026-08-25T20:33:35
<RECORD_ID>4_2026-08-25T20:33:35</RECORD_ID>
NoAudit
<SERVER_ID>31</SERVER_ID>
</AUDIT_RECORD>

[/code]