How to capture only DDL, Connections, Failed Logins and Grant/Revoke privileges in Percona MySQL 8.4.7?

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

We are able to capture Connections, Failed Login, DDL statements like (CREATE USER/TABLE/DATABASE, ALTER USER/TABLE/DATABASE, DROP USER/TABLE/DATABASE) using the following filter definition;

SELECT audit_log_filter_set_filter(‘capture_required_events’, ‘{
“filter”: {
“class”: [
{
“name”: “connection”,
“event”: [
{ “name”: “connect”},
{ “name”: “disconnect”},
{ “name”: “failed_login”}
]
},
{
“name”: “query”,
“event”: [
{
“name”: “start”,
“log”: {
“or”: [
{ “field”: { “name”: “sql_command_id”, “value”: “create_db”} },
{ “field”: { “name”: “sql_command_id”, “value”: “drop_db”} },
{ “field”: { “name”: “sql_command_id”, “value”: “alter_db”} },
{ “field”: { “name”: “sql_command_id”, “value”: “truncate”} },
{ “field”: { “name”: “sql_command_id”, “value”: “create_table”} },
{ “field”: { “name”: “sql_command_id”, “value”: “alter_table”} },
{ “field”: { “name”: “sql_command_id”, “value”: “drop_table”} },
{ “field”: { “name”: “sql_command_id”, “value”: “create_user”} },
{ “field”: { “name”: “sql_command_id”, “value”: “alter_user”} },
{ “field”: { “name”: “sql_command_id”, “value”: “drop_user”} }
]
}
}
]
}
]
}
}’);

But how do we also capture GRANT/REVOKE statements?

2

Just add this to your existing filter:

{ "field": { "name": "sql_command_id", "value": "grant" } },
{ "field": { "name": "sql_command_id", "value": "revoke" } }
3

Yes, I added these two filters… but not capturing the grant or revoke events in the audit log

4

Interesting in my lab its working as it should. No issue at all. Here is the filter I used:

SELECT audit_log_filter_set_filter('capture_required_events', '{
  "filter": {
    "class": [
      {
        "name": "connection",
        "event": [
          { "name": "connect" },
          { "name": "disconnect" },
          { "name": "failed_login" }
        ]
      },
      {
        "name": "query",
        "event": [
          {
            "name": "start",
            "log": {
              "or": [
                { "field": { "name": "sql_command_id", "value": "create_db" } },
                { "field": { "name": "sql_command_id", "value": "drop_db" } },
                { "field": { "name": "sql_command_id", "value": "alter_db" } },
                { "field": { "name": "sql_command_id", "value": "truncate" } },
                { "field": { "name": "sql_command_id", "value": "create_table" } },
                { "field": { "name": "sql_command_id", "value": "alter_table" } },
                { "field": { "name": "sql_command_id", "value": "drop_table" } },
                { "field": { "name": "sql_command_id", "value": "create_user" } },
                { "field": { "name": "sql_command_id", "value": "alter_user" } },
                { "field": { "name": "sql_command_id", "value": "drop_user" } },

                { "field": { "name": "sql_command_id", "value": "grant" } },
                { "field": { "name": "sql_command_id", "value": "revoke" } }
              ]
            }
          }
        ]
      }
    ]
  }
}');

Here is the output from my audit log:

{
    "timestamp": "2026-01-22 08:10:45",
    "id": 16,
    "class": "query",
    "event": "query_start",
    "connection_id": 10,
    "query_data": {
      "query": "grant select on spice.* to example@'%'",
      "status": 0,
      "sql_command": "grant"}
  },
  {
    "timestamp": "2026-01-22 08:12:56",
    "id": 17,
    "class": "query",
    "event": "query_start",
    "connection_id": 10,
    "query_data": {
      "query": "revoke select on spice.* from example@'%'",
      "status": 0,
      "sql_command": "revoke"}
  }

Did you run:

SELECT audit_log_filter_flush();

After you made your changes?

5

Thank you very much. Yes, this works with granting direct privileges (SELECT, INSERT, etc). But if we grant or revoke role(s) it is not captured.

Your help is highly appreciated.

Thank you

6

Just add these:

{ "field": { "name": "sql_command_id", "value": "create_role" } },
{ "field": { "name": "sql_command_id", "value": "drop_role" } },

Output:

  {
    "timestamp": "2026-01-22 12:24:19",
    "id": 26,
    "class": "query",
    "event": "query_start",
    "connection_id": 10,
    "query_data": {
      "query": "create role temp_role",
      "status": 0,
      "sql_command": "create_role"}
  },
  {
    "timestamp": "2026-01-22 12:24:44",
    "id": 27,
    "class": "query",
    "event": "query_start",
    "connection_id": 10,
    "query_data": {
      "query": "drop role temp_role",
      "status": 0,
      "sql_command": "drop_role"}
  }
7

Thank you. Yes, it captures the create/drop role in the audit log. But when I grant or revoke the role to/from an user, it is not captured. Here is the filter

SELECT audit_log_filter_set_filter(‘capture_required_events’, ‘{
“filter”: {
“class”: [
{
“name”: “connection”,
“event”: [
{ “name”: “connect” },
{ “name”: “disconnect” },
{ “name”: “failed_login” }
]
},
{
“name”: “query”,
“event”: [
{
“name”: “start”,
“log”: {
“or”: [
{ “field”: { “name”: “sql_command_id”, “value”: “create_db” } },
{ “field”: { “name”: “sql_command_id”, “value”: “drop_db” } },
{ “field”: { “name”: “sql_command_id”, “value”: “alter_db” } },
{ “field”: { “name”: “sql_command_id”, “value”: “truncate” } },
{ “field”: { “name”: “sql_command_id”, “value”: “create_table” } },
{ “field”: { “name”: “sql_command_id”, “value”: “alter_table” } },
{ “field”: { “name”: “sql_command_id”, “value”: “drop_table” } },
{ “field”: { “name”: “sql_command_id”, “value”: “create_user” } },
{ “field”: { “name”: “sql_command_id”, “value”: “alter_user” } },
{ “field”: { “name”: “sql_command_id”, “value”: “drop_user” } },
{ “field”: { “name”: “sql_command_id”, “value”: “create_role” } },
{ “field”: { “name”: “sql_command_id”, “value”: “drop_role” } },
{ “field”: { “name”: “sql_command_id”, “value”: “grant” } },
{ “field”: { “name”: “sql_command_id”, “value”: “revoke” } }
]
}
}
]
}
]
}
}’);

8

Role assignment is not logged as a query event.
It is logged as an authorization class event.

Try adding:

{
  "name": "authorization",
  "event": [
    { "name": "grant" },
    { "name": "revoke" }
  ]
}

To your existing filter.

9

Looks like it is not working… Can you please provide me the full filter on this filter?

SELECT audit_log_filter_set_filter(‘capture_required_events’, ‘{
“filter”: {
“class”: [
{
“name”: “connection”,
“event”: [
{ “name”: “connect” },
{ “name”: “disconnect” },
{ “name”: “failed_login” }
]
},
{
“name”: “query”,
“event”: [
{
“name”: “start”,
“log”: {
“or”: [
{ “field”: { “name”: “sql_command_id”, “value”: “create_db” } },
{ “field”: { “name”: “sql_command_id”, “value”: “drop_db” } },
{ “field”: { “name”: “sql_command_id”, “value”: “alter_db” } },
{ “field”: { “name”: “sql_command_id”, “value”: “truncate” } },
{ “field”: { “name”: “sql_command_id”, “value”: “create_table” } },
{ “field”: { “name”: “sql_command_id”, “value”: “alter_table” } },
{ “field”: { “name”: “sql_command_id”, “value”: “drop_table” } },
{ “field”: { “name”: “sql_command_id”, “value”: “create_user” } },
{ “field”: { “name”: “sql_command_id”, “value”: “alter_user” } },
{ “field”: { “name”: “sql_command_id”, “value”: “drop_user” } },
{ “field”: { “name”: “sql_command_id”, “value”: “create_role” } },
{ “field”: { “name”: “sql_command_id”, “value”: “drop_role” } },
{ “field”: { “name”: “sql_command_id”, “value”: “grant” } },
{ “field”: { “name”: “sql_command_id”, “value”: “revoke” } }
]
}
}
]
}
]
}
}’);

10

Try this:

SELECT audit_log_filter_set_filter('capture_required_events', '{
  "filter": {
    "class": [
      {
        "name": "connection",
        "event": [
          { "name": "connect" },
          { "name": "disconnect" },
          { "name": "failed_login" }
        ]
      },
      {
        "name": "query",
        "event": [
          {
            "name": "start",
            "log": {
              "or": [
                { "field": { "name": "sql_command_id", "value": "create_db" } },
                { "field": { "name": "sql_command_id", "value": "drop_db" } },
                { "field": { "name": "sql_command_id", "value": "alter_db" } },
                { "field": { "name": "sql_command_id", "value": "truncate" } },
                { "field": { "name": "sql_command_id", "value": "create_table" } },
                { "field": { "name": "sql_command_id", "value": "alter_table" } },
                { "field": { "name": "sql_command_id", "value": "drop_table" } },
                { "field": { "name": "sql_command_id", "value": "create_user" } },
                { "field": { "name": "sql_command_id", "value": "alter_user" } },
                { "field": { "name": "sql_command_id", "value": "drop_user" } },
                { "field": { "name": "sql_command_id", "value": "create_role" } },
                { "field": { "name": "sql_command_id", "value": "drop_role" } },
                { "field": { "name": "sql_command_id", "value": "grant" } },
                { "field": { "name": "sql_command_id", "value": "revoke" } }
              ]
            }
          }
        ]
      },
      {
        "name": "authorization",
        "event": [
          { "name": "grant" },
          { "name": "revoke" }
        ]
      }
    ]
  }
}');
11

Yes, I tried the following filter, but it is NOT capturing the grant/revoke role statements

SELECT audit_log_filter_set_filter(‘capture_core_events’, ‘{
“filter”: {
“class”: [
{
“name”: “connection”,
“event”: [
{ “name”: “connect” },
{ “name”: “disconnect” },
{ “name”: “failed_login” }
]
},
{
“name”: “query”,
“event”: [
{
“name”: “start”,
“log”: {
“or”: [
{ “field”: { “name”: “sql_command_id”, “value”: “create_db” } },
{ “field”: { “name”: “sql_command_id”, “value”: “drop_db” } },
{ “field”: { “name”: “sql_command_id”, “value”: “alter_db” } },
{ “field”: { “name”: “sql_command_id”, “value”: “truncate” } },
{ “field”: { “name”: “sql_command_id”, “value”: “create_table” } },
{ “field”: { “name”: “sql_command_id”, “value”: “alter_table” } },
{ “field”: { “name”: “sql_command_id”, “value”: “drop_table” } },
{ “field”: { “name”: “sql_command_id”, “value”: “create_user” } },
{ “field”: { “name”: “sql_command_id”, “value”: “alter_user” } },
{ “field”: { “name”: “sql_command_id”, “value”: “drop_user” } },
{ “field”: { “name”: “sql_command_id”, “value”: “create_role” } },
{ “field”: { “name”: “sql_command_id”, “value”: “drop_role” } },
{ “field”: { “name”: “sql_command_id”, “value”: “grant” } },
{ “field”: { “name”: “sql_command_id”, “value”: “revoke” } }
]
}
}
]
},
{
“name”: “authorization”,
“event”: [
{ “name”: “grant” },
{ “name”: “revoke” }
]
}
]
}
}’);

12

Looks like we might have to add the following filter too

            { "field": { "name": "sql_command_id", "value": "grant_role" } }
            { "field": { "name": "sql_command_id", "value": "revoke_role" } }

Am I correct?

13

We don’t need “authorization” block also. Here is the correct (working) one:

SELECT audit_log_filter_set_filter('capture_core_events', 
'{
  "filter": {
    "class": [
      {
        "name": "connection",
        "event": [
          {
            "name": "connect"
          },
          {
            "name": "disconnect"
          },
          {
            "name": "failed_login"
          }
        ]
      },
      {
        "name": "query",
        "event": [
          {
            "name": "start",
            "log": {
              "or": [
                {
                  "field": {
                    "name": "sql_command_id",
                    "value": "create_db"
                  }
                },
                {
                  "field": {
                    "name": "sql_command_id",
                    "value": "drop_db"
                  }
                },
                {
                  "field": {
                    "name": "sql_command_id",
                    "value": "alter_db"
                  }
                },
                {
                  "field": {
                    "name": "sql_command_id",
                    "value": "truncate"
                  }
                },
                {
                  "field": {
                    "name": "sql_command_id",
                    "value": "create_table"
                  }
                },
                {
                  "field": {
                    "name": "sql_command_id",
                    "value": "alter_table"
                  }
                },
                {
                  "field": {
                    "name": "sql_command_id",
                    "value": "drop_table"
                  }
                },
                {
                  "field": {
                    "name": "sql_command_id",
                    "value": "create_user"
                  }
                },
                {
                  "field": {
                    "name": "sql_command_id",
                    "value": "alter_user"
                  }
                },
                {
                  "field": {
                    "name": "sql_command_id",
                    "value": "drop_user"
                  }
                },
                {
                  "field": {
                    "name": "sql_command_id",
                    "value": "create_role"
                  }
                },
                {
                  "field": {
                    "name": "sql_command_id",
                    "value": "drop_role"
                  }
                },
                {
                  "field": {
                    "name": "sql_command_id",
                    "value": "grant_role"
                  }
                },
                {
                  "field": {
                    "name": "sql_command_id",
                    "value": "revoke_role"
                  }
                },
                {
                  "field": {
                    "name": "sql_command_id",
                    "value": "grant"
                  }
                },
                {
                  "field": {
                    "name": "sql_command_id",
                    "value": "revoke"
                  }
                }
              ]
            }
          }
        ]
      }
    ]
  }
}');

(Admin Edit: Formatting)

14

The authorization block will capture:

authorization events at the server level, independent of SQL text:

  • grant
  • revoke

This means you’ll capture privilege changes even if they’re not visible or fully represented in query text.

If the filter you have is working the way you want, then leave it as is.

To capture events that happen outside of SQL, add the authorization class back in for a more complete view.