MySQL Security Guide: Accounts, Privileges and Hardening

MySQL breaches rarely need a zero-day: a root account reachable from the network, an application holding global privileges, or a FILE grant that turns a SQL injection into file access on the host is usually enough. MySQL 8.4 LTS closed many historic gaps, with caching_sha2_password as the default, TLS 1.0 and 1.1 removed and mysql_native_password disabled by default, but many estates still run upgraded 8.0 instances that carry older accounts and settings. This guide covers MySQL Community and Enterprise 8.4 and the 9.x series: the account model, authentication plugins, password and lockout policy, privileges and roles, server options that limit file and plugin abuse, encryption, auditing and patching. It is for DBAs, platform engineers and security engineers running MySQL themselves or on a managed service. Engine-neutral principles live in the Database Security Guide; this page goes deeper on MySQL itself.

Key takeaways

• An account is user plus host. 'app'@'10.0.1.%' and 'app'@'%' are different accounts with different passwords and grants, so wildcard hosts quietly widen who can log in.
• Move off mysql_native_password now. It was deprecated in 8.0.34, is disabled by default in 8.4 and is removed in 9.0, so any account still using it blocks your upgrade path.
• Global privileges are the danger zone. Grants on *.*, and especially FILE, SUPER, SYSTEM_USER and CREATE USER, give far more than any application needs.
• Native lockout and rotation exist, use them. FAILED_LOGIN_ATTEMPTS, PASSWORD_LOCK_TIME and dual passwords with RETAIN CURRENT PASSWORD support safe service credential rotation without downtime.
• Audit is an edition decision. MySQL Enterprise Audit is part of the commercial Enterprise Edition; Community users need an alternative audit plugin, managed service audit features or network-level monitoring.

The account model: user, host and the grant tables

MySQL identifies an account by both a user name and a host pattern, stored in the mysql.user table along with its authentication plugin and global privileges. When a client connects, the server picks the most specific matching host entry. This has three practical consequences.

• Wildcards widen exposure: '%' matches any address. Prefer an exact IP or a narrow subnet such as '10.0.1.%', and remove '%' accounts that duplicate a narrower one.
• Anonymous and test accounts: older installers created anonymous users (empty user name) and a test database open to all. Check with SELECT user, host, plugin FROM mysql.user; and drop any account with an empty user name.
• Hostnames depend on DNS: with name resolution on, a host pattern such as '%.corp.example' trusts reverse DNS. Setting skip_name_resolve = ON forces IP-based host matching and removes that dependency.

The MySQL security guidelines are blunt on the system tables: never give anyone except root accounts access to mysql.user, never grant privileges to all hosts, and test whether mysql -u root connects without a password. Use SHOW GRANTS FOR 'user'@'host'; to review each account and REVOKE what is not needed.

Authentication plugins and transport encryption

MySQL 8.4 accepts encrypted connections only over TLSv1.2 and TLSv1.3; the documentation states that TLSv1 and TLSv1.1 were deprecated and removed in MySQL 8.0. Set require_secure_transport = ON so the server rejects unencrypted TCP connections (local socket connections remain allowed), and for individual accounts add REQUIRE SSL or REQUIRE X509 with CREATE USER or ALTER USER for certificate-bound service accounts. On the client side, the default --ssl-mode=PREFERRED does not verify the server; use VERIFY_IDENTITY with a CA file in application connection strings. Keep port 3306 (and 33060 for the X Protocol, if enabled) behind a firewall, and bind bind_address to a private interface. See the mTLS Guide for certificate-based service authentication.

Password policy, lockout and rotation

MySQL 8.x has a richer native password policy than most databases. The settings below can be applied globally and overridden per account.

Dual passwords are the most useful feature for non-human identities. A rotation job sets the new password with RETAIN CURRENT PASSWORD, updates the secret in the vault, waits for every application instance to reconnect, confirms in the connection logs that the old secret is no longer used, then runs DISCARD OLD PASSWORD. Managing your own secondary password needs APPLICATION_PASSWORD_ADMIN; managing other accounts' passwords needs CREATE USER. See the Secrets Management Guide and the Password Policy Guide (NIST-aligned).

Privileges, roles and partial revokes

MySQL privileges apply at global (*.*), database (db.*), table, column and routine level. Global static privileges such as FILE, PROCESS, RELOAD, SHUTDOWN and SUPER sit alongside dynamic privileges introduced in 8.0 that split SUPER into narrower rights.

• Roles: MySQL 8 supports CREATE ROLE, GRANT role TO user and default roles via SET DEFAULT ROLE. Build roles such as app_rw, app_ro and app_migrate per schema and grant them, rather than granting privileges directly to accounts. Note that granted roles are not active in a session unless set as default or activated, unless activate_all_roles_on_login is ON.
• Partial revokes: with partial_revokes = ON (default OFF) you can grant a global privilege and revoke it for specific schemas, for example letting an operations account modify every schema except mysql. It applies to schema-level privileges only, not to global-only privileges such as FILE, and once any account uses it the variable cannot be switched off until those restrictions are removed. In replication it must be enabled on all hosts.
• Definers: views, routines, triggers and events default to SQL SECURITY DEFINER. Use SQL SECURITY INVOKER where the caller's rights should apply, and make sure definers are dedicated, locked accounts (ACCOUNT LOCK) so the code keeps working without a usable login.

Server hardening options

Run mysqld as a dedicated, unprivileged OS user, keep the data directory and option files readable only by that user, and never store client passwords in plain ~/.my.cnf files on shared hosts; mysql_config_editor obfuscates rather than encrypts, so treat login path files as secrets too (ATT&CK T1552.001 Credentials In Files). See the Linux Server Hardening Guide for the host layer.

Encryption at rest and backups

MySQL supports InnoDB data-at-rest encryption for file-per-table and general tablespaces, redo and undo logs, and binary and relay log encryption (binlog_encryption), with master keys held by a keyring component. Keep the keyring outside the data directory and outside backup sets, or use a key management service through an Enterprise keyring, so a stolen backup does not carry its own key. Encryption at rest protects files, disks and backups; it does not restrict anyone who logs in with valid credentials. Backups from mysqldump or physical tools are full copies of the data: encrypt them, store them on immutable storage with separate credentials, and restore-test them. The JADEPUFFER agentic ransomware case shows why: an agent holding root MySQL credentials dropped configuration and history tables and whole databases, and inserted a backdoor admin directly into the database. See the Backup Security and Immutable Backup Guide.

Auditing and detection

MySQL Enterprise Audit is included only in the commercial Enterprise Edition. It is implemented as the audit_log plugin, writes new-style XML by default (JSON is selectable through audit_log_format) and supports rule-based filters per account and event class. Community Edition users can rely on the general query log (too verbose and unsafe for production because it records statements with values), the error log for failed logins, Performance Schema tables such as events_statements_history and host_cache, third-party audit plugins, or the audit features of their managed service.

• Authentication: bursts of access-denied errors per account or source (T1110 Brute Force) and any error 3957 lockout.
• Account changes: CREATE USER, GRANT of global privileges, ALTER USER on root or definer accounts outside change windows (T1078 Valid Accounts persistence).
• File and code paths: SELECT ... INTO OUTFILE, LOAD_FILE(), CREATE FUNCTION ... SONAME, and new events or triggers.
• Bulk access and destruction: row counts far above the account's baseline (T1213.006 Databases), DROP DATABASE and mass deletes (T1485 Data Destruction).
• Connections by root over TCP: should never happen if root is restricted to localhost with auth_socket.

Forward logs off host so a compromised administrator cannot erase them. See the Logging and Monitoring Policy Template.

Versions, patching and managed services

Oracle's end-of-life notice states that MySQL 8.0 moved to Sustaining Support on 21 April 2026 and recommends upgrading to MySQL 8.4 LTS or 9.7 LTS; innovation releases between LTS versions are short-lived. Sustaining Support means no new security fixes, so any 8.0 instance is now an unpatched asset. Upgrading to 8.4 is also where mysql_native_password accounts surface, because the plugin is disabled by default: inventory them with SELECT user, host FROM mysql.user WHERE plugin = 'mysql_native_password'; and re-key them with ALTER USER ... IDENTIFIED WITH caching_sha2_password BY ... before the upgrade. Track Oracle's quarterly Critical Patch Updates and the CISA KEV catalogue. On managed services the provider patches the engine and withholds true root, but you still own network rules, TLS enforcement, accounts and grants, and log export. See the Patch Management Guide.

Implementation roadmap

1. Inventory: list every instance, version, edition, bind_address, and every account with its host, plugin and global privileges (SELECT * FROM mysql.user review plus SHOW GRANTS).
2. Close exposure: remove internet reachability on 3306 and 33060, drop anonymous and '%' duplicate accounts, restrict root to localhost. Snapshot grants with SHOW GRANTS output first so any removed grant can be restored in seconds.
3. Turn on visibility: enable audit (Enterprise Audit, a plugin or managed service audit) in log-only mode and baseline which accounts and privileges are actually used for two weeks.
4. Fix authentication: re-key mysql_native_password accounts, enable require_secure_transport, set client VERIFY_IDENTITY, install validate_password, set lockout on human accounts.
5. Reduce privilege: replace global grants with schema roles, split runtime and migration accounts, remove FILE and SUPER, lock definer accounts. Roll out one application at a time in pilot rings.
6. Rotate service credentials: move secrets into a vault and adopt dual-password rotation, or move to IAM tokens on managed services.
7. Baseline and drift: apply a CIS MySQL Benchmark profile through configuration management and alert on drift; upgrade any 8.0 instance to an LTS release.

Choosing your approach

For a small team, a managed MySQL service with IAM authentication, enforced TLS, schema roles and the provider's audit log covers most risk with little operational load. Larger estates that self-host should weigh Enterprise Edition for native audit, LDAP or Kerberos authentication and KMS-backed keyrings against Community Edition plus third-party audit and a strong secrets manager. Use certificate-bound accounts (REQUIRE X509) where you already operate a private CA; use vault-issued passwords with dual-password rotation where you do not.

Metrics that show it is working

• Exposure: instances reachable from untrusted networks (target: zero).
• Legacy authentication: accounts still on mysql_native_password (target: zero before any 9.x upgrade).
• Privilege: accounts with global privileges, FILE or SUPER, trending to named break-glass accounts only.
• Encryption: percentage of sessions using TLS, from Performance Schema status_by_thread or connection logs.
• Rotation: median age of service account passwords.
• Patch currency: instances on a supported LTS and latest patch release.

Framework mapping

Relevant ATT&CK techniques: T1190 Exploit Public-Facing Application, T1078.001 Default Accounts, T1110 Brute Force, T1505.001 SQL Stored Procedures, T1213.006 Databases, T1485 Data Destruction and T1486 Data Encrypted for Impact.

Common failure patterns

• Root from anywhere: 'root'@'%' with a reused password, often created by a container image environment variable and never removed (T1078.001 Default Accounts).
• Application with ALL ON *.*: one account for the application, migrations and humans, so a SQL injection can read every schema and, with FILE, write web shells through INTO OUTFILE. See the SQL Injection Prevention Guide.
• Unlimited secure_file_priv: an empty value left in place after a data load, removing the only directory restriction on file functions.
• TLS on the server, PREFERRED on clients: encrypted in theory, but no client checks the server certificate.
• Stuck on 8.0: instances past their support date because mysql_native_password clients were never upgraded.
• Single-copy backups: dumps on the same host or bucket the database credentials can delete, as in the JADEPUFFER destruction above.

Practitioner checklist

• No anonymous accounts, no 'root'@'%', root restricted to localhost with auth_socket or a vaulted password.
• No accounts on mysql_native_password; all on caching_sha2_password, certificates or IAM tokens.
• require_secure_transport = ON and client connection strings use VERIFY_IDENTITY.
• Application accounts hold schema roles only, with no global privileges, FILE, SUPER or GRANT OPTION, and a specific host pattern.
• local_infile = OFF, secure_file_priv set to a dedicated directory or NULL, skip_name_resolve = ON.
• validate_password installed and FAILED_LOGIN_ATTEMPTS with PASSWORD_LOCK_TIME set on human accounts.
• Service secrets in a vault with dual-password rotation and an owner recorded per account.
• Audit logging enabled for account, privilege and DDL events, shipped off host with alerts.
• Tablespace, binary log and backup encryption on, with keyring material stored apart from backups; restores tested quarterly.
• Every instance on a supported LTS release with the latest Critical Patch Update.

Standards and references

• MySQL: General Security Guidelines (8.4 Reference Manual)
• MySQL: Password Management
• MySQL: Native Pluggable Authentication
• MySQL: Encrypted Connection TLS Protocols and Ciphers
• MySQL: Privilege Restriction Using Partial Revokes
• MySQL: Security-Related mysqld Options and Variables
• MySQL: MySQL Enterprise Audit
• Oracle: MySQL Product Support EOL Announcements

Related NHI Mgmt Group resources: Database Security Guide · Database Credential and Access Security Guide · PostgreSQL Security Guide · Privileged Access Management Guide · Secrets Management Guide