MySQL Bugs: #114938: MySQL 8.0.32 alter user with mysql_native

Bug #114938	MySQL 8.0.32 alter user with mysql_native_password need CREATE USER privileges
Submitted:	9 May 8:08	Modified:	10 May 10:14
Reporter:	mars xu	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A user without the CREATE USER privilege encounters an error when executing the ALTER USER command with mysql_native_password to change the password in MySQL 8.0.32, stating that the CREATE USER privilege is required. However, when not using the mysql_native_password keyword, the user can change their own password. I believe that regardless of whether the mysql_native_password keyword is used or not, users should be allowed to change their own passwords.

How to repeat:
create user `user_a`@'%' identified by 'password';

grant select on test.* to `user_a`@'%';

login by user_a:
alter user `user_a`@'%' identified by 'newPassword';
# success

alter user `user_a`@'%' identified with mysql_native_password by 'newPassword';
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

authentication_policy = mysql_native_password
default_authentication_plugin = mysql_native_password

Hi MR. xu,

Thank you for your bug report.

However, mysql_native_password option is no longer supported and that plugin is no longer available in the current versions of 8.0.

Our Reference Manual clearly states the following:

As of MySQL 8.0.34, the mysql_native_password authentication plugin is deprecated and subject to removal in a future version of MySQL.

on this page:

https://dev.mysql.com/doc/refman/8.0/en/native-pluggable-authentication.html#native-plugga...

Unsupported.

First of all, thank you for your response. 

Although I'm using mysql_native_password here, the actual issue arises when using the ALTER USER xxx WITH xxx BY syntax, which requires the user to have the CREATE USER privilege. For example, even if the statement is changed to ALTER USER 'user_a'@'%' IDENTIFIED WITH caching_sha2_password BY 'newPassword', you'll still encounter ERROR 1227. Is this reasonable?

Hi Mr. xu,

This sounds very interesting.

Can you send us a full and repeatable test case with caching_sha2_password ????

Thanks in advance.

Hi Mr. xu,

Thank you for your bug report.

We made our own test case and, indeed, this is a bug, because even when using caching_sha2_password, you still get an error.

This is totally insignificant bug, but still a bug, which is affecting version 8.0 and higher.

Verified.

Thanks a lot !!!!!!

Hi,

We had a discussion with a Development team.

Any IDENTIFIED WITH will require CREATE / ALTER privileges, since the default authentication method would be revealed to the user who does not have administrative privileges.

https://dev.mysql.com/doc/refman/8.0/en/alter-user.html:

The above page will be appended to specify then any use of IDENTIFIED WITH, regardless of the authentication plugin, will require special privileges.

So, it is still a bug, but a bug in the documentation.