MySQL 4.1 extended password hashes from 16 to 41 bytes. However, upgrading MySQL does not automatically update the old password passwords, so existing passwords continue to be stored in the deprecated format. This is because MySQL does not store passwords as plain text, so regenerating password hashes requires user intervention.
The associated secure_auth option
was enabled by default as of MySQL 5.6. It is always enabled as of
MySQL 5.7, meaning it can not be disabled. A
future MySQL release will remove this option. With this option
enabled, a user with a password defined in the old format will not
be able to login to MySQL.
With all that said, the deprecated password format does not function with MySQL 5.7. All passwords using the old format must be updated. This section documents how to upgrade these passwords using MySQL Workbench. For information about migrating away from the old password format using the MySQL command line instead of MySQL Workbench, see Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin.
The method that MySQL stores a password is defined by an
authentication plugin. The old method uses the
mysql_old_password authentication plugin, and
the current default method uses
mysql_native_password. As of MySQL 5.6, a
sha256_password option is also available
although it requires an SSL or encrypted connection. When
MySQL Workbench upgrades passwords, it upgrades
mysql_old_password to
mysql_native_password. For additional
information about authentication plugins, see
Pluggable Authentication.
The mysql_native_password authentication
plugin is deprecated as of MySQL Server 8.0.34, disabled by
default as of MySQL Server 8.4.0, and removed as of MySQL Server
9.0.0.
MySQL Workbench uses mysql_native_password by
default, so for example enabling it is required to create a
MySQL Server 8.4 user. Include
mysql_native_password=ON in the
[mysqld] section of your MySQL Server 8.4
my.cnf to enable this plugin.
Options Depend on your secure_auth Option
Upgrading a password does have constraints. Here are two scenarios:
If the
secure_authMySQL Server option is disabled, then you can log in using the user with the old password format and update the user's own MySQL password. However, this is not an option as of MySQL Workbench 6.3.5 because compatibility with the old password format was removed. For this reason, a user's ability to upgrade their own password format must be done using the MySQL command line as described in Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin.NoteIf using the MySQL command line is not an option, then you could use an older version of MySQL Workbench (version 6.3.4 and earlier), which allows you to enable a Use the old authentication protocol option under the Advanced connections tab. Older versions of MySQL Workbench are available at https://downloads.mysql.com/archives/workbench/.
As stated earlier,
secure_authis enabled by default as of MySQL 5.6, and always enabled as of MySQL 5.7.If
secure_authis enabled, you can not log in if your user's password is stored in the old format. Attempts will fail and emit an error similar to "ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)". To upgrade the password, you can either disablesecure_auth(not recommended) then update as described above, or log in as a different and privileged user, such as root, to change the password for a different user.
Using MySQL Workbench to Update Your Password
Keeping the above in mind, there are two methods to update passwords using MySQL Workbench.
Open the Users and Privileges tab from the Management navigator. Select the user account you want to update from the User Accounts section. If using the old password format, you will see text beginning with "This account is using the pre-mysql-4.1.1 password hashing type." in the lower right corner of the screen, and also a large button on the right. From here, you can:
Option for all MySQL versions:
Manually enter a new password, or the current password, and click . This upgrades the password to the newer password format, and the MySQL user can now log in using the new password that you defined.
Option for MySQL 5.6 and later:
Rather than editing the password field, leave it alone and immediately click . From here, you can generate a random password and tag it as expired by clicking . Use this temporary random password to login the user, and MySQL will prompt for a new password when the user first logs in.
The following figures demonstrate the sequence of steps used in both methods:
When resetting to a random password, you must save the password and give it to the user. You will find the random password in the new popup window that is similar to the following figure.
After completing the upgrade, notice the new
Authentication Type for the connection. In
the example shown in the next figure, the value changed from
Standard (old) to
Standard. In other words, the
authentication type changed from
mysql_old_password to
mysql_native_password.
