0

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, and tried to configure the filter with a few approaches.

First, I tried to use general

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' instead of 'general', 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 🙏

1 Answer 1

0

I found the reason for it, you just need to relogin to database... so audit_log will be able to capture the action.

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;
SELECT audit_log_filter_set_user('%', 'user_modification');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.