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 ?
Best Answer
I found the reason for it, you just need to relogin to database... so audit_log will be able to capture the action.