MySQL数据库审计--只审计DDL

1.设定json文件

set @ddl_filter='
{
"filter": {
  "class": {
    "name": "general",
    "event": {
      "name": "status",
      "log": {
        "and": [
        {
           "or": [
           {"field": { "name": "general_command.str", "value": "Query" }},
           {"field": { "name": "general_command.str", "value": "Execute" }}
           ]
        },
       {
          "or": [
          {"field": { "name": "general_sql_command.str", "value": "alter_db" }},
          {"field": { "name": "general_sql_command.str", "value": "alter_db_upgrade" }},
          {"field": { "name": "general_sql_command.str", "value": "alter_event" }},
          {"field": { "name": "general_sql_command.str", "value": "alter_function" }},
          {"field": { "name": "general_sql_command.str", "value": "alter_instance" }},
          {"field": { "name": "general_sql_command.str", "value": "alter_procedure" }},
          {"field": { "name": "general_sql_command.str", "value": "alter_server" }},
          {"field": { "name": "general_sql_command.str", "value": "alter_table" }},
          {"field": { "name": "general_sql_command.str", "value": "alter_tablespace" }},
          {"field": { "name": "general_sql_command.str", "value": "create_db" }},
          {"field": { "name": "general_sql_command.str", "value": "create_event" }},
          {"field": { "name": "general_sql_command.str", "value": "create_function" }},
          {"field": { "name": "general_sql_command.str", "value": "create_index" }},
          {"field": { "name": "general_sql_command.str", "value": "create_procedure" }},
          {"field": { "name": "general_sql_command.str", "value": "create_server" }},
          {"field": { "name": "general_sql_command.str", "value": "create_table" }},
          {"field": { "name": "general_sql_command.str", "value": "create_trigger" }},
          {"field": { "name": "general_sql_command.str", "value": "create_udf" }},
          {"field": { "name": "general_sql_command.str", "value": "create_view" }},
          {"field": { "name": "general_sql_command.str", "value": "drop_db" }},
          {"field": { "name": "general_sql_command.str", "value": "drop_event" }},
          {"field": { "name": "general_sql_command.str", "value": "drop_function" }},
          {"field": { "name": "general_sql_command.str", "value": "drop_index" }},
          {"field": { "name": "general_sql_command.str", "value": "drop_procedure" }},
          {"field": { "name": "general_sql_command.str", "value": "drop_server" }},
          {"field": { "name": "general_sql_command.str", "value": "drop_table" }},
          {"field": { "name": "general_sql_command.str", "value": "drop_trigger" }},
          {"field": { "name": "general_sql_command.str", "value": "drop_view" }},
          {"field": { "name": "general_sql_command.str", "value": "rename_table" }}
          ]
       }
    ]
    }
   }
  }
 }
}';

2. 确认json文件有效性

mysql> select json_valid(@ddl_filter);   #返回值1表示有效

3.移除之前本本的过滤策略

mysql> select audit_log_filter_remove_filter('log_ddl');

4.设定新的过滤策略

mysql> select audit_log_filter_set_filter('log_ddl',@ddl_filter);

5.绑定用户

mysql> select audit_log_filter_set_user('root@localhost','log_ddl');