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)
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)