Bug #107233 update mysql.user to modify user password no effect
Submitted: 7 May 2022 1:10 Modified: 7 May 2022 20:47
Reporter: link link Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.6.25 OS:Any
Assigned to: CPU Architecture:Any

[7 May 2022 1:10] link link
Description:

step1:

mysql -h10.200.210.66  -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 568668
Server version: 5.6.25-log Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

SELECT user, host,authentication_string from mysql.user;
| archery | %              | *6A15CC80FB2C1CFADFB676FF1DA064801BA34045 |

step2:
update mysql.user set authentication_string=password("Cr66@") where User="archery" and Host="%";
flush privileges;

step3:
mysql -h10.200.210.66  -uarchery -p'Cr66@'
it report error:
ERROR 1045 (28000): Access denied for user 'archery'@'10.200.210.66' (using password: YES)

How to repeat:
just happend in our db ,
[7 May 2022 20:47] MySQL Verification Team
Thank you for the bug report. I couldn't repeat behavior of quite older version  5.6.25 and EOL version too:

mysql> update mysql.user set authentication_string=password("Cr66@") where User="archery" and Host="%";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> sELECT user, host,authentication_string from mysql.user;
+---------+-----------+-------------------------------------------+
| user    | host      | authentication_string                     |
+---------+-----------+-------------------------------------------+
| root    | localhost |                                           |
| root    | 127.0.0.1 |                                           |
| root    | ::1       |                                           |
|         | localhost | NULL                                      |
| archery | %         | *E350AA6BE5E92584862793A4EA316802CF1C7FD8 |
+---------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> show variables like "%version";
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| innodb_version   | 5.6.51     |
| protocol_version | 10         |
| version          | 5.6.51-log |
+------------------+------------+
3 rows in set (0.00 sec)

https://www.mysql.com/support/eol-notice.html

"MySQL 5.6 is covered under Oracle Lifetime Sustaining Support

Per Oracle's Lifetime Support policy, as of February 1, 2021, MySQL 5.6 is covered under Oracle Sustaining Support.

Users are encouraged to upgrade to MySQL 8.0."