Security

MySQL provides a layered security model covering authentication, authorisation, encryption (in transit and at rest), auditing, and SQL injection prevention. The default configuration in MySQL 8.0 has been significantly hardened compared to earlier versions, most notably by switching the default authentication plugin to caching_sha2_password.

See also: index, architecture, operations, architecture

---

Authentication

MySQL authenticates clients based on the user account they connect as, the host they connect from, and the authentication plugin assigned to that account. User accounts are defined as 'user_name'@'host_name' tuples stored in the mysql system schema.

Authentication Plugins

MySQL 8.0 supports multiple authentication plugins, each implementing a different password verification mechanism:

Plugin	Algorithm	Default?	Notes
caching_sha2_password	SHA-256 with server-side cache	Yes (8.0+)	Preferred. Fast on repeated auth due to in-memory cache. Requires TLS or RSA key exchange for first connection.
mysql_native_password	SHA-1	No (8.0+)	Legacy. Pre-8.0 default. Still supported for backward compatibility.
sha256_password	SHA-256	No	Older SHA-256 plugin, deprecated as of 8.0.16. Slower than caching variant.
auth_socket	OS socket peer UID	No	Unix-only. Authenticates based on the OS user name of the client process. Useful for local admin.
ldap_simple_auth / ldap_sasl_auth	LDAP bind	No	Enterprise. Delegates authentication to an LDAP or Active Directory server.
authentication_pam	PAM	No	Enterprise. Uses the OS PAM stack.

caching_sha2_password

This is the default plugin in MySQL 8.0 and provides improved security over mysql_native_password:

• Passwords are hashed using SHA-256 with a random salt.
• On successful authentication, the server caches the hash in memory, making subsequent authentications faster (no recomputation needed).
• The first authentication for a cached entry requires either a TLS connection or RSA-based password encryption over an unencrypted connection.

-- Create a user with the default plugin (caching_sha2_password)
CREATE USER 'app_user'@'10.0.%' IDENTIFIED BY 'secure_password';

-- Migrate an existing user to caching_sha2_password
ALTER USER 'legacy_user'@'localhost'
    IDENTIFIED WITH caching_sha2_password BY 'new_password';

Connection Security Requirements

Accounts using caching_sha2_password require a secure transport for the password exchange:

• TLS connection -- the entire session is encrypted, including password exchange.
• RSA key pair -- if TLS is not used, the password is encrypted with the server's public RSA key (retrieved from the server automatically by modern connectors).
• If neither is available, the connection is refused with error ER_ACCESS_DENIED_NO_PASSWORD.

---

User and Role Management

User Accounts

MySQL user accounts define: identity, authentication credentials, default roles, TLS requirements, password policies, and resource limits.

-- Create a user with specific requirements
CREATE USER 'app_reader'@'10.0.%'
    IDENTIFIED BY 'reader_pass'
    REQUIRE SSL
    PASSWORD EXPIRE INTERVAL 90 DAY
    WITH MAX_QUERIES_PER_HOUR 1000;

-- Lock an account
ALTER USER 'deprecated_user'@'localhost' ACCOUNT LOCK;

Roles

MySQL 8.0 introduced native roles. A role is a named collection of privileges that can be granted to users:

-- Create a role
CREATE ROLE 'app_readonly';

-- Grant privileges to the role
GRANT SELECT ON appdb.* TO 'app_readonly';

-- Assign the role to users
GRANT 'app_readonly' TO 'analyst_user'@'10.0.%', 'reporting_user'@'10.0.%';

-- Set default roles (activated automatically at login)
SET DEFAULT ROLE 'app_readonly' TO 'analyst_user'@'10.0.%';

Privilege Levels

MySQL privileges are organised hierarchically:

Level	Example Privileges
Global (*.*)	CREATE USER, PROCESS, RELOAD, SHUTDOWN, SUPER, REPLICATION SLAVE
Database (db.*)	CREATE, DROP, GRANT OPTION, EVENT, ROUTINE
Table (db.table)	SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, TRIGGER
Column (db.table(col))	SELECT, INSERT, UPDATE (per-column granularity)
Routine (db.procedure)	EXECUTE, ALTER ROUTINE

-- Grant column-level privileges
GRANT SELECT (id, name, email), UPDATE (name, email)
    ON appdb.customers TO 'support_agent'@'10.0.%';

Password Policy

MySQL 8.0 provides built-in password validation via the validate_password component:

Variable	Default	Purpose
validate_password.policy	MEDIUM	Policy strength: LOW (length only), MEDIUM (length + mixed case + digits + special), STRONG (adds dictionary check).
validate_password.length	8	Minimum password length.
validate_password.mixed_case_count	1	Minimum uppercase and lowercase characters.
validate_password.number_count	1	Minimum digits.
validate_password.special_char_count	1	Minimum special characters.

---

TLS / SSL Encryption

MySQL supports TLS 1.2 and TLS 1.3 (MySQL 8.0.28+) for encrypting client-server and replication channels.

Configuring TLS

# my.cnf server configuration
[mysqld]
ssl_ca     = /etc/mysql/ca.pem
ssl_cert   = /etc/mysql/server-cert.pem
ssl_key    = /etc/mysql/server-key.pem
require_secure_transport = ON

Setting require_secure_transport = ON forces all connections to use TLS. Connections without TLS are rejected.

Per-User TLS Requirements

-- Require a valid client certificate
CREATE USER 'secure_app'@'10.0.%'
    IDENTIFIED BY 'password'
    REQUIRE X509;

-- Require a specific certificate subject
CREATE USER 'strict_app'@'10.0.%'
    IDENTIFIED BY 'password'
    REQUIRE SUBJECT '/CN=app.example.com/O=MyOrg/C=US';

-- Require a specific cipher suite
GRANT USAGE ON *.* TO 'secure_app'@'10.0.%'
    REQUIRE CIPHER 'TLS_AES_256_GCM_SHA384';

Replication Channel Encryption

Replication connections can be secured with TLS:

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'primary.example.com',
    SOURCE_USER = 'repl_user',
    SOURCE_PASSWORD = 'repl_pass',
    SOURCE_SSL = 1,
    SOURCE_SSL_CA = '/etc/mysql/ca.pem',
    SOURCE_SSL_CERT = '/etc/mysql/client-cert.pem',
    SOURCE_SSL_KEY = '/etc/mysql/client-key.pem';

---

Encryption at Rest

InnoDB Tablespace Encryption

MySQL 8.0 supports transparent tablespace encryption using the keyring plugin. When enabled, InnoDB encrypts data files (.ibd files) on disk using AES-256-ECB or AES-256-CBC.

Keyring Plugins

Plugin	Storage	Notes
keyring_file	Local file	Development only. Key stored in plaintext on disk.
keyring_encrypted_file	Encrypted local file	Password-protected key file.
keyring_okv	Oracle Key Vault	Enterprise. Centralised key management.
keyring_aws	AWS KMS	Enterprise. Keys stored in AWS KMS.
keyring_hashicorp	HashiCorp Vault	Enterprise. Keys stored in Vault.

Enabling Tablespace Encryption

# my.cnf -- load the keyring plugin at startup
[mysqld]
early-plugin-load = keyring_file.so
keyring_file_data = /var/lib/mysql-keyring/keyring

-- Create an encrypted table
CREATE TABLE sensitive_data (
    id BIGINT PRIMARY KEY,
    ssn VARCHAR(11),
    credit_card VARCHAR(19)
) ENCRYPTION = 'Y';

-- Encrypt an existing table
ALTER TABLE customers ENCRYPTION = 'Y';

-- Encrypt the system tablespace
ALTER INSTANCE ROTATE INNODB MASTER KEY;

Redo Log and Undo Log Encryption

MySQL 8.0.17+ supports encrypting redo logs and undo logs independently:

-- Enable redo log encryption
ALTER INSTANCE ENABLE INNODB REDO_LOG_ENCRYPTION;

-- Enable undo log encryption
ALTER INSTANCE ENABLE INNODB UNDO_LOG_ENCRYPTION;

---

Audit Plugin

MySQL Enterprise Audit (audit_log plugin) records all MySQL server activity for compliance and forensic analysis.

Features

• Records: connections, queries, administrative commands, access denied events.
• Formats: XML (legacy), JSON, and CSV output.
• Filtering: by user, account, event class, or SQL pattern.
• Log rotation: automatic or manual.

# my.cnf -- load the audit plugin
[mysqld]
plugin-load = audit_log.so
audit_log_format = JSON
audit_log_policy = ALL
audit_log_rotate_on_size = 100M

Audit Log Filtering

-- Create a filter that logs only DML from specific users
INSERT INTO mysql.audit_log_filter (name, filter)
VALUES ('app_dml_only', '{
    "filter": {
        "class": {
            "name": "general",
            "event": {
                "name": {
                    "not_in": ["connect", "disconnect"]
                }
            }
        },
        "user": {
            "value": "app_user"
        }
    }
}');

-- Assign the filter to a user
INSERT INTO mysql.audit_log_user (user, host, filtername)
VALUES ('app_user', '%', 'app_dml_only');

Open Source Alternatives

The MySQL Enterprise Audit plugin requires an Enterprise licence. Open-source alternatives include MariaDB Audit Plugin (compatible with MySQL), Percona Audit Plugin, and McAfee MySQL Audit Plugin.

---

SQL Injection Prevention

MySQL provides several mechanisms to prevent SQL injection attacks:

Prepared Statements

Parameterised queries (prepared statements) separate SQL logic from data values. When used correctly, they prevent SQL injection by ensuring user input is always treated as data, not executable SQL.

-- Server-side prepared statement
PREPARE stmt FROM 'SELECT * FROM users WHERE email = ? AND status = ?';
SET @email = 'user@domain';
SET @status = 'active';
EXECUTE stmt USING @email, @status;
DEALLOCATE PREPARE stmt;

Application-level prepared statements (via connectors) are more common:

# Python example with mysql-connector-python
cursor.execute(
    "SELECT * FROM users WHERE email = %s AND status = %s",
    ("user@domain", "active")
)

Input Validation

• Use application-level validation to constrain input formats (email patterns, numeric ranges, whitelisted values).
• Never concatenate user input directly into SQL strings.

Least-Privilege Principle

Limit the damage of any successful injection by granting only the minimum privileges required:

-- Application user should not have DDL or admin privileges
CREATE USER 'web_app'@'10.0.%' IDENTIFIED BY 'app_pass';
GRANT SELECT, INSERT, UPDATE ON appdb.orders TO 'web_app'@'10.0.%';
-- No DELETE, no DROP, no GRANT OPTION

---

Security Best Practices

Production Security Checklist

• Use caching_sha2_password as the default authentication plugin (already default in 8.0).
• Enable require_secure_transport = ON to enforce TLS for all connections.
• Remove or disable anonymous accounts and test databases: DROP DATABASE IF EXISTS test;
• Run mysql_secure_installation after initial setup.
• Enable the validate_password component with at least MEDIUM policy.
• Use the keyring plugin for encryption at rest on tables containing sensitive data.
• Restrict SUPER and GRANT OPTION privileges to administrative accounts only.
• Enable audit logging for compliance-sensitive workloads.
• Rotate replication user credentials and TLS certificates periodically.
• Use REQUIRE X509 for administrative and replication accounts.
• Set local_infile = OFF to prevent LOAD DATA LOCAL INFILE attacks.

---

Sources

• MySQL 8.0 Reference Manual -- Security
• MySQL 8.0 Authentication Plugins
• MySQL 8.0 Encryption at Rest
• MySQL Enterprise Audit
• MySQL TLS Configuration