Bug #76048 IDENTIFIED BY PASSWORD is not deprecated for alter user but doesn't work
Description:
create user with identified by password clause and observe deprecation warnings
alter this user with identified by clause and set new password,observe that it is successful but it is not reflected in authentication_string and unable to login with newly set password.
How to repeat:
[lroot@vitro77 bin]# ./mysql -uroot --port=3307 --socket=/tmp/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
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 | |
+------+-----------+-----------------------+-----------------------+
1 row in set (0.00 sec)
mysql> create user ramana@localhost identified by password '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1287
Message: 'IDENTIFIED BY PASSWORD' is deprecated and will be removed in a future release. Please use IDENTIFIED WITH <plugin> AS <hash> instead
1 row in set (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 | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+--------+-----------+-----------------------+-------------------------------------------+
2 rows in set (0.00 sec)
[lroot@vitro77 bin]# ./mysql -uramana -ptest --port=3307 --socket=/tmp/mysql.sock<--see connection is successful
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 19
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
[lroot@vitro77 bin]# ./mysql -uroot --port=3307 --socket=/tmp/mysql.sock
mysql> select password('ramana');
+-------------------------------------------+
| password('ramana') |
+-------------------------------------------+
| *E0AC89936D38F80901D7A39137CD222F38050BF1 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> alter user ramana@localhost identified by password '*E0AC89936D38F80901D7A39137CD222F38050BF1';<--this statement is success without any warnings\errors
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,plugin,authentication_string from mysql.user;<--but hash in authentication_string for this user not changed with newly set one as above
+--------+-----------+-----------------------+-------------------------------------------+
| user | host | plugin | authentication_string |
+--------+-----------+-----------------------+-------------------------------------------+
| root | localhost | mysql_native_password | |
| ramana | localhost | mysql_native_password | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+--------+-----------+-----------------------+-------------------------------------------+
2 rows in set (0.00 sec)
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.042 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.
Description: create user with identified by password clause and observe deprecation warnings alter this user with identified by clause and set new password,observe that it is successful but it is not reflected in authentication_string and unable to login with newly set password. How to repeat: [lroot@vitro77 bin]# ./mysql -uroot --port=3307 --socket=/tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 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 | | +------+-----------+-----------------------+-----------------------+ 1 row in set (0.00 sec) mysql> create user ramana@localhost identified by password '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1287 Message: 'IDENTIFIED BY PASSWORD' is deprecated and will be removed in a future release. Please use IDENTIFIED WITH <plugin> AS <hash> instead 1 row in set (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 | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | +--------+-----------+-----------------------+-------------------------------------------+ 2 rows in set (0.00 sec) [lroot@vitro77 bin]# ./mysql -uramana -ptest --port=3307 --socket=/tmp/mysql.sock<--see connection is successful 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 19 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 [lroot@vitro77 bin]# ./mysql -uroot --port=3307 --socket=/tmp/mysql.sock mysql> select password('ramana'); +-------------------------------------------+ | password('ramana') | +-------------------------------------------+ | *E0AC89936D38F80901D7A39137CD222F38050BF1 | +-------------------------------------------+ 1 row in set (0.00 sec) mysql> alter user ramana@localhost identified by password '*E0AC89936D38F80901D7A39137CD222F38050BF1';<--this statement is success without any warnings\errors Query OK, 0 rows affected (0.00 sec) mysql> select user,host,plugin,authentication_string from mysql.user;<--but hash in authentication_string for this user not changed with newly set one as above +--------+-----------+-----------------------+-------------------------------------------+ | user | host | plugin | authentication_string | +--------+-----------+-----------------------+-------------------------------------------+ | root | localhost | mysql_native_password | | | ramana | localhost | mysql_native_password | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | +--------+-----------+-----------------------+-------------------------------------------+ 2 rows in set (0.00 sec)