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