How to config and enable MySQL Enterprise Audit in MySQL

Here is the steps to show you how to config and enable the MySQL Enterprise Audit in MySQL.

A. Config and enable the MySQL Enterprise Audit in MySQL.

Step 1: Append the following lines in config file

plugin-load-add=authentication_pam.so
plugin-load-add=audit_log.so
audit_log_flush=on
audit_log_rotate_on_size=200MB
#Example of audit log name is audit_host01_3306.log
audit_log_file=/db_log/audit/audit_host01_3306.log 
#Log connection error only
audit_log_connection_policy=ERRORS

Step 2: Check plugin status

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME LIKE 'audit%';

   

Step 3: Create audit function and tables

USE mysql;
CREATE TABLE IF NOT EXISTS audit_log_filter(NAME VARCHAR(64) BINARY NOT NULL PRIMARY KEY, 
FILTER JSON NOT NULL) engine= InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci;

CREATE TABLE IF NOT EXISTS audit_log_user(USER VARCHAR(32) BINARY NOT NULL, 
HOST VARCHAR(60) BINARY NOT NULL, FILTERNAME VARCHAR(64) BINARY NOT NULL, 
PRIMARY KEY (USER, HOST), FOREIGN KEY (FILTERNAME) REFERENCES mysql.audit_log_filter(NAME)) 
engine= InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci;
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
CREATE FUNCTION audit_log_filter_set_filter RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_filter_remove_filter RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_filter_set_user RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_filter_remove_user RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_filter_flush RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_read_bookmark RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_read RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_encryption_password_set RETURNS INTEGER SONAME 'audit_log.so';
CREATE FUNCTION audit_log_encryption_password_get RETURNS STRING SONAME 'audit_log.so';
SELECT audit_log_filter_flush() AS 'Result';

Step 4: Rotate the log file when the audit log reached 200MB

SET GLOBAL audit_log_rotate_on_size = 1024*1024*1024;

B. Create the log filter and assigned it to specified user. 

Reference: 

https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/audit-log-filter-definitions.html#audit-log-general-event-fields

Step 5: Create the log filter for logging specified DDL

set @log_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" }}
          ]
       }
    ]
    }
   }
  }
 }
}';

Step 6: Apply the filter for all DB users

Verify the filter is a valid JSON object:

use mysql;
select json_valid(@log_ddl_filter); 

Remove any previous version of the filter:

select audit_log_filter_remove_filter('log_specified_ddl');

Set the new filter:

select audit_log_filter_set_filter('log_specified_ddl',@log_ddl_filter); 

Specify which user(s) the filter should apply to, e.g. apply the filter to all user.

Remark: The filter can be assigned to any user account if the user has no assigned filter.

select audit_log_filter_set_user('%','log_specified_ddl');

Enquiry the created audit filter 

select * from audit_log_filter where name = 'log_specified_ddl' \G

Enquiry the audit user account that has assigned filter.

select * from audit_log_user;