| Bug #95841 | Convert users from mysql_native_password to caching_sha2_password | ||
|---|---|---|---|
| Submitted: | 17 Jun 2019 13:59 | Modified: | 18 Jun 2019 16:26 |
| Reporter: | IGG t | Email Updates: | |
| Status: | Unsupported | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.16 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | authentication, plugin | ||
[17 Jun 2019 14:28]
Peter Laursen
What about this statement: "ALTER USER 'user'@'host' IDENTIFIED WITH caching_sha2_password;" ? This worked for me with a user who before executing the statements used "mysql_native_password" plugin. Now - after executing the above statement - the user uses "caching_sha2_password". Related docs https://dev.mysql.com/doc/refman/8.0/en/alter-user.html, where you should look for this auth_option: { .. | IDENTIFIED WITH auth_plugin .. } -- Peter -- not a MySQL/Oracle person
[17 Jun 2019 15:00]
IGG t
The only problem with that, is that if you don't specify the password it wipes out the password and expires the login (would be catastrophic if I was trying to migrate some of my application / web logins), but if it is a user login, I can't simply expire them (as they may be working off site and need to maintain access), but as I don't know the password, it could take weeks to migrate everyone to MySQL 8.0. But I think it's probably the only option I'm going to get.
[18 Jun 2019 16:26]
MySQL Verification Team
Hi, I am afraid that the only option that we support is the one recommended by my colleague Peter Laursen. Thank you for your interest in our product.
Description: When trying to migrate from MySQL 5.7 to MySQL 8.0 I discovered that there doesn't appear to be a way to convert users from using the old `mysql_native_password` plugin to the newer `caching_sha2_password` plugin without first removing the user and re-creating them. How to repeat: On MySQL 5.7 get an existing users details using SHOW CREATE USER 'username'@'1.2.3.4'; This gives me the following: CREATE USER 'username'@'1.2.3.4' IDENTIFIED WITH 'mysql_native_password' AS '*1BDEB0AF67D8237DF5EFE4927F134126B3B9F2C6B' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; I then run this on MySQL 8.0 to create the user. SELECT user, host, plugin FROM mysql.user; +----------+------------+-----------------------+ | user | host | plugin | +----------+------------+-----------------------+ | username | 1.2.3.4 | mysql_native_password | +----------+------------+-----------------------+ But there is no way to convert the plugin without dropping and re-creating the user, this simply isn't practical in a live migration, where there may be many users that need to be migrated, who are all in constant use. Suggested fix: Set up an option to allow live migration of users from one login plugin to the other.