MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
The MySQL server authenticates connection attempts for each
account listed in the mysql.user
system table
using the authentication plugin named in the
plugin
column. If the
plugin
column is empty, the server
authenticates the account as follows:
Before MySQL 5.7, the server uses the
mysql_native_password
or
mysql_old_password
plugin implicitly,
depending on the format of the password hash in the
Password
column. If the
Password
value is empty or a 4.1 password
hash (41 characters), the server uses
mysql_native_password
. If the password
value is a pre-4.1 password hash (16 characters), the server
uses mysql_old_password
. (For additional
information about these hash formats, see
Section 6.1.2.4, “Password Hashing in MySQL”.)
As of MySQL 5.7, the server requires the
plugin
column to be nonempty and disables
accounts that have an empty plugin
value.
Pre-4.1 password hashes and the
mysql_old_password
plugin are deprecated in
MySQL 5.6 and support for them is removed in MySQL 5.7. They
provide a level of security inferior to that offered by 4.1
password hashing and the
mysql_native_password
plugin.
Given the requirement in MySQL 5.7 that the
plugin
column must be nonempty, coupled with
removal of mysql_old_password
support, DBAs
are advised to upgrade accounts as follows:
Upgrade accounts that use
mysql_native_password
implicitly to use
it explicitly
Upgrade accounts that use
mysql_old_password
(either implicitly or
explicitly) to use mysql_native_password
explicitly
The instructions in this section describe how to perform those
upgrades. The result is that no account has an empty
plugin
value and no account uses pre-4.1
password hashing or the mysql_old_password
plugin.
As a variant on these instructions, DBAs might offer users the
choice to upgrade to the sha256_password
plugin, which authenticates using SHA-256 password hashes. For
information about this plugin, see
Section 6.4.1.4, “SHA-256 Pluggable Authentication”.
The following table lists the types of
mysql.user
accounts considered in this
discussion.
plugin Column |
Password Column |
Authentication Result | Upgrade Action |
---|---|---|---|
Empty | Empty | Implicitly uses mysql_native_password |
Assign plugin |
Empty | 4.1 hash | Implicitly uses mysql_native_password |
Assign plugin |
Empty | Pre-4.1 hash | Implicitly uses mysql_old_password |
Assign plugin, rehash password |
mysql_native_password |
Empty | Explicitly uses mysql_native_password |
None |
mysql_native_password |
4.1 hash | Explicitly uses mysql_native_password |
None |
mysql_old_password |
Empty | Explicitly uses mysql_old_password |
Upgrade plugin |
mysql_old_password |
Pre-4.1 hash | Explicitly uses mysql_old_password |
Upgrade plugin, rehash password |
Accounts corresponding to lines for the
mysql_native_password
plugin require no
upgrade action (because no change of plugin or hash format is
required). For accounts corresponding to lines for which the
password is empty, consider asking the account owners to choose
a password (or require it by using ALTER
USER
to expire empty account passwords).
Accounts that have an empty plugin and a 4.1 password hash use
mysql_native_password
implicitly. To upgrade
these accounts to use mysql_native_password
explicitly, execute these statements:
UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE plugin = '' AND (Password = '' OR LENGTH(Password) = 41); FLUSH PRIVILEGES;
Before MySQL 5.7, you can execute those statements to uprade accounts proactively. As of MySQL 5.7, you can run mysql_upgrade, which performs the same operation among its upgrade actions.
Notes:
The upgrade operation just described is safe to execute at
any time because it makes the
mysql_native_password
plugin explicit
only for accounts that already use it implicitly.
This operation requires no password changes, so it can be performed without affecting users or requiring their involvement in the upgrade process.
Accounts that use mysql_old_password
(either
implicitly or explicitly) should be upgraded to use
mysql_native_password
explicitly. This
requires changing the plugin and changing
the password from pre-4.1 to 4.1 hash format.
For the accounts covered in this step that must be upgraded, one of these conditions is true:
The account uses mysql_old_password
implicitly because the plugin
column is
empty and the password has the pre-4.1 hash format (16
characters).
The account uses mysql_old_password
explicitly.
To identify such accounts, use this query:
SELECT User, Host, Password FROM mysql.user WHERE (plugin = '' AND LENGTH(Password) = 16) OR plugin = 'mysql_old_password';
The following discussion provides two methods for updating that set of accounts. They have differing characteristics, so read both and decide which is most suitable for a given MySQL installation.
Method 1.
Characteristics of this method:
It requires that server and clients be run with
secure_auth=0
until all users have been
upgraded to mysql_native_password
.
(Otherwise, users cannot connect to the server using their
old-format password hashes for the purpose of upgrading to a
new-format hash.)
It works for MySQL 5.5 and 5.6. In 5.7, it does not work because the server requires accounts to have a nonempty plugin and disables them otherwise. Therefore, if you have already upgraded to 5.7, choose Method 2, described later.
You should ensure that the server is running with
secure_auth=0
.
For all accounts that use mysql_old_password
explicitly, set them to the empty plugin:
UPDATE mysql.user SET plugin = '' WHERE plugin = 'mysql_old_password'; FLUSH PRIVILEGES;
To also expire the password for affected accounts, use these statements instead:
UPDATE mysql.user SET plugin = '', password_expired = 'Y' WHERE plugin = 'mysql_old_password'; FLUSH PRIVILEGES;
Now affected users can reset their password to use 4.1 hashing. Ask each user who now has an empty plugin to connect to the server and execute these statements:
SET old_passwords = 0;
SET PASSWORD = PASSWORD('user-chosen-password
');
The client-side --secure-auth
option is enabled by default, so remind users to disable it or
they cannot connect:
shell> mysql -u user_name
-p --secure-auth=0
After an affected user has executed those statements, you can
set the corresponding account plugin to
mysql_native_password
to make the plugin
explicit. Or you can periodically run these statements to find
and fix any accounts for which affected users have reset their
password:
UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE plugin = '' AND (Password = '' OR LENGTH(Password) = 41); FLUSH PRIVILEGES;
When there are no more accounts with an empty plugin, this query returns an empty result:
SELECT User, Host, Password FROM mysql.user WHERE plugin = '' AND LENGTH(Password) = 16;
At that point, all accounts have been migrated away from pre-4.1
password hashing and the server no longer need be run with
secure_auth=0
.
Method 2.
Characteristics of this method:
It assigns each affected account a new password, so you must tell each such user the new password and ask the user to choose a new one. Communication of passwords to users is outside the scope of MySQL, but should be done carefully.
It does not require server or clients to be run with
secure_auth=0
.
It works for any version of MySQL 5.5 or later (and for 5.7 has an easier variant).
With this method, you update each account separately due to the need to set passwords individually. Choose a different password for each account.
Suppose that 'user1'@'localhost'
is one of
the accounts to be upgraded. Modify it as follows:
In MySQL 5.7, ALTER USER
provides the
capability of modifying both the account password and its
authentication plugin, so you need not modify the
mysql.user
system table directly:
ALTER USER 'user1'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'DBA-chosen-password
';
To also expire the account password, use this statement instead:
ALTER USER 'user1'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'DBA-chosen-password
'
PASSWORD EXPIRE;
Then tell the user the new password and ask the user to connect to the server with that password and execute this statement to choose a new password:
ALTER USER USER() IDENTIFIED BY 'user-chosen-password
';
Before MySQL 5.7, you must modify the
mysql.user
system table directly using
these statements:
SET old_passwords = 0;
UPDATE mysql.user SET plugin = 'mysql_native_password',
Password = PASSWORD('DBA-chosen-password
')
WHERE (User, Host) = ('user1', 'localhost');
FLUSH PRIVILEGES;
To also expire the account password, use these statements instead:
SET old_passwords = 0;
UPDATE mysql.user SET plugin = 'mysql_native_password',
Password = PASSWORD('DBA-chosen-password
'), password_expired = 'Y'
WHERE (User, Host) = ('user1', 'localhost');
FLUSH PRIVILEGES;
Then tell the user the new password and ask the user to connect to the server with that password and execute these statements to choose a new password:
SET old_passwords = 0;
SET PASSWORD = PASSWORD('user-chosen-password
');
Repeat for each account to be upgraded.