Security - MySQL - page 797

word hashing method than the original pre-4.1 method. (MySQL does not store literal
passwords in the mysql.user system table because that is insecure. Instead, the server
computes a hash value from the password and stores the hash string.) More recent
authentication changes include the introduction in MySQL 5.6 of the sha256_pass
word plug-in that implements SHA-256 password hashing and the validate_pass
word plug-in that implements password policy and password strength assessment. This
section describes characteristics of the 4.1 and (less secure) original hashing methods
and shows how to upgrade accounts that use the original method so they use the 4.1
method instead. For information about the sha256_password and validate_pass
word plug-ins, see Recipes 23.2 and 23.4 .
For any account with a nonempty Password value in its user table row, you can tell
which hashing method generated it:
• The hashing method introduced in MySQL 4.1 produces 41-character hash values
beginning with a * character. This is the “4.1” or “native” hashing method. For
accounts that have this type of password hash, the server authenticates connection
attempts using the mysql_native_password plug-in.
• The original hashing method produces 16-character hash values. This is the
“pre-4.1” or “old” hashing method. The server authenticates accounts that have this
type of password hash using the mysql_old_password authentication plug-in.
To see the difference between the two hash formats, generate hash values explicitly:
mysql> SET old_passwords = 0;
mysql> SELECT OLD_PASSWORD('mypass') AS old, PASSWORD('mypass') AS new\G
*************************** 1. row ***************************
old: 6f8c114b58f2ce9e
new: *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4
The example sets old_passwords to 0 because PASSWORD() uses the pre-4.1 hashing
method and returns the same result as OLD_PASSWORD() if old_passwords is set to 1.
Administrators should avoid creating accounts that use the older, less secure pre-4.1
hashing method. If your MySQL installation has accounts that have old password hash‐
es, you can upgrade them to use the 4.1 hashing method. (This will become necessary
eventually, anyway. Pre-4.1 hashing is deprecated as of MySQL 5.6 and support for it
will be dropped at some point.)
Additionally, each account should have a nonempty password.
To identify and upgrade insecure accounts, use this procedure:
1. Determine whether your user table contains accounts with weak security. A “weak”
account has either of these characteristics: