Description:
One method to set a new password for an existing user is to use this syntax:
GRANT USAGE ON *.* TO 'userfoo'@'10.%' IDENTIFIED BY 'secret password';
And up until 5.6.6, this worked fine with an empty string for the password, eg:
GRANT USAGE ON *.* TO 'userfoo'@'10.%' IDENTIFIED BY '';
Starting with 5.6.6, when setting an empty password with GRANT, the query runs successfully, does not return a warning or error, yet never changes the password, even after a FLUSH TABLES (which GRANT does not need).
Note that the GRANT is applied to an existing user so the NO_AUTO_CREATE_USER sql_mode should not apply to this case.
PS - Yes, there are alternative (and I'd say more conventional) methods to change the password but there is legacy code depending on this behaviour.
How to repeat:
===============================
5.6.6 and above does not set the empty password
Note: Also tested 5.6.10, 5.6.12 with same results
===============================
Server version: 5.6.6-m9-log MySQL Community Server (GPL)
Copyright (c) 2000, 2012, 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 [localhost] {root} ((none)) > SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode\G
*************************** 1. row ***************************
@@GLOBAL.sql_mode: NO_ENGINE_SUBSTITUTION
@@SESSION.sql_mode: NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)
mysql [localhost] {root} ((none)) > SELECT User,Host,Password FROM mysql.user WHERE User='userfoo';
Empty set (0.00 sec)
mysql [localhost] {root} ((none)) > GRANT USAGE ON *.* TO 'userfoo'@'10.%' IDENTIFIED BY '';
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {root} ((none)) > SELECT User,Host,Password FROM mysql.user WHERE User='userfoo';
+---------+------+----------+
| User | Host | Password |
+---------+------+----------+
| userfoo | 10.% | |
+---------+------+----------+
1 row in set (0.00 sec)
mysql [localhost] {root} ((none)) > GRANT USAGE ON *.* TO 'userfoo'@'10.%' IDENTIFIED BY 'foo';
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {root} ((none)) > SELECT User,Host,Password FROM mysql.user WHERE User='userfoo';
+---------+------+-------------------------------------------+
| User | Host | Password |
+---------+------+-------------------------------------------+
| userfoo | 10.% | *F3A2A51A9B0F2BE2468926B4132313728C250DBF |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost] {root} ((none)) > GRANT USAGE ON *.* TO 'userfoo'@'10.%' IDENTIFIED BY '';
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {root} ((none)) > SELECT User,Host,Password FROM mysql.user WHERE User='userfoo';
+---------+------+-------------------------------------------+
| User | Host | Password |
+---------+------+-------------------------------------------+
| userfoo | 10.% | *F3A2A51A9B0F2BE2468926B4132313728C250DBF |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
============
===============================
5.6.5, 5.5.x and below sets the empty password
Note: Also tested 5.5.23, 5.5.32, 5.1.63 with same results
===============================
Server version: 5.6.5-m8 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, 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 [localhost] {root} ((none)) > 5.6.6 and above does not set the empty password
-> ===============================\c
mysql [localhost] {root} ((none)) > SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode\G
*************************** 1. row ***************************
@@GLOBAL.sql_mode:
@@SESSION.sql_mode:
1 row in set (0.00 sec)
mysql [localhost] {root} ((none)) > SELECT User,Host,Password FROM mysql.user WHERE User='userfoo';
+---------+------+----------+
| User | Host | Password |
+---------+------+----------+
| userfoo | 10.% | |
+---------+------+----------+
1 row in set (0.01 sec)
mysql [localhost] {root} ((none)) > GRANT USAGE ON *.* TO 'userfoo'@'10.%' IDENTIFIED BY '';
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {root} ((none)) > SELECT User,Host,Password FROM mysql.user WHERE User='userfoo';
+---------+------+----------+
| User | Host | Password |
+---------+------+----------+
| userfoo | 10.% | |
+---------+------+----------+
1 row in set (0.00 sec)
mysql [localhost] {root} ((none)) > GRANT USAGE ON *.* TO 'userfoo'@'10.%' IDENTIFIED BY 'foo';
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {root} ((none)) > SELECT User,Host,Password FROM mysql.user WHERE User='userfoo';
+---------+------+-------------------------------------------+
| User | Host | Password |
+---------+------+-------------------------------------------+
| userfoo | 10.% | *F3A2A51A9B0F2BE2468926B4132313728C250DBF |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost] {root} ((none)) > GRANT USAGE ON *.* TO 'userfoo'@'10.%' IDENTIFIED BY '';
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {root} ((none)) > SELECT User,Host,Password FROM mysql.user WHERE User='userfoo';
+---------+------+----------+
| User | Host | Password |
+---------+------+----------+
| userfoo | 10.% | |
+---------+------+----------+
1 row in set (0.00 sec)
Suggested fix:
- Make the behaviour consistent between 5.1, 5.5 and 5.6 GA releases, that is, allow GRANT to set empty passwords for existing users.
- If behaviour in 5.6GA can not be made consistent with older releases, document this in the manual, for example, here:
http://dev.mysql.com/doc/refman/5.6/en/grant.html