Bug #69899 GRANT fails to set empty password for existing user

• Submitted: 1 Aug 2013 15:18
• Modified: 20 Nov 2013 14:40
• Reporter: Leandro Morgado
• Status: Closed
• Category: MySQL Server: Security: Privileges
• Severity: S3 (Non-critical)
• Version: 5.6
• OS: Any
• CPU Architecture: Any

[1 Aug 2013 15:18] Leandro Morgado

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

[1 Aug 2013 15:25] Leandro Morgado

Relevant section of the manual
===================
The user specification may indicate how the user should authenticate when connecting to the server, through inclusion of an IDENTIFIED BY or IDENTIFIED WITH clause. The syntax is the same as for the CREATE USER statement. See Section 13.7.1.2, “CREATE USER Syntax”.

When the IDENTIFIED BY clause is present and you have global grant privileges, the password becomes the new password for the account, even if the account exists and already has a password. With no IDENTIFIED BY clause, the account password remains unchanged.

If the account named in a GRANT statement does not exist in the mysql.user table, GRANT creates it if the NO_AUTO_CREATE_USER SQL mode is not enabled. This is very insecure unless you specify a nonempty password using IDENTIFIED BY or an authentication plugin using IDENTIFIED WITH.

If the account does not exist and NO_AUTO_CREATE_USER is enabled, GRANT fails and does not create the account unless you specify a nonempty password with IDENTIFIED BY or specify an IDENTIFIED WITH clause to name an authentication plugin.
  http://dev.mysql.com/doc/refman/5.6/en/grant.html
===================

[5 Aug 2013 11:13] Leandro Morgado

Small correction, where it says:

"even after a FLUSH TABLES (which GRANT does not need)."

It should read FLUSH PRIVILEGES instead of TABLES.

[26 Sep 2013 13:05] Kristian Koehntopp

This behavior may have been introduced with MySQL 5.6.3.

See https://plus.google.com/u/0/+KristianK%C3%B6hntopp/posts/NHpFgngs2tx, http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_log-raw and see if the problem occurs with --log-raw as well.

If you get previous behavior with --log-raw, the faulty code is in sql_rewrite.cc.

[26 Sep 2013 15:40] Leandro Morgado

>This behavior may have been introduced with MySQL 5.6.3.
>
GRANT allowing setting of empty password was tested and working on 5.6.5-m8 but failing in 5.6.6-m9 (see above in How to repeat). 
Most likely bug was not introduced in  5.6.3.

[20 Nov 2013 14:40] Paul DuBois

Noted in 5.7.4 changelog.

For an existing user, GRANT with an empty password (IDENTIFIED BY
[PASSWORD] '') did not change the password.

[24 Dec 2013 15:04] Simon Mudd

Fixed in 5.7 but not in 5.6? That seems strange.