How to Audit Access in MariaDB

How do I audit user access and queries in MariaDB?

Audit Access in MariaDB enables detailed logging of connections, queries, and table activity for compliance and troubleshooting.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why audit access in MariaDB?

Regulatory compliance, performance troubleshooting, and security forensics all require a precise record of who touched what data and when.

How do I enable the audit plugin?

Install the built-in server_audit plugin once per server. The plugin starts writing JSON-style rows to the MariaDB error log or to a dedicated file.

What events can I capture?

CONNECT, QUERY, QUERY_DDL, QUERY_DML, TABLE, QUERY_DCL.Combine them to focus on logins, DDL on Products, or DML against Orders.

How can I filter by user or database?

Use server_audit_incl_users / server_audit_excl_users and server_audit_incl_databases / server_audit_excl_databases to restrict output to, for example, app_user logging into ecommerce.

How do I rotate and secure the audit log?

Point server_audit_file_path at a secured directory, enable OS-level log rotation, and restrict file permissions to the DBA group.

Best practices for ecommerce workloads?

Log QUERY_DDL and TABLE events so schema changes to OrderItems are captured.Exclude high-frequency report accounts to keep log size manageable.

Should I log DML?

Only if required by compliance; high-write tables like Orders can inflate logs rapidly.

Why How to Audit Access in MariaDB is important

How to Audit Access in MariaDB Example Usage


-- Capture all DDL against the Products table
INSTALL SONAME 'server_audit';
SET GLOBAL server_audit_logging = ON;
SET GLOBAL server_audit_events = 'QUERY_DDL';
SET GLOBAL server_audit_incl_tables = 'Products';
-- Now any ALTER TABLE Products ADD COLUMN discount DECIMAL(5,2) will be written to the audit log.

How to Audit Access in MariaDB Syntax


-- 1. Install plugin (run once)
INSTALL SONAME 'server_audit';

-- 2. Turn logging on
SET GLOBAL server_audit_logging = ON;

-- 3. Choose events
SET GLOBAL server_audit_events = 'CONNECT,QUERY_DDL,TABLE';

-- 4. Include only the application user working on the ecommerce DB
SET GLOBAL server_audit_incl_users = 'app_user';
SET GLOBAL server_audit_incl_databases = 'ecommerce';

-- 5. Optional: exclude noisy monitoring user
SET GLOBAL server_audit_excl_users = 'metrics_bot';

-- 6. View current settings
SHOW GLOBAL VARIABLES LIKE 'server_audit%';

-- 7. Disable when finished
SET GLOBAL server_audit_logging = OFF;
UNINSTALL SONAME 'server_audit';

Common Mistakes

Leaving <code>server_audit_logging</code> ON in production without filters. This floods storage with unnecessary rows. Always narrow <code>server_audit_events</code> and include users/databases.
Unsecured audit log directory. If OS permissions are world-readable, attackers can wipe evidence. Restrict <code>server_audit_file_path</code> to root-owned, 600-mode files.