MySQL Enterprise Edition includes powerful audit filtering capabilities, but writing audit filter JSON by hand can be tedious and error-prone. The JSON model is flexible, which is exactly what makes it useful, but it also means that a small typo, a missing event class, or an incorrectly assigned user can change what does or does not appear in the audit log.
To make that process easier, I started building a MySQL Shell plugin that walks through common audit filter choices using an interactive wizard.
In MySQL Shell Python mode, the wizard is started with:
MySQL PY > audittool.start.wizard()
The idea is simple: instead of starting with a blank JSON document, start with guided questions. What do you want to audit? Connections? Failed operations? DDL? User and role changes? Sensitive table access? The wizard then builds the audit filter JSON and helps assign it to the right MySQL accounts.
MySQL Shell supports plugins, including Python plugins, which makes it a convenient place to build small DBA-focused workflows like this one.
Why a wizard?
MySQL Enterprise Audit filters are JSON documents. At the top level, a filter definition uses the form:
{ “filter”: actions }
Those actions can log everything, log specific event classes, log specific subclasses, test event fields, replace field values, or combine multiple conditions. MySQL supports event classes such as connection, general, and table_access, and a filter can include multiple classes by using a JSON array.
That flexibility is useful, but it also creates a lot of room for mistakes when writing JSON manually. For example, a DBA may want a filter that captures:
- Connection events
- Failed operations
- DDL changes
- User, role, and privilege changes
- Sensitive table reads
Each of those maps to a different part of the audit filter model. A wizard can guide the user through those choices and generate the JSON consistently.
From one filter type to multi-select filters
The first version of the plugin created one type of filter at a time. That worked for simple cases, but it quickly raised a question: what if someone wants to audit connections, DDL, and privilege changes in one filter?
Initially, I considered adding a “merge filters” option. But that felt like solving the wrong problem. The better approach was to let the user select multiple audit categories up front, then generate one combined filter.
The creation menu now works more like this:
What types of actions do you wish to collect in the audit log?
1 – Log Everything
2 – Connections
3 – Non-SSL TCP connection attempts
4 – Failed operations
5 – Sensitive table access / table DML
6 – DDL changes
7 – User, role, and privilege changes
8 – Audit administration changes
9 – Administrative server commands
10 – Stored code / scheduled event changes
11 – Bulk import/export and data movement
12 – Replication / topology changes
13 – Custom command group from performance_schema.setup_instruments
14 – Application audit messages
15 – Return
For example, the user can enter: 2, 4, 6, 7
and the wizard builds a single filter for connections, failed operations, DDL, and user/role/privilege changes.
Log Everything stands on its own, so the wizard treats option 1 as exclusive. If someone selects 1,6, the wizard asks them to either log everything or select specific categories.
Supporting both the audit plugin and audit component
Another important change came from testing with MySQL 9.7. The original code checked only for the older audit plugin. In current MySQL Enterprise Audit usage, the audit component is the expected path; the MySQL 9.7 documentation states that the audit log component is intended to replace the deprecated audit log plugin.
So the wizard now checks for either implementation.
For the plugin path, it checks information_schema.PLUGINS for an active audit_log plugin. For the component path, it checks mysql.component for:
SELECT * FROM mysql.component
WHERE component_urn = ‘file://component_audit_log’;
The MySQL documentation shows mysql.component as the way to verify the audit component installation and includes file://component_audit_log as the component URN.
Once either the plugin or component is found, the wizard continues. It also checks that the expected audit filter metadata tables are present, such as mysql.audit_log_filter and mysql.audit_log_user.
Note: Log filters can be located in a customer defined database –
show variables like ‘audit%’;
Use database name that matches yours. The examples here assume ‘mysql’ as my test has the system variable
audit_log.database = mysql
Recommending useful audit settings
After confirming that audit support is installed, the wizard checks recommended settings.
For plugin-style deployments, it recommends:
audit_log_format = JSON
audit_log_format_unix_timestamp = ON
For the audit component, JSON handling is different, so the wizard does not blindly recommend the plugin variable names. Instead, it checks the component-style setting for Unix timestamp formatting where appropriate.
This was a good reminder that supporting both plugin and component mode is not just an installation check. Some variable names and behaviors differ, so the wizard needs to be aware of which implementation is active.
Safer JSON generation
The first version built JSON strings by concatenating text. That is a common early approach, but it is fragile. It is too easy to create invalid JSON or fail to escape a value correctly.
The wizard now builds filters as Python dictionaries and serializes them with json.dumps().
Instead of constructing strings like:
fjson = ‘{ “filter”: { “log”: true } }’
the code can build:
filter_doc = {
“filter”: {
“log”: True
}
}
and then serialize it:
fjson = json.dumps(filter_doc, separators=(“,”, “:”))
That makes the filter builders easier to review and easier to extend. It also makes multi-select generation much cleaner because each selected category contributes a dictionary structure rather than a partial string.
Common filters the wizard can create
The wizard is intended to cover practical audit cases that DBAs and security teams commonly need.
Some examples include:
- Log all activity
- Log connection activity
- Log failed operations
- Log non-SSL TCP connection attempts
- Log sensitive table access
- Log DDL changes
- Log user, role, and privilege changes
- Log audit administration changes
- Log administrative server commands
- Log stored code and scheduled event changes
- Log bulk import/export and data movement
- Log replication or topology changes
- Log application audit messages
The custom command option is also useful. The wizard can query:
SELECT NAME
FROM performance_schema.setup_instruments
WHERE NAME LIKE ‘statement/sql/%’
ORDER BY NAME;
and let the user build a command group based on the SQL statement instruments available in that MySQL version. That avoids hard-coding every possible command name and makes the wizard more adaptable across versions.
Handling sensitive SQL text
Audit logs can contain sensitive information. For example, account-management statements, application SQL, or table access against sensitive data may include literals that should not be stored in full.
The filter model supports replacement of event field values, including using query digests in place of full query text. The wizard can offer a privacy-preserving option for filters where full SQL text may not be appropriate.
That gives users a choice:
1 – Store full SQL text
2 – Replace SQL text with query digest
This does not remove the need for a proper audit policy, but it helps make safer defaults easier to choose.
Assigning filters to users
Creating a filter is only half the job. The filter must also be assigned to the right audit user target.
The audit component internally registers functions such as:
audit_log_filter_set_filter()
audit_log_filter_remove_filter()
audit_log_filter_set_user()
audit_log_filter_remove_user()
The wizard uses those functions rather than directly editing audit tables.
The assignment flow also became more careful over time.
Instead of asking the user to manually type a user and host, the wizard now lists accounts using the full user@host form and shows whether the account already has a filter assigned.
For example:
| # | User@Host | State | Current filter | Classification |
|---|---|---|---|---|
| 1 | app@% | available | – | Schema/object privileged |
| 2 | root@localhost | available | – | Admin / high privilege |
| 3 | reporting@10.% | assigned | report_filter | Schema/object privileged |
| 4 | mysql.sys@localhost | available | – | System/internal account |
That matters because an audit user target can only have one filter assignment. If an account already has a filter, the wizard blocks selecting it for another filter and tells the user to remove the existing assignment first.
The % default fallback
One nuance that came up during the build was the special % audit assignment target.
% is not a normal user@host account in this context. It is the default fallback audit filter target for accounts that do not have a more specific audit filter assignment.
For example:
SELECT audit_log_filter_set_user(‘%’, ‘log_all’);
The wizard now lists % as:
% (default fallback)
and treats it like any other single-assignment target. If % already has a filter, it is shown as assigned and cannot be selected for a second filter.
This makes the fallback behavior visible instead of leaving it as something the user has to remember manually.
Adding privilege context
Another improvement was classifying users before assigning filters.
The wizard looks at available grant metadata and gives each account a simple classification, such as:
System/internal account
Admin / high privilege
Security administration
Global privileged
Schema/object privileged
Role-backed account
Login only / low direct grants
This is not intended to be a complete security assessment. It is a quick decision aid.
Assigning a filter to a high-privilege DBA account is different from assigning a filter to a low-privilege application account. Showing that context in the selection list makes the assignment step safer and more informative.
Better display options
The original “show filters” option simply dumped the contents of mysql.audit_log_filter.
That is still useful, but not always readable. The wizard now provides a display submenu:
Show audit filters
1 – Current as-is
2 – Pretty print JSON
3 – High level description of each filter with list of users using it
4 – Return
The current-as-is option is useful for troubleshooting. Pretty-printed JSON is useful for review. The high-level description is useful for quickly understanding what is configured.
For example:
Filter: admin_changes
Users: root@localhost, dba@%
Description: logs user, role, privilege, and audit administration changes
Filter: sensitive_payroll
Users: payroll_app@%
Description: logs read and update access to payroll.employee
Filter: log_all
Users: % (default fallback)
Description: logs everything
This makes the plugin useful not only when creating filters, but also when reviewing an existing audit configuration.
Small usability fixes matter
Some of the most useful improvements were not deep technical changes.
For example, the first menu flow printed the selected number back to the screen. In MySQL Shell, that could look odd, with output such as:
Se5
5
Removing debug echoes and using a cleaner prompt format made the wizard feel much more polished:
Selection
> 5
That seems minor, but tools like this are interactive. Small prompt issues can make the difference between a tool feeling rough and a tool feeling usable.
Lessons from the process
The biggest lesson was that the wizard became better through iteration.
The initial version proved the basic idea: a MySQL Shell plugin could guide filter creation. But each test raised a practical question:
Does it support the audit component, or only the old plugin?
Can a filter include more than one audit category?
Can we avoid hand-written JSON string concatenation?
Can we prevent assigning a second filter to the same audit user?
Can we include the % fallback filter target?
Can we show privilege context before assignment?
Can we display filters in a way humans can quickly understand?
Each of those questions made the plugin more useful.
The result is not just a JSON generator. It is becoming a guided workflow for creating, assigning, and reviewing MySQL Enterprise Audit filters.
Why this helps
Audit filtering is one of those areas where small mistakes matter. A missing event type, an incorrectly assigned user, or a forgotten default fallback filter can change what appears in the audit log.
A wizard does not replace understanding the audit model. The generated JSON should still be reviewed, tested, and matched against the organization’s audit requirements.
But a wizard can make the common paths easier to get right:
Choose the audit categories
Generate valid JSON
Use audit functions to create filters
Assign filters to the right accounts
Respect the one-filter-per-target rule
Handle the % fallback target
Review filters in readable form
That is the goal of this plugin: not to hide MySQL Enterprise Audit, but to make it easier to use consistently.
Availability
The plugin is published on GitHub as an independent community project:
github.com/mfrankmysql/mysqlshellauditplugin
It is a MySQL Shell plugin intended to help experiment with and manage MySQL Enterprise Edition audit filters. It is not part of MySQL, MySQL Shell, Oracle, or any official MySQL source tree.
The final version was developed iteratively with help from ChatGPT. That process worked well for this kind of tool because each test surfaced a practical edge case: supporting both the audit plugin and audit component, handling the %fallback filter target, preventing duplicate user assignments, pretty-printing stored JSON, and making the interactive prompts cleaner.
As always, test generated filters in a non-production environment first, review the JSON before applying it broadly, and verify that the resulting audit log behavior matches your compliance and operational requirements.
And happy to have ideas and contributions for expanding this mysql audit tool wizard.