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:
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;
