MySQL Security Checks

This page is original PmaControl documentation. It keeps a practical index structure for MySQL optimization work, without copying third-party content or depending on an external product.

Objective

MySQL security checks reduce attack surface: overpowered accounts, unencrypted connections, weak passwords, unnecessary global privileges, and network exposure.

Priority Checks

1. Anonymous accounts or accounts without a password.
2. Users with unjustified SUPER, FILE, PROCESS, GRANT OPTION, or global privileges.
3. % access where a precise host is enough.
4. TLS not enforced for sensitive flows.
5. local_infile enabled without need.
6. Binary logs and backups readable by overly broad system accounts.

Basic SQL

SELECT user, host, account_locked, password_expired
FROM mysql.user
ORDER BY user, host;

SHOW GRANTS FOR 'app_user'@'%';

Key Point

The rule is simple: every privilege needs an owner, a justification, and a lifetime.

Define scope and collect account evidence

Inventory database servers, proxies, application accounts, administrators, replicas and backup tools. Associate every account with an owner and intended source host. The following account columns apply to MySQL; MariaDB stores account information differently depending on version. Use a suitably authorized audit account and avoid exporting password hashes or credentials into reports.

SELECT VERSION() AS server_version,
       CURRENT_USER() AS matched_account, USER() AS connection_identity;

SELECT user, host, plugin, account_locked, password_expired, ssl_type
FROM mysql.user
ORDER BY user, host;

SELECT grantee, privilege_type, is_grantable
FROM information_schema.user_privileges
ORDER BY grantee, privilege_type;

CURRENT_USER() identifies the account used for privilege checks; USER() describes the connection identity. The global-privilege inventory is incomplete by itself: inspect SHOW GRANTS for database/table privileges, dynamic privileges and assigned roles, including default/active roles. An empty authentication string is not sufficient proof of an insecure account because authentication plugins differ. Verify the plugin, host restriction, locking state and actual authentication policy.

Verify transport on the actual connection

SHOW GLOBAL VARIABLES WHERE Variable_name IN (
  'require_secure_transport', 'tls_version', 'local_infile',
  'secure_file_priv', 'bind_address', 'port'
);
SHOW SESSION STATUS WHERE Variable_name IN ('Ssl_cipher', 'Ssl_version');

The session TLS fields describe this connection only. A Unix socket can be a valid secure local transport without a TLS cipher. For TCP, check encryption and certificate/hostname verification in the application driver. Test each leg when a proxy terminates TLS: client-to-proxy and proxy-to-server. A server accepting TLS does not mean that all clients require it.

mysql --host=db.example --user=app_reader --password \
  --ssl-mode=VERIFY_IDENTITY --ssl-ca=/path/to/ca.pem

This MySQL-client example prompts for the password and verifies identity; replace the host and CA path. Check the connector’s own options for other clients. Plan certificate rotation and test new application connections before enforcing a new policy, so existing sessions do not hide a reconnect failure.

Review exposure beyond SQL privileges

Apply least privilege by job: separate normal application traffic, migrations, monitoring and backup. Test privilege removals and host restrictions with fresh connections, retain the original grants for rollback, and monitor denied operations. Update server and connector versions through a documented upgrade path; authentication-plugin support is version-dependent. A security review must end with an owner, remediation date and verified result for each finding.

Continue the investigation

• Include security findings in the evaluation page, and verify operational readiness with health checks.

Official references

• MySQL encrypted connections
• MySQL SHOW GRANTS