Description:
Upgraded MySQL setups often have existing users with mysql_native_password passwords.
Some users want to upgrade the password encryption.
The documentation ("3.5 Changes in MySQL 8.0") says, that
"Existing users who wish to switch to caching_sha2_password can do so using the ALTER USER statement:
ALTER USER user IDENTIFIED WITH caching_sha2_password BY 'password';"
This is not possible for "none-admin" users. The error message is:
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
How to repeat:
1) Create a user "testuser" with a mysql_native_password password
CREATE USER testuser@'%' IDENTIFIED WITH 'mysql_native_password' BY 'password';
2) Login as user testuser
3) Try to change the password and the password encryption:
alter user current_user() identified with caching_sha2_password by 'newpassword';
Suggested fix:
Allow users to change their password encryption.
At least changing passwords to the new default_authentication_plugin encryption should by possible without the "CREATE USER" privilege.
Two alternatives:
1) Change the password encryption automatically to the
'default_authentication_plugin' encryption with
alter user current_user() identified by 'newpassword';
2) Add an additional option or privilege
Description: Upgraded MySQL setups often have existing users with mysql_native_password passwords. Some users want to upgrade the password encryption. The documentation ("3.5 Changes in MySQL 8.0") says, that "Existing users who wish to switch to caching_sha2_password can do so using the ALTER USER statement: ALTER USER user IDENTIFIED WITH caching_sha2_password BY 'password';" This is not possible for "none-admin" users. The error message is: ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation How to repeat: 1) Create a user "testuser" with a mysql_native_password password CREATE USER testuser@'%' IDENTIFIED WITH 'mysql_native_password' BY 'password'; 2) Login as user testuser 3) Try to change the password and the password encryption: alter user current_user() identified with caching_sha2_password by 'newpassword'; Suggested fix: Allow users to change their password encryption. At least changing passwords to the new default_authentication_plugin encryption should by possible without the "CREATE USER" privilege. Two alternatives: 1) Change the password encryption automatically to the 'default_authentication_plugin' encryption with alter user current_user() identified by 'newpassword'; 2) Add an additional option or privilege