Database Security · Per-User Auditing

Percona Server 8.0 Audit Logging

Track every action of a specific MySQL user with full visibility — JSON logs, persistent configuration, and log rotation. Perfect for security, compliance, or learning.

Percona Server 8.0 audit_log plugin JSON Format Per-User Filtering Compliance Ready

What This Setup Does

This guide configures the Percona audit_log plugin to capture every action performed by a single user (user1) — every query, every connection, every error — into structured JSON logs. Filter noise out by scoping to one account, persist the config in mysqld.cnf, and rotate logs automatically so the disk never fills.

user1 — audited (query & JSON event) other users — executed, not logged plugin active

Figure 1 — Per-user audit capture. Every account's queries run on Percona, but only user1's actions are serialized to structured JSON in audit.log — keeping the trail focused and the noise out.

Why Audit at the User Level?

Five reasons targeted database auditing earns its keep in production environments:

Least-Privilege Visibility

Capture only the account you care about — service users, contractors, dev accounts — without flooding logs with system noise.

Structured JSON Logs

Human-readable and machine-parseable. Ship straight to ELK, Splunk, or Loki — no XML wrangling required.

Compliance Ready

Provides verifiable evidence for PCI-DSS, HIPAA, ISO 27001, and SOC 2 — who did what, when, with success/fail status.

Forensic Debugging

Reproduce production incidents by replaying the exact sequence of statements a user ran — including failed attempts.

Auto-Rotation

1 GB rotation + 10 archive files — high-volume databases stay protected from runaway log growth.

Async Writes

ASYNCHRONOUS strategy keeps query latency low while audit events queue safely in the background.

Prerequisites

A fresh Percona Server 8.0 installation
sudo access to the server
Basic familiarity with MySQL commands

01 Verify Percona & Audit Plugin goal: confirm plugin is available

First confirm the Percona installation, then check that the audit_log plugin is loaded and active.

Confirm Percona is installed

bash

mysql -V

Should show: Percona Server (GPL), Release '34'...

Check the audit plugin is loaded

sql

SELECT * FROM information_schema.PLUGINS
WHERE PLUGIN_NAME = 'audit_log';

Expected: one row with PLUGIN_STATUS = ACTIVE

Why this check matters

Percona ships the audit_log plugin by default, but configuration can lazy-load it or load it disabled. Confirming ACTIVE upfront prevents a silent "logs never appear" failure mode later.

02 Configure Audit Logging Persistently goal: persistent + JSON format

Make the audit settings survive restarts and emit JSON instead of the legacy XML-ish format.

Edit the Percona config file

bash

sudo nano /etc/mysql/percona-server.conf.d/mysqld.cnf

Add this block under [mysqld]

ini

[mysqld]
# Load audit plugin at startup
plugin_load_add = audit_log.so

# Audit settings
audit_log_policy = ALL
audit_log_include_accounts = user1@%
audit_log_format = NEW
audit_log_strategy = ASYNCHRONOUS
audit_log_file = /var/log/mysql/audit.log
audit_log_rotate_on_size = 1073741824   # Rotate at 1GB
audit_log_rotations = 10                # Keep 10 old files

Why these settings matter:

audit_log_include_accounts = user1@% — only log user1 (least-privilege scope, no noise)
audit_log_format = NEW — emits clean JSON, not legacy XML-style
audit_log_strategy = ASYNCHRONOUS — keeps query latency low; events queue in background
Log file outside datadir — easier to secure, ship, and rotate
Rotate at 1 GB · keep 10 — caps disk usage at ~10 GB worst case

Prepare the log directory

bash

sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo chmod 750 /var/log/mysql

Restart MySQL to apply

bash

sudo systemctl restart mysql

⚠ Important: audit_log_format only takes effect at startup — a restart is mandatory. Skipping this and you'll keep getting the old format with no error.

03 Create the Dedicated User goal: provision user1

bash

mysql -u root -p

Create user & grant minimal privileges

sql

-- Create user
CREATE USER 'user1'@'%' IDENTIFIED BY 'password';

-- Grant full access to their own databases
GRANT CREATE, ALTER, DROP, SELECT, INSERT, UPDATE, DELETE
ON *.* TO 'user1'@'%';

-- Critical: Allow foreign keys (if used later)
GRANT REFERENCES ON *.* TO 'user1'@'%';

FLUSH PRIVILEGES;
EXIT;

Why grant on *.*?

For learning & lab simplicity. In production, restrict to specific databases like app_db.* — never blanket grants on real systems.

04 Test Auditing — Create DB, Tables & Data goal: generate audit events

bash

mysql -u user1 -p

Create a test database

sql

CREATE DATABASE inventory_db;
USE inventory_db;

Create tables

sql

CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    quantity INT
);

CREATE TABLE suppliers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company VARCHAR(100),
    email VARCHAR(100)
);

Insert sample data

sql

INSERT INTO items (name, quantity) VALUES
    ('Notebook', 150),
    ('Backpack', 80);

INSERT INTO suppliers (company, email) VALUES
    ('Office Co.', 'contact@office.com');

Verify

sql

SELECT * FROM items;
SELECT * FROM suppliers;
EXIT;

All these actions are now logged because they were done by user1

05 Verify the Audit Logs goal: confirm JSON output

Tail the audit log in real time and watch entries stream in as user1 works.

bash

sudo tail -f /var/log/mysql/audit.log

Expected JSON output

json

{
  "audit_record": {
    "name": "Query",
    "command_class": "create_db",
    "sqltext": "CREATE DATABASE inventory_db",
    "user": "user1[user1] @ localhost []",
    "status": 0
  }
}

Success signs:

Format is JSON (not legacy XML)
Only user1 actions appear — other accounts are excluded
status: 0 = success · non-zero = error (useful for spotting failed access attempts)

Stop tailing

Press Ctrl + C to exit the live tail.

06 Clean Up (Optional) goal: reset the lab

Want to start over? Drop the test database — and watch a drop_db event appear in the audit log:

sql

-- As user1:
DROP DATABASE inventory_db;

Check the audit log again — you'll see a drop_db entry

Key Takeaways

Concept	Why It Matters
`audit_log_include_accounts`	Only audit specific users → less noise, sharper signal, better security
`audit_log_format = NEW`	JSON is easy to read, parse with jq, and ship to log aggregators
Restart after config change	Some settings (like `audit_log_format`) only apply at startup — silent failure if you skip the restart
`GRANT REFERENCES`	Needed for foreign keys — its absence causes confusing errors that look unrelated
Log rotation	Prevents the disk filling up — critical for high-volume databases under audit

Troubleshooting

Problem	Solution
Logs still in old format	You forgot to restart MySQL after setting `audit_log_format = NEW`
user1 can't create DB	Grant CREATE privilege: `GRANT CREATE ON . TO 'user1'@'%';`
No logs appearing	Check: (1) user matches `user1@%`, (2) plugin is ACTIVE, (3) log dir permissions are `mysql:mysql` with 750
SELECT fails with error 1142	Grant the SELECT privilege explicitly on the target database
Audit log file isn't created	Confirm `/var/log/mysql` exists and is owned by `mysql:mysql` — MySQL won't auto-create the parent directory

You Now Have

A secure, persistent audit trail scoped to a single MySQL user
Clean JSON logs at /var/log/mysql/audit.log
Full visibility into who did what, when, and whether it succeeded
Auto-rotation so the disk stays healthy at scale

This setup is ideal for:

Meeting compliance requirements — PCI-DSS, HIPAA, ISO 27001, SOC 2
Forensic debugging of user activity in dev / test / staging
Production security monitoring of contractor or third-party accounts
Learning MySQL security primitives in a structured way

Next Steps

Once the baseline audit logging is working, the natural extensions are:

Ship logs to ELK / Splunk / Loki — use Filebeat or Logstash to forward audit.log for centralized search & alerting
Add logrotate integration — pair Percona's built-in rotation with system logrotate for advanced compression and retention policies
Restrict user1 in production — replace *.* grants with database-scoped ones once the lab work is done
Hook into SIEM alerts — fire on status: non-zero entries to catch failed access attempts in real time

Real-World Impact

Per-user audit logging unlocks a powerful pattern: the database itself becomes a witness. Every privileged session, every schema change, every misuse leaves a tamper-evident JSON trail that integrates cleanly with the broader observability stack.

Outcomes from this setup in production:

Audit evidence satisfies SOC 2 and PCI-DSS controls without bolt-on tooling
Failed login & privilege-escalation attempts surface in real time when piped to SIEM
Forensic queries — "who dropped the orders table on Oct 12?" — become a one-liner against the JSON log
Selective per-user scoping keeps the log volume tractable even on busy clusters