How to Audit Access in MySQL

How do I audit user access in MySQL?

AUDIT ACCESS configures MySQL’s enterprise-grade audit plugin to log connections, queries, and privilege use for later security review.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why audit access in MySQL?

Auditing exposes who connected, what queries ran, and which rows changed. Security teams use these logs to detect abuse, satisfy compliance, and debug data issues.

How do I enable the audit plugin quickly?

Install the built-in audit_log plugin, reload the server, then pick a logging policy.No schema changes are required.

What is the exact syntax?

See the syntax block below for INSTALL PLUGIN, policy variables, and filter creation.

How do I log every login and query?

Set audit_log_policy = 'ALL'. MySQL writes each connection, disconnection, and statement to $DATADIR/audit.log.

How can I limit logging to sensitive tables?

Create a JSON filter that includes only the Customers, Orders, and Products tables.Attach the filter to specific users to reduce noise.

How do I review the audit file?

Use mysqlbinlog --read-from-remote-server --raw --result-file=./ audit.log or stream it into any SIEM that understands JSON.

When should I rotate the audit log?

Rotate daily or whenever the file reaches 100 MB. Use SET GLOBAL audit_log_rotate_now = ON to force an immediate roll-over.

Best practices for production?

Store logs on a separate volume, ship them off-box hourly, and hash each rotated file.Never place audit logs on tmpfs.

What if performance degrades?

Switch to audit_log_policy = 'LOGINS,TABLE_ACCESS' or add a filter that excludes high-volume read-only traffic.

How can I disable auditing safely?

First set the policy to 'NONE', verify the log has stopped, then UNINSTALL PLUGIN audit_log during a maintenance window.

Example: track customer email changes

The example query shows how to filter UPDATEs to the Customers.email column and link each change to the modifying user.

Next steps?

Automate log shipping, build Grafana dashboards, and alert on suspicious patterns such as mass DELETE operations.

Why How to Audit Access in MySQL is important

How to Audit Access in MySQL Example Usage


-- Goal: record every UPDATE of customer emails by support agents
UPDATE Customers
   SET email = 'new_email@example.com'
 WHERE id = 42;
-- The audit log entry will look like:
-- {
--   "class": "table_access",
--   "command": "update",
--   "object": {"db":"ecom","name":"Customers"},
--   "user": "support_agent@%",
--   "query": "UPDATE Customers SET email = 'new_email@example.com' WHERE id = 42;"
-- }

How to Audit Access in MySQL Syntax


-- 1. install the audit plugin (once per server)
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

-- 2. choose a global policy
SET GLOBAL audit_log_policy = 'ALL';          -- log everything
-- SET GLOBAL audit_log_policy = 'LOGINS';    -- log only connects
-- SET GLOBAL audit_log_policy = 'NONE';      -- disable logging

-- 3. optional: create a JSON filter (MySQL 8.0+)
CREATE AUDIT FILTER customer_update_filter
SET FILTER = '{
  "rule": {
    "event": { "class": "table_access", "command": ["update"] },
    "object": { "db": "ecom", "name": "Customers" }
  }
}';

-- 4. assign the filter to a user
CREATE AUDIT USER 'support_agent'@'%' WITH customer_update_filter;

-- 5. force log rotation
SET GLOBAL audit_log_rotate_now = ON;

Common Mistakes

Mistake: Leaving audit_log_policy at the default 'NONE'. Why: Nothing is logged so incidents go unnoticed. Fix: Set policy to 'ALL' or a specific subset.
Mistake: Logging every query on a busy OLTP server. Why: Disk I/O and CPU overhead can spike. Fix: Apply narrow JSON filters or log only logins.