How to Harden MySQL with SQL: Password Policies, SSL, and Access Controls

MySQL stores valuable data, so securing it is critical. Starting with MySQL 8.0, many configuration tasks can be performed directly via SQL, eliminating the need for OS‑level changes and simplifying automation.

Strengthen the root account

After installation, change the initial root password:

ALTER USER root@localhost IDENTIFIED BY '<auth_string>';

If multiple root accounts exist, consider removing the global "root@%" account to reduce remote attack surface.

Enforce password policies

Check whether the password‑validation component is installed:

SELECT component_urn, 'PASSWORD Policy Component Installed?' AS Note,
       IF(COUNT(component_urn) > 0, 'YES', 'NO') AS Answer
FROM mysql.component
WHERE component_urn='file://component_validate_password'
GROUP BY component_urn;

Install and configure the component with strong settings:

INSTALL COMPONENT 'file://component_validate_password';
SET PERSIST validate_password.check_user_name='ON';
SET PERSIST validate_password.dictionary_file='<FILENAME OF DICTIONARY FILE>';
SET PERSIST validate_password.length=15;
SET PERSIST validate_password.mixed_case_count=1;
SET PERSIST validate_password.special_char_count=2;
SET PERSIST validate_password.number_count=2;
SET PERSIST validate_password.policy='STRONG';
SET PERSIST password_history=5;
SET PERSIST password_reuse_interval=365;
SET GLOBAL default_password_lifetime=180;

Connection control and SSL

Install the connection‑control plugin and set thresholds for failed login attempts:

INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
SET PERSIST connection_control_failed_connections_threshold=4;
SET PERSIST connection_control_min_connection_delay=1500;

Force encrypted connections:

SET PERSIST require_secure_transport=ON;

File import/export permissions

Verify the secure_file_priv variable to control import/export directories:

SELECT VARIABLE_NAME, VARIABLE_VALUE, 'Secure File Check' AS Note,
       IF(LENGTH(VARIABLE_VALUE) > 0 AND VARIABLE_VALUE!='NULL', 'FAIL', 'PASS') AS SecFileCheck
FROM performance_schema.global_variables
WHERE VARIABLE_NAME='secure_file_priv';

Local infile setting

SELECT IF(@@local_infile, 'ON', 'OFF') AS LOCAL_LOAD_DATA_ALLOWED;
SET PERSIST local_infile=OFF;

User authentication and roles

List internal users and their authentication plugins:

SELECT host, user, plugin,
       IF(plugin='mysql_native_password','WEAK SHA1','STRONG SHA2') AS HASHTYPE
FROM mysql.user
WHERE user NOT IN ('mysql.infoschema','mysql.session')
  AND plugin NOT LIKE 'auth%'
  AND plugin!='mysql_no_login'
  AND LENGTH(authentication_string)>0
ORDER BY plugin;

Show X.509‑based internal users:

SELECT `user`.`Host`, `user`.`User`, `user`.`ssl_type`,
       CAST(`user`.`x509_issuer` AS CHAR) AS Issuer,
       CAST(`user`.`x509_subject` AS CHAR) AS Subject
FROM mysql.user
WHERE `user`.`User` NOT LIKE 'mysql.%' AND ssl_type='X509';

External authentication plugins (LDAP, Kerberos, etc.):

SELECT `user`.`Host`, `user`.`User`, `user`.`plugin`, `user`.`authentication_string`
FROM mysql.user
WHERE plugin LIKE 'auth%';

Example of two‑factor authentication:

CREATE USER 'alice'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'sha2_password'
       AND IDENTIFIED WITH authentication_ldap_sasl AS 'uid=u1_ldap,ou=People,dc=example,dc=com';
ALTER USER 'alice'@'localhost' ADD 3 FACTOR IDENTIFIED WITH authentication_fido;

Privileges and access checks

Check maximum connections and adjust if needed:

SELECT VARIABLE_NAME, VARIABLE_VALUE, 'MUST be 210 or less' AS Note,
       IF(VARIABLE_VALUE < 211, 'PASS', 'FAIL')
FROM performance_schema.global_variables
WHERE VARIABLE_NAME='max_connections';
SET PERSIST max_connections=210;

Generate reports of user/table privileges, role usage, and global privileges using CTEs and UNION queries (see original source for full statements).

Encryption verification

Confirm that InnoDB redo, undo, binlog, and audit logs are encrypted:

SELECT VARIABLE_NAME, VARIABLE_VALUE, 'innodb_redo_log AT REST ENCRYPTION' AS Note,
       IF(VARIABLE_VALUE='ON','PASS','FAIL') AS CHECK_VAL
FROM performance_schema.global_variables
WHERE VARIABLE_NAME='innodb_redo_log_encrypt';
-- Repeat for innodb_undo_log_encrypt, binlog_encryption, audit_log_encryption

Check tablespace encryption status:

SELECT `NAME`, `ENCRYPTION`, IF(ENCRYPTION='Y','PASS','FAIL') AS CHECK_VAL
FROM information_schema.INNODB_TABLESPACES
WHERE ENCRYPTION='N';

Verify that the keyring plugin is active:

SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE
FROM information_schema.PLUGINS
WHERE PLUGIN_NAME LIKE 'keyring_file' AND PLUGIN_STATUS='ACTIVE';

Additional system checks

List MySQL ports (port, mysqlx_port, admin_port).

Inspect file‑related variables (directories, files) for secure locations.

Review plugin list and status.

By executing these SQL statements, DBAs can audit, harden, and continuously monitor MySQL security without leaving the database environment.