Bug #76048 IDENTIFIED BY PASSWORD is not deprecated for alter user but doesn't work
Submitted: 25 Feb 2015 12:52 Modified: 19 Mar 2015 16:12
Reporter: Ramana Yeruva Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.7.6 OS:Any
Assigned to: CPU Architecture:Any

[25 Feb 2015 12:52] Ramana Yeruva
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)
[19 Mar 2015 16:12] Paul Dubois
Noted in 5.7.7, 5.8.0 changelogs.

The deprecated IDENTIFIED BY PASSWORD syntax is supported only for
CREATE USER and GRANT, but ALTER USER failed to reject it. ALTER USER
now produces a syntax error.