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

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”}
},

2 Likes