MySQL Bugs: #118957: Documentation says you can use a HEX value for ALTER user passwords, but you can't.

Bug #118957	Documentation says you can use a HEX value for ALTER user passwords, but you can't.
Submitted:	8 Sep 12:24	Modified:	8 Sep 15:18
Reporter:	IGG t	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0.35	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
According to the documentation:

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

"As of MySQL 8.0.17, a hashed string can be either a string literal or a hexadecimal value. The latter corresponds to the type of value displayed by SHOW CREATE USER for password hashes containing unprintable characters when the print_identified_with_as_hex system variable is enabled."

However, when I try to run:

ALTER USER 'user1'@'%' IDENTIFIED BY 0x2441243032352433644D30740A56593B2379636B5F5C1B49523D643639685032426D613943477831734D46654B3831427A704C6B755A766B30596C695A93687A44746E433643;

I get the following response:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0x2441243032352433644D30740A56593B2379636B5F5C1B49523D643639685032426D6139434778' at line 1

How to repeat:
On DB1 create a new user:

CREATE USER `user1`@`%` IDENTIFIED WITH 'caching_sha2_password' BY 'password'. . . 

Then select the password hash is hex format:

set @@session.print_identified_with_as_hex = 1;
show create user 'user1'@'%';
 
Copy the password hash, and on DB2 run:

ALTER USER 'user1'@'%' IDENTIFIED BY 0x2441243032352433644D30740A56593B2379636B5F5C1B49523D643639685032426D613943477831734D46654B3831427A704C6B755A766B30596C695A93687A44746E433643;

This fails with error 1064.

Changing it to a regular string works fine:

ALTER USER 'user1'@'%' IDENTIFIED BY 'password';

Suggested fix:
Either the documentation needs updating to say that you "can't" use "ALTER USER" with a password hex value, or it is a bug. Ideally, it should be fixed as I regularly need to update user passwords across multiple databases, and historically I would just copy the password hash. But with caching_sha2_password this doesn't work. 

As it stands, the only option I have (apart from asking the user for their password) is to drop the user and re-create it with the new hex password. 

This is a pain when you have to do this on multiple databases, with different sets of permissions on each.

The syntax is different for the hex version.  IDENTIFIED BY    vs  IDENTIFIED WITH .. AS ..

See my example and let us know there's any doubt.

mysql> create user myself@'%' identified by '12345';
Query OK, 0 rows affected (0.02 sec)

mysql> show create user myself \G
*************************** 1. row ***************************
CREATE USER for myself@%: CREATE USER `myself`@`%` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$O\'cK#[\\q^BJ%/ar^pKAoeGHQGadZBwM/P3xGZq5XNIuxeB6HHMNJOyOfIF.' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.00 sec)

mysql> set @@session.print_identified_with_as_hex = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> show create user myself \G
*************************** 1. row ***************************
CREATE USER for myself@%: CREATE USER `myself`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035244F1327634B235B5C715E02424A4C08252F61725E704B416F654748514761645A42774D2F503378475A7135584E49757865423648484D4E4A4F794F6649462E REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.00 sec)

mysql> drop user myself@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER `myself`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035244F1327634B235B5C715E02424A4C08252F61725E704B416F654748514761645A42774D2F503378475A7135584E49757865423648484D4E4A4F794F6649462E REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
Query OK, 0 rows affected (0.00 sec)

IDENTIFIED BY takes a plain text string for the password, not a raw hex hash value.

It also appears you must include the "WITH CACHING_SHA2_PASSWORD"

e.g. 
ALTER USER `user1`@`%` IDENTIFIED AS 0x2441243030352433644D30770A56593B23. . . 

Doesn't work, it has to be:

ALTER USER `user1`@`%` IDENTIFIED WITH CACHING_SHA2_PASSWORD AS 0x2441243030352433644D30770A56593B23. . .