Hardening MySQL 8.4: Permissions, SSL, Auditing & Compliance Guide

Overview

MySQL 8.4 LTS introduces secure defaults, but production deployments still need explicit hardening to mitigate mis‑configured privileges, weak passwords, clear‑text traffic, and lack of auditing. The guide covers five hardening dimensions: permission design, password policy, audit logging, TLS encryption, and network access control.

Technical characteristics

Depth‑in‑defence : layered permission control, network isolation, TLS, and audit logging.

Least privilege : role‑based accounts, avoid SUPER privilege.

Auditability : all DDL/DML actions are traceable.

Compliance support : satisfies PCI‑DSS, GB/T 22239 and similar baselines.

Applicable scenarios

New MySQL instance baseline configuration.

Security audit and remediation of existing instances.

Pre‑assessment hardening for GB/T 22239 certification.

Multi‑tenant environments requiring strict permission isolation.

Environment requirements

MySQL 8.4 LTS

OS: CentOS 8+ or Ubuntu 22.04+ (systemd required)

OpenSSL 3.0+ for TLS 1.3 support

Audit plugin: audit_log (community) or Enterprise Audit

Detailed steps

1. Preparation

Check the current security posture before making changes.

# Check MySQL version
mysql -u root -p -e "SELECT VERSION();"

# List all accounts and host configuration
mysql -u root -p -e "SELECT user, host, plugin, password_expired, account_locked FROM mysql.user;"

# Find accounts with empty passwords
mysql -u root -p -e "SELECT user, host FROM mysql.user WHERE authentication_string = '' OR authentication_string IS NULL;"

# Find anonymous accounts
mysql -u root -p -e "SELECT user, host FROM mysql.user WHERE user = '';"

# Show global privilege assignments
mysql -u root -p -e "SELECT * FROM information_schema.USER_PRIVILEGES;"

Remove insecure defaults.

# Delete anonymous accounts
mysql -u root -p -e "DELETE FROM mysql.user WHERE user = ''; FLUSH PRIVILEGES;"

# Drop the default test database
mysql -u root -p -e "DROP DATABASE IF EXISTS test;"
mysql -u root -p -e "DELETE FROM mysql.db WHERE db = 'test' OR db = 'test\\_%'; FLUSH PRIVILEGES;"

# Restrict root to local connections only
mysql -u root -p -e "DELETE FROM mysql.user WHERE user = 'root' AND host != 'localhost'; FLUSH PRIVILEGES;"

2. Core configuration

2.1 Permission system design

MySQL 8.4 permission hierarchy: global → database → table → column. Create three role categories and assign them to accounts.

Application account (minimal privileges, limited to a subnet):

# Create an application user limited to a subnet
CREATE USER 'app_user'@'10.0.1.%' IDENTIFIED BY 'StrongP@ssw0rd_2024';

# Grant only DML on the business database
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app_user'@'10.0.1.%';

# Optional: allow stored procedures
GRANT EXECUTE ON appdb.* TO 'app_user'@'10.0.1.%';
FLUSH PRIVILEGES;

Read‑only account (analytics, replica verification):

CREATE USER 'readonly_user'@'10.0.2.%' IDENTIFIED BY 'ReadOnly@2024!';
GRANT SELECT ON appdb.* TO 'readonly_user'@'10.0.2.%';
-- Optional full‑database read access (use with caution)
-- GRANT SELECT ON *.* TO 'readonly_user'@'10.0.2.%';
FLUSH PRIVILEGES;

Operations (DBA) account (high privileges, audited, no SUPER):

# Create a DBA user without SUPER
CREATE USER 'dba_ops'@'192.168.100.10' IDENTIFIED BY 'DBA_Ops@Secure2024';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,
      CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW,
      SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER,
      SYSTEM_VARIABLES_ADMIN, BINLOG_ADMIN, REPLICATION_SLAVE_ADMIN,
      REPLICATION_CLIENT, SHOW DATABASES, PROCESS, RELOAD
ON *.* TO 'dba_ops'@'192.168.100.10';
FLUSH PRIVILEGES;

2.2 Password policy configuration

Enable the bundled validate_password plugin and enforce a strong policy.

# View current settings
SHOW VARIABLES LIKE 'validate_password%';

# Enforce STRONG policy (uppercase, lowercase, digits, special chars)
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;
SET GLOBAL validate_password.check_user_name = ON;

Persist the settings in my.cnf:

[mysqld]
validate_password.policy          = STRONG
validate_password.length          = 12
validate_password.mixed_case_count = 1
validate_password.number_count    = 1
validate_password.special_char_count = 1
validate_password.check_user_name  = ON

default_password_lifetime = 90
password_history          = 6
password_reuse_interval   = 365

2.3 TLS/SSL encrypted connections

Generate CA‑signed certificates for production use.

# Generate CA key and certificate
openssl genrsa 4096 > ca-key.pem
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem \
    -subj "/C=CN/ST=Beijing/O=YourOrg/CN=MySQL-CA"

# Server certificate
openssl req -newkey rsa:4096 -days 3650 -nodes -keyout server-key.pem \
    -out server-req.pem -subj "/C=CN/ST=Beijing/O=YourOrg/CN=mysql-server"
openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem \
    -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Client certificate
openssl req -newkey rsa:4096 -days 3650 -nodes -keyout client-key.pem \
    -out client-req.pem -subj "/C=CN/ST=Beijing/O=YourOrg/CN=mysql-client"
openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem \
    -CAkey ca-key.pem -set_serial 02 -out client-cert.pem

# Deploy certificates to MySQL data directory
cp ca-cert.pem server-cert.pem server-key.pem /var/lib/mysql/
chown mysql:mysql /var/lib/mysql/ca-cert.pem /var/lib/mysql/server-cert.pem /var/lib/mysql/server-key.pem
chmod 600 /var/lib/mysql/server-key.pem

Enable TLS in my.cnf:

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

tls_version = TLSv1.2,TLSv1.3
require_secure_transport = ON

Force specific accounts to use SSL:

ALTER USER 'app_user'@'10.0.1.%' REQUIRE SSL;
ALTER USER 'dba_ops'@'192.168.100.10' REQUIRE X509;

2.4 Audit log configuration

Install and enable the built‑in audit_log plugin (MySQL 8.0+).

# Install the plugin (community edition)
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

# Verify installation
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM information_schema.PLUGINS WHERE PLUGIN_NAME = 'audit_log';

Configure audit parameters in my.cnf:

[mysqld]
audit_log_file        = /var/log/mysql/audit.log
audit_log_format      = JSON
audit_log_policy      = ALL   # adjust for production
audit_log_rotate_on_size = 1073741824   # 1 GB
audit_log_strategy    = ASYNCHRONOUS   # use SYNCHRONOUS for strict compliance
audit_log_buffer_size = 1048576

2.5 Network access control

Restrict MySQL to internal IPs and tighten server variables.

[mysqld]
bind-address          = 10.0.1.10   # listen only on internal interface
local_infile          = OFF
secure_file_priv      = ''          # disable file import/export
skip_symbolic_links   = ON
max_connections       = 500
max_user_connections  = 50
connect_timeout       = 10
wait_timeout          = 600
interactive_timeout   = 600

Example firewalld rules (allow only trusted subnets):

# Application servers (10.0.1.0/24)
firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="10.0.1.0/24" port protocol="tcp" port="3306" accept'

# DBA jump host
firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.100.10/32" port protocol="tcp" port="3306" accept'

# Block everything else
firewall-cmd --permanent --remove-service=mysql || true
firewall-cmd --reload

3. Example configuration snippets

3.1 Complete security my.cnf snippet

# File: /etc/mysql/mysql.conf.d/security.cnf
[mysqld]
# ===== Network security =====
bind-address          = 10.0.1.10
local_infile          = OFF
secure_file_priv      = ''
skip_symbolic_links   = ON
max_connections       = 500
max_user_connections  = 50
connect_timeout       = 10
wait_timeout          = 600
interactive_timeout   = 600

# ===== SSL/TLS =====
ssl_ca                = /var/lib/mysql/ca-cert.pem
ssl_cert              = /var/lib/mysql/server-cert.pem
ssl_key               = /var/lib/mysql/server-key.pem
tls_version          = TLSv1.2,TLSv1.3
require_secure_transport = ON

# ===== Password policy =====
default_password_lifetime = 90
password_history          = 6
password_reuse_interval   = 365
validate_password.policy          = STRONG
validate_password.length          = 12
validate_password.mixed_case_count = 1
validate_password.number_count    = 1
validate_password.special_char_count = 1
validate_password.check_user_name  = ON

# ===== Audit log =====
audit_log_file        = /var/log/mysql/audit.log
audit_log_format      = JSON
audit_log_policy      = ALL
audit_log_rotate_on_size = 1073741824
audit_log_strategy    = ASYNCHRONOUS
audit_log_buffer_size = 1048576

3.2 Permission initialization script

# File: /opt/mysql/scripts/init_security.sql
-- Remove insecure defaults
DELETE FROM mysql.user WHERE user = '';
DELETE FROM mysql.user WHERE user = 'root' AND host != 'localhost';
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE db = 'test' OR db = 'test\\_%';

-- Create roles
CREATE ROLE IF NOT EXISTS role_app_rw;
CREATE ROLE IF NOT EXISTS role_app_ro;
CREATE ROLE IF NOT EXISTS role_dba;

-- Grant privileges to roles
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON appdb.* TO role_app_rw;
GRANT SELECT ON appdb.* TO role_app_ro;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,
      CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW,
      SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER,
      SYSTEM_VARIABLES_ADMIN, BINLOG_ADMIN, REPLICATION_SLAVE_ADMIN,
      REPLICATION_CLIENT, SHOW DATABASES, PROCESS, RELOAD
ON *.* TO role_dba;

-- Create application user and assign role
CREATE USER IF NOT EXISTS 'app_user'@'10.0.1.%' IDENTIFIED BY 'App_User@2024#Prod' \
    PASSWORD EXPIRE INTERVAL 90 DAY FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1 REQUIRE SSL;
GRANT role_app_rw TO 'app_user'@'10.0.1.%';
SET DEFAULT ROLE role_app_rw TO 'app_user'@'10.0.1.%';

-- Create read‑only user
CREATE USER IF NOT EXISTS 'readonly'@'10.0.2.%' IDENTIFIED BY 'Read_Only@2024#' \
    PASSWORD EXPIRE INTERVAL 90 DAY FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2 REQUIRE SSL;
GRANT role_app_ro TO 'readonly'@'10.0.2.%';
SET DEFAULT ROLE role_app_ro TO 'readonly'@'10.0.2.%';

FLUSH PRIVILEGES;

4. Best practices & caveats

4.1 Permission management

Prefer roles over direct grants : modify role definitions to change permissions globally.

Account naming convention : {purpose}_{env}@{source_subnet} (e.g., [email protected].%).

Password automation : store passwords in Vault or cloud Secrets Manager and rotate automatically.

4.2 Audit log

Log storage separation : keep audit logs on a dedicated disk (e.g., /var/log/mysql) to avoid data‑disk exhaustion.

Retention : GB/T 22239 requires 6 months, PCI‑DSS 1 year. JSON logs compress to ~10 % of raw size.

Performance impact : audit_log_policy = ALL adds ~10‑15 % overhead at >5 000 TPS; consider selective logging for high‑frequency SELECTs.

4.3 SSL/TLS

Certificate lifecycle : use 2‑year validity and rotate 30 days before expiry.

TLS version control : disable TLS 1.0/1.1; ensure tls_version includes only 1.2/1.3.

4.4 Configuration pitfalls

Enabling require_secure_transport = ON rejects non‑TLS clients; verify all applications, monitoring agents, and backup tools support TLS first.

Setting secure_file_priv = '' disables all file import/export; leaving it unset permits arbitrary paths and is a security risk.

Changing bind-address requires a restart and will drop existing connections—plan a maintenance window.

Asynchronous audit writes may lose logs on crash; switch to SYNCHRONOUS for strict compliance at the cost of performance.

5. Troubleshooting & monitoring

5.1 Common issues

ERROR 1045: Access denied – usually caused by host mismatch or locked account. Check mysql.user host field and SHOW CREATE USER.

SSL connection error – verify certificate paths, permissions, and expiration with SHOW VARIABLES LIKE 'ssl%'.

Audit plugin fails to load – ensure audit_log.so exists in the directory defined by plugin_dir.

Password change rejected – password does not meet validate_password rules; review variables.

Account locked – exceeds FAILED_LOGIN_ATTEMPTS; unlock with ALTER USER ... ACCOUNT UNLOCK;.

5.2 Monitoring metrics

Aborted_connects : SHOW STATUS LIKE 'Aborted_connects'; – alert if >50/min.

Connection_errors_max_connections : should stay at 0; non‑zero indicates capacity limits.

Ssl_accepts / Ssl_finished_accepts : monitor SSL usage ratio; aim for >90 % SSL connections.

Audit log write latency : target <10 ms; alert >50 ms.

5.3 Backup of permission configurations

#!/bin/bash
BACKUP_DIR="/opt/mysql/backups/permissions"
DATE=$(date +%Y%m%d)
mkdir -p "$BACKUP_DIR"

# Export CREATE USER statements
mysql -u root -p -N -s -e "SELECT CONCAT('SHOW CREATE USER ''', user, '''@''', host, ''';') FROM mysql.user WHERE user NOT IN ('mysql.sys','mysql.infoschema');" | \
    mysql -u root -p > "$BACKUP_DIR/users_${DATE}.sql"

# Export privilege tables
mysqldump -u root -p --no-data --no-tablespaces mysql user db tables_priv columns_priv procs_priv \
    > "$BACKUP_DIR/grants_${DATE}.sql"

echo "Permission backup completed: $BACKUP_DIR"

Conclusion

The hardening checklist emphasizes five pillars: minimal privileges via roles, strong password policies with expiration and history, TLS‑only transport, comprehensive audit logging with isolated storage, and strict network isolation. Implementing these steps aligns MySQL 8.4 with PCI‑DSS, GB/T 22239, and general best‑practice security standards.