Sample Header Ad - 728x90

MySQL: how to use audit_log plugin to audit user modification

0 votes
1 answer
311 views
I am trying to audit any user modification action (create user/alter user/drop user/rename user) on a MySQL Enterprise server 5.7.24. I have installed the audit_log plugin following the [ document](https://dev.mysql.com/doc/refman/5.7/en/audit-log-installation.html) , and tried to configure the filter with a few approaches. First, I tried to use [general](https://dev.mysql.com/doc/mysql-security-excerpt/5.7/en/audit-log-filter-definitions.html#audit-log-general-event-fields)
SELECT audit_log_filter_set_filter('user_modification', 
'{
  "filter": {
    "class": {
    "name": "general",
      "event": {
        "name": "status",
        "log": {
          "field": { "name": "general_sql_command.str", "value": "create_user" }
        }
      }
    }
  }
}') as RESULT;
and assigned the filter to everyone but no luck, it doesn't generate any audit log when I tried to create user
SELECT audit_log_filter_set_user('%', 'user_modification');
I also tried to swap a few things in the same filter, no luck - swap 'create user' with full instrument name 'statement/sql/create_user' - swap 'general_sql_command.str' with 'general_command.str' Next, I tried to use '[table_access](https://dev.mysql.com/doc/mysql-security-excerpt/5.7/en/audit-log-filter-definitions.html#audit-log-table-access-event-fields) ' instead of '[general](https://dev.mysql.com/doc/mysql-security-excerpt/5.7/en/audit-log-filter-definitions.html#audit-log-general-event-fields) ', hopeful it will capture any movement on mysql.user table, but still not luck
SELECT audit_log_filter_set_filter('user_modification', 
'{
  "filter": {
    "class": [
      {
        "name": "table_access",
        "event": {
          "name": [ "read", "insert", "delete", "update" ],
          "log": {
            "field": { "name": "table_database.str", "value": "mysql"},
            "field": { "name": "table_name.str", "value": "user"}
          }
        }
      }
    ]
  }
}') as RESULT;
Just in case I missed out on any step during installation, I created another filter for test and the result is positive.
SELECT audit_log_filter_set_filter('connection', 
'{
  "filter": {
    "class": { "name": "connection" }
  }
}');
SELECT audit_log_filter_set_user('%', 'connection');
Now I am totally puzzled, can anyone help me? thanks 🙏
Asked by Soh Wei Jen (11 rep)
Aug 4, 2020, 02:15 PM
Last activity: Apr 29, 2025, 02:01 AM