DBAtlas · Database operations atlas

Audit risky accounts: empty password, '%' host, SUPER, mysql_native_password

MySQLSecurity

Command

SELECT user, host, plugin,
       CONCAT_WS(', ',
         IF(authentication_string = '' AND account_locked = 'N', 'EMPTY PASSWORD', NULL),
         IF(host = '%', 'ANY HOST', NULL),
         IF(plugin = 'mysql_native_password', 'NATIVE AUTH', NULL),
         IF(Super_priv = 'Y', 'SUPER', NULL),
         IF(Grant_priv = 'Y', 'GRANT OPTION', NULL),
         IF(File_priv = 'Y', 'FILE', NULL),
         IF(ssl_type = '', 'NO TLS REQUIRED', NULL),
         IF(password_expired = 'Y', 'EXPIRED', NULL),
         IF(account_locked = 'Y', 'LOCKED', NULL)) AS findings,
       password_last_changed
FROM mysql.user
WHERE user NOT IN ('mysql.sys', 'mysql.session', 'mysql.infoschema')
ORDER BY (Super_priv = 'Y') DESC, user;

-- Accounts holding powerful dynamic privileges
SELECT GRANTEE, PRIVILEGE_TYPE FROM information_schema.USER_PRIVILEGES
WHERE PRIVILEGE_TYPE IN ('SUPER', 'SYSTEM_VARIABLES_ADMIN', 'CONNECTION_ADMIN', 'BINLOG_ADMIN', 'REPLICATION_SLAVE_ADMIN')
ORDER BY GRANTEE;

What it does

Lists security findings per account in one row: no password, connectable from anywhere, legacy authentication plugin, SUPER / GRANT / FILE privileges and no TLS requirement; the second query shows dangerous dynamic privileges.

When to use it

During periodic security audits, on a newly inherited server and before an 8.4 upgrade.

Caution

The query reads mysql.user directly and needs SELECT on it. Role accounts are locked with no password, so EMPTY PASSWORD is reported only for unlocked accounts. Super_priv is the static SUPER privilege; since 8.0 splits it into dynamic privileges, check the second query too. Accounts flagged NATIVE AUTH must move to caching_sha2_password before 8.4: ALTER USER 'u'@'h' IDENTIFIED WITH caching_sha2_password BY '...'; old clients / connectors may not support caching_sha2_password, test them first.

Open in DBAtlas →

Related