Posted inSecurity

Hardening MySQL and MariaDB for Production: TLS, Auditing, and Access Control

Security tutorial - IT technology blog
Security tutorial - IT technology blog

Why Default Database Installations Fail in Production

Fresh installations of MySQL or MariaDB on Ubuntu and Debian prioritize ease of setup over bulletproof security. Fresh out of apt install, database servers often bind to open interfaces, run unencrypted connections across internal VPC subnets, and leave query logs disabled to minimize I/O overhead.

These convenience-first defaults expose your database to serious operational risks:

  • Plaintext Traffic Interception: Anyone with packet capture access inside your VPC, Kubernetes overlay (e.g., Flannel or Calico), or cloud subnet can inspect unencrypted SQL queries. That exposes plaintext passwords, session tokens, and customer PII.
  • Over-Privileged Service Accounts: When an app connects using ALL PRIVILEGES or a wildcard host ('app'@'%'), an SQL injection flaw in a single endpoint immediately escalates to a full database dump or remote server compromise.
  • Missing Audit Trails: Default error logs record daemon crashes and startup events, but ignore queries. When someone drops a table at 2 AM or dumps 50,000 user rows, standard logs won’t show who did it, which client IP initiated the request, or what query ran.

The Three Pillars of Database Hardening

Securing database instances doesn’t require expensive enterprise add-ons. Instead, focus on three fundamental layers:

1. Transport Layer Security (TLS/SSL)

TLS encrypts communication between your backend applications and database instances. It prevents eavesdropping across private cloud networks and cryptographically confirms database server identity before credentials exchange.

2. Principle of Least Privilege (PoLP)

Grant each application and engineer only the exact permissions needed for their role. A reporting service needs only SELECT on specific tables. A backend API needs Data Manipulation Language (DML) rights (SELECT, INSERT, UPDATE, DELETE), but never schema modification rights (DROP, ALTER) in daily operations.

3. Continuous Query Auditing

Audit logging captures every login attempt, schema modification (DDL), and sensitive query into an immutable record. This telemetry speeds up incident response and satisfies compliance controls for SOC 2 Type II, ISO 27001, and HIPAA.

Step-by-Step Hardening Guide

Step 1: Network Isolation and Granular User Privileges

Start by restricting listening interfaces. If the application and database share a virtual machine, bind strictly to localhost (127.0.0.1). For multi-tier setups, bind exclusively to your private VPC IP (e.g., 10.0.1.25).

Update your primary configuration file (/etc/mysql/mysql.conf.d/mysqld.cnf for MySQL or /etc/mysql/mariadb.conf.d/50-server.cnf for MariaDB):

[mysqld]
# Bind only to the private internal interface
bind-address = 10.0.1.25
port = 3306

# Block local file injection vulnerabilities
local_infile = 0

# Disable symbolic links to prevent table overwrite tricks
skip_symbolic_links = 1

Next, open an administrative database prompt:

sudo mysql -u root -p

Never use wildcard hosts ('%') or short passwords for service accounts. I use the browser-based password generator at toolcraft.app/en/tools/security/password-generator to produce high-entropy 32-character strings. It generates secrets entirely on the client side without transmitting data over the network.

Run these statements to provision isolated, IP-restricted service accounts and purge default accounts:

-- Create the web application user tied directly to its private server IP
CREATE USER 'shop_app'@'10.0.1.50' IDENTIFIED BY 'v8K9#mQ2$pL7!zR4*wT1@jN6^bX3(yU9';

-- Grant DML permissions strictly on production tables
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_production.* TO 'shop_app'@'10.0.1.50';

-- Create a dedicated read-only user for analytical tools (Metabase, Looker)
CREATE USER 'bi_readonly'@'10.0.1.60' IDENTIFIED BY 'cN4$tP9*kR2!yW7#mQ5@jL8^vX1(bZ3)';
GRANT SELECT ON shop_production.* TO 'bi_readonly'@'10.0.1.60';

-- Remove anonymous accounts and default test databases
DROP USER IF EXISTS ''@'localhost', ''@'127.0.0.1', ''@'%';
DROP DATABASE IF EXISTS test;

-- Refresh privilege tables
FLUSH PRIVILEGES;

Step 2: Enforce TLS 1.3 Encryption

Self-signed certificates work for initial testing, but production environments need dedicated internal certificates signed by your private PKI or generated via OpenSSL.

Generate a dedicated Certificate Authority (CA) and server certificate pair with 2048-bit RSA keys:

sudo mkdir -p /etc/mysql/ssl
cd /etc/mysql/ssl

# 1. Create the internal CA
sudo openssl genrsa 2048 > ca-key.pem
sudo openssl req -new -x509 -nodes -days 1825 -key ca-key.pem -out ca.pem -subj "/CN=Internal-Database-CA"

# 2. Create the server key and CSR
sudo openssl req -newkey rsa:2048 -days 1825 -nodes -keyout server-key.pem -out server-req.pem -subj "/CN=db-primary.internal"
sudo openssl rsa -in server-key.pem -out server-key.pem
sudo openssl x509 -req -in server-req.pem -days 1825 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# 3. Lock down file ownership and permissions
sudo chown -R mysql:mysql /etc/mysql/ssl
sudo chmod 600 /etc/mysql/ssl/*-key.pem
sudo chmod 644 /etc/mysql/ssl/*.pem

Configure MySQL/MariaDB to load these certificates and reject outdated ciphers:

[mysqld]
ssl_ca = /etc/mysql/ssl/ca.pem
ssl_cert = /etc/mysql/ssl/server-cert.pem
ssl_key = /etc/mysql/ssl/server-key.pem

# Enforce modern TLS protocol versions only
tls_version = TLSv1.2,TLSv1.3

# Reject any unencrypted client connections globally
require_secure_transport = ON

Apply the configuration by restarting the database service:

sudo systemctl restart mysql # On MariaDB: sudo systemctl restart mariadb

Add a per-user requirement to guarantee that the application account connects only over encrypted channels:

ALTER USER 'shop_app'@'10.0.1.50' REQUIRE SSL;
FLUSH PRIVILEGES;

Step 3: Enable and Configure the Audit Plugin

The MariaDB Audit Plugin (server_audit) logs connections, disconnections, and executed queries. It comes prepackaged in MariaDB and can also run on standard MySQL installations.

Add the audit configuration to your MySQL/MariaDB server configuration file:

[mysqld]
# Load the audit plugin library
plugin_load_add = server_audit=server_audit.so

# Activate logging
server_audit_logging = ON

# Record logins (CONNECT), SQL queries (QUERY), and DDL changes (TABLE)
server_audit_events = CONNECT,QUERY,TABLE

# Target file and rotation settings (100MB max per file, preserve 10 rotations)
server_audit_output_type = file
server_audit_file_path = /var/log/mysql/server_audit.log
server_audit_file_rotate_size = 104857600
server_audit_file_rotations = 10

# Exclude internal maintenance accounts to reduce noise
server_audit_excl_users = 'debian-sys-maint'

Restart the service or load the plugin on a running server without downtime:

INSTALL SONAME 'server_audit';
SET GLOBAL server_audit_logging = ON;
SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE';
SET GLOBAL server_audit_file_path = '/var/log/mysql/server_audit.log';
SET GLOBAL server_audit_file_rotate_size = 104857600;
SET GLOBAL server_audit_file_rotations = 10;

Step 4: Verification and Incident Testing

Before launching to production, verify every security boundary.

1. Verify TLS Negotiation:

SHOW STATUS LIKE 'Ssl_cipher';
-- Expected output: TLS_AES_256_GCM_SHA384 or ECDHE-RSA-AES256-GCM-SHA384

2. Test Plaintext Connection Rejection:

# Attempt connecting without SSL from the application node
mysql -u shop_app -p -h 10.0.1.25 --ssl-mode=DISABLED
# Expected output: ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.

3. Verify Audit Log Writing:

sudo tail -n 5 /var/log/mysql/server_audit.log

You should see structured CSV lines capturing timestamps, hostnames, usernames, source IPs, connection IDs, and query payloads.

Production Readiness Checklist

Security hardening is an ongoing operational discipline. Review these controls before pointing live client applications to your cluster:

  1. Network Boundary: Port 3306 is bound to private IPs and blocked by firewall rules (such as ufw or AWS Security Groups) from the public internet.
  2. Zero Wildcards: The mysql.user table contains no accounts with '%' host definitions or blank usernames.
  3. Mandatory In-Transit Encryption: require_secure_transport = ON is active, and connection pools include sslmode=verify-full or sslmode=verify-ca with valid certificates.
  4. Log Forwarding & Storage: Audit logs sit on a dedicated disk volume or stream to log collectors (Vector, Filebeat, Grafana Loki) to prevent local disk exhaustion.

With these three layers active, your database protects data across the network, blocks privilege escalation, and logs every query for audit compliance.

Share:
Tags: