Bug #76018 unable to login with user whose pwd changed from 5.6.23 mysqladmin in 5.7.6
Description:
start 5.7.6 server(either commercial/community) by creating database with --initialize-insecure
login with root user and create user and make sure that newly created user able to login
now change newly created user password using 5.6.23-advanced-mysqladmin and try to login with changed password
observe it is failing.
How to repeat:
./mysqld -uroot --initialize-insecure --basedir=../ --datadir=./data
./mysqld -uroot --basedir=../ --datadir=./data --gdb &
[root@vitro77 bin]# ./mysql -uroot --socket=/tmp/mysql.sock
mysql> select user,host,plugin,authentication_string from mysql.user;
+------+-----------+-----------------------+-----------------------+
| user | host | plugin | authentication_string |
+------+-----------+-----------------------+-----------------------+
| root | localhost | mysql_native_password | |
+------+-----------+-----------------------+-----------------------+
1 row in set (0.00 sec)
mysql> create user ramana@localhost identified by 'ramana';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,plugin,authentication_string from mysql.user;
+--------+-----------+-----------------------+-------------------------------------------+
| user | host | plugin | authentication_string |
+--------+-----------+-----------------------+-------------------------------------------+
| root | localhost | mysql_native_password | |
| ramana | localhost | mysql_native_password | *E0AC89936D38F80901D7A39137CD222F38050BF1 |<--Note this is hash for password 'ramana'
+--------+-----------+-----------------------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> exit
Bye
[root@vitro77 bin]# ./mysql -uramana --socket=/tmp/mysql.sock -pramana<--making sure that newly created user able to login
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.6-m16-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, 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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit
Bye
[root@vitro77 bin]# ../../mysqladmin_adv_5.6.23 -uramana -pramana password ramana --socket=/tmp/mysql.sock<--here there is no error so that means password changed successfuly
[root@vitro77 bin]# ./mysql -uramana --socket=/tmp/mysql.sock -pramana<--observe that login failed with changed password from mysqladmin as above
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'ramana'@'localhost' (using password: YES)
Warning: Using a password on the command line interface can be insecure.
[root@vitro77 bin]# ./mysql -uroot --socket=/tmp/mysql.sock<--lets observe the hash of password changed by mysqladmin as above which is different that that of original
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.6-m16-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, 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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user,host,plugin,authentication_string from mysql.user;
+--------+-----------+-----------------------+-------------------------------------------+
| user | host | plugin | authentication_string |
+--------+-----------+-----------------------+-------------------------------------------+
| root | localhost | mysql_native_password | |
| ramana | localhost | mysql_native_password | *9025696EE4B0C07448717789F07426478CE55D6A |
+--------+-----------+-----------------------+-------------------------------------------+
2 rows in set (0.00 sec)
Suggested fix:
Since 5.6.23 mysqladmin client is allowing to change password and no error reported by server during password change,login should be failed
OR
server should throw error for this operation.
Now it is in the situation that, seems like password is changed but unable to login
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.