Home/Learn/MySQL/MySQL Security Best Practices

MySQL Security Best Practices

Intermediate

Administration

Grant least-privilege, use SSL/TLS, hash passwords with caching_sha2_password, rename the root account, disable remote root login, and audit access with the audit log plugin.

Overview

Securing MySQL involves multiple layers: network access control, authentication hardening, least-privilege authorisation, encryption in transit and at rest, and auditing. MySQL 8.0 introduced the default caching_sha2_password authentication plugin (replacing the weaker mysql_native_password), role-based access control, and TLS 1.2/1.3 enforcement. A hardened MySQL installation should have no remote root login, application accounts with column-level least privilege, encrypted connections, and an audit trail for compliance.

User Management & Least Privilege

Create separate accounts for each application component. Grant only the minimum privileges required. Use roles (MySQL 8.0+) to group privileges and simplify management. Never use root for application connections.

SQL — user creation, roles, least privilege

-- Create application user restricted to localhost or app subnet
CREATE USER 'order_app'@'10.0.1.%'
    IDENTIFIED WITH caching_sha2_password BY 'StrongPass!123'
    PASSWORD EXPIRE INTERVAL 90 DAY
    FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 2;

-- Grant only required privileges (no DROP, no CREATE)
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.orders TO 'order_app'@'10.0.1.%';
GRANT SELECT ON shop.products TO 'order_app'@'10.0.1.%';

-- Role-based access control (MySQL 8.0+)
CREATE ROLE 'app_read', 'app_write';
GRANT SELECT ON shop.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON shop.* TO 'app_write';

GRANT 'app_read', 'app_write' TO 'order_app'@'10.0.1.%';
SET DEFAULT ROLE ALL TO 'order_app'@'10.0.1.%';

-- Disable remote root login
DELETE FROM mysql.user WHERE User = 'root' AND Host != 'localhost';
FLUSH PRIVILEGES;

-- Check open accounts (blank password is a security risk)
SELECT User, Host, plugin, password_expired
FROM mysql.user
WHERE authentication_string = '' OR plugin = 'mysql_native_password';

SSL/TLS Encryption in Transit

Require SSL/TLS for all application connections to prevent eavesdropping. MySQL 8.0 ships with auto-generated certificates; replace with CA-signed certs in production. REQUIRE SSL on the user account enforces encryption.

SQL + Properties — TLS configuration

-- Require SSL for a user
ALTER USER 'order_app'@'10.0.1.%' REQUIRE SSL;

-- Or require a specific cipher
ALTER USER 'order_app'@'10.0.1.%'
    REQUIRE CIPHER 'TLSv1.3';

-- my.cnf — server TLS configuration
[mysqld]
ssl-ca   = /etc/mysql/certs/ca.pem
ssl-cert = /etc/mysql/certs/server-cert.pem
ssl-key  = /etc/mysql/certs/server-key.pem
tls_version = TLSv1.2,TLSv1.3
require_secure_transport = ON   # reject non-SSL connections

-- Spring Boot application.properties
spring.datasource.url=jdbc:mysql://db-host:3306/shop\
  ?useSSL=true&requireSSL=true\
  &verifyServerCertificate=true\
  &trustCertificateKeyStoreUrl=file:/path/truststore.jks\
  &trustCertificateKeyStorePassword=changeit

-- Verify TLS is being used on an active connection
SHOW STATUS LIKE 'Ssl_cipher';   -- non-empty if TLS active

Auditing with MySQL Audit Log Plugin

The MySQL Enterprise Audit Log plugin (or the community-edition McAfee/Percona equivalent) records login events, DDL, and DML. Review audit logs for compliance (PCI-DSS, SOC 2) and anomaly detection.

SQL + my.cnf — audit log configuration

-- Enable audit log (MySQL Enterprise Edition)
[mysqld]
plugin-load-add = audit_log.so
audit_log_format = JSON
audit_log_policy = ALL          # log all events (reads + writes)
audit_log_rotate_on_size = 100M # rotate at 100 MB

-- Or install at runtime
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

-- Filter to log only failed logins and DDL
SET GLOBAL audit_log_policy = 'LOGINS,QUERIES';

-- Community alternative: Percona Audit Log Plugin
-- audit_log_format=JSON
-- audit_log_handler=FILE
-- audit_log_file=/var/log/mysql/audit.log

-- Check general_log for debugging (NOT production — massive overhead)
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
-- Always turn off after use:
SET GLOBAL general_log = 'OFF';

Key Points to Remember

1Use caching_sha2_password (MySQL 8.0 default) — never use mysql_native_password for new accounts.
2Never use root for application connections; create per-service accounts with minimal privileges.
3REQUIRE SSL on user accounts enforces encrypted connections.
4Set require_secure_transport=ON in my.cnf to reject all non-TLS connections.
5Use roles (MySQL 8.0+) to simplify privilege management and auditing.
6Enable the Audit Log plugin for compliance; never leave general_log ON in production.

Interview Questions

What is the principle of least privilege and how do you apply it in MySQL?

EasyInfosys

What is caching_sha2_password and why is it preferred over mysql_native_password?

MediumAmazon

How do you enforce SSL connections for a specific MySQL user?

MediumBooking.com

What is the difference between MySQL roles and direct privilege grants?

MediumLinkedIn

How would you audit all failed login attempts in MySQL?

HardUber

Ask Aria about MySQL Security Best Practices

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Performance Schema

MySQL Configuration Tuning