Security - MySQL - page 798

• The plugin column is mysql_native_password but the Password column is
empty.
• The plugin column is empty or mysql_old_password . (If the value is empty, the
server authenticates clients using either mysql_native_password or
mysql_old_password , making the choice based on the hash format of the value
stored in the Password column. To prevent the possibility of implicit authenti‐
cation using mysql_old_password , set the plug-in to mysql_native_password .)
Use this query to find weak accounts with those characteristics:
SELECT User , Host , plugin , Password FROM mysql . user
WHERE ( plugin = 'mysql_native_password' AND Password = '' )
OR plugin IN ( '' , 'mysql_old_password' );
2. Before upgrading a weak account, consider whether the account is even necessary.
Perhaps it was created long ago for a project that's no longer used and you can simply
remove it:
DROP USER 'olduser' @ 'localhost' ;
The result is one less account to be protected and one less point of exploit.
3. If a weak account must be retained, upgrade it:
• If the plug-in is empty or mysql_old_password , change it to mysql_native_pass
word so that pre-4.1 password hashing cannot be used.
• If the password is empty or in pre-4.1 hash format, assign a new password using
4.1 hashing.
Suppose that a server's user population includes accounts with the following authenti‐
cation characteristics, most of which need improvement. (All have a Host value of
localhost , although it's not shown here.)
mysql> SELECT User, plugin, Password FROM mysql.user
-> WHERE User LIKE 'user%' AND Host = 'localhost' ORDER BY User;
+-------+-----------------------+-------------------------------------------+
| User | plugin | Password |
+-------+-----------------------+-------------------------------------------+
| user1 | mysql_native_password | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
| user2 | | |
| user3 | mysql_old_password | |
| user4 | | 6f8c114b58f2ce9e |
| user5 | mysql_old_password | 6f8c114b58f2ce9e |
| user6 | mysql_native_password | |
| user7 | | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------+-----------------------+-------------------------------------------+
The requirements for better security are that each account names the mysql_na
tive_password plug-in explicitly and has a nonempty password in 4.1 hash format.