Bug #36742 GRANT hostname case handling inconsistent
Submitted: 15 May 2008 20:10 Modified: 16 Nov 2010 3:44
Reporter: Jeremy Hough Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.1.24-rc, 5.0, 5.1, 6.0 BK OS:Any
Assigned to: Dmitry Shulga
Triage: Triaged: D2 (Serious)

[15 May 2008 20:10] Jeremy Hough
Description:
The handling of case in the hostname for grant statements is inconsistent. Because of the inconsistent handling, you can accidentally create new users and cause authentication problems.

See below for an example of the inconsistent handling. Note "Localhost" vs "localhost" in both the statements and the output. This inconsistent case handling occurs in both 5.1 and 5.0.

I'm not certain how to reproduce the authentication issue. I've only run across it in 5.0.22, so it may or may not be moot in later versions. On 5.0.22, mysql seems to sometimes prefer the new user over the old one causing authentication failures when the new user has no password and the old user does have a password. The preference seems to change over time (immediately after the new user is accidentally created, it may still prefer the old user, but after a period of time--perhaps days--it may prefer the new user).

How to repeat:
mysql> grant usage on Foo.* to myuser@Localhost identified by 'foo';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for myuser@localhost;
+-------------------------------------------------------------------------------
--------------------------------+
| Grants for myuser@localhost
                                |
+-------------------------------------------------------------------------------
--------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'localhost' IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' |
+-------------------------------------------------------------------------------
--------------------------------+
1 row in set (0.01 sec)

mysql> grant select on Foo.* to myuser@localhost;
Query OK, 0 rows affected (0.02 sec)

mysql> select host,user,password from mysql.user where User='myuser';
+-----------+--------+-------------------------------------------+
| host      | user   | password                                  |
+-----------+--------+-------------------------------------------+
| Localhost | myuser | *F3A2A51A9B0F2BE2468926B4132313728C250DBF |
| localhost | myuser |                                           |
+-----------+--------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for myuser@localhost;
+-------------------------------------------------------------------------------
--------------------------------+
| Grants for myuser@localhost
                                |
+-------------------------------------------------------------------------------
--------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'localhost' IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' |
| GRANT SELECT ON `Foo`.* TO 'myuser'@'localhost'
                                |
+-------------------------------------------------------------------------------
--------------------------------+
2 rows in set (0.01 sec)

Suggested fix:
Make the hostname either case sensitive or not. If it is case sensitive, it would help if "show grants for <user>" would output the correct case.
[17 May 2008 17:08] Sveta Smirnova
Thank you for the report.

Verified as described.
[14 Sep 2010 7:33] Konstantin Osipov
Agreed with Dmitry to lowercase the hostname part of the user name before storage and before access check.
[27 Sep 2010 10:34] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/119155

3498 Dmitry Shulga	2010-09-27
      Fixed bug#36742 - GRANT hostname case handling inconsistent.
     @ sql/mysql_priv.h
        It was added declaration for host_to_lowercase.
     @ sql/sql_parse.cc
        It was added standalone procedure host_to_lowercase.
     @ sql/sql_yacc.yy
        It was added convertation of host name part of username to lowercase.
[27 Sep 2010 10:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/119159

3498 Dmitry Shulga	2010-09-27
      Fixed bug#36742 - GRANT hostname case handling inconsistent.
     @ sql/sql_yacc.yy
        It was added convertation of host name part of user name to lowercase.
[28 Sep 2010 10:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/119247

3498 Dmitry Shulga	2010-09-28
      Fixed bug#36742 - GRANT hostname case handling inconsistent.
     @ mysql-test/r/grant.result
        It was added result for test case for bug#36742.
     @ mysql-test/t/grant.test
        It was added test case for bug#36742.
     @ sql/sql_yacc.yy
        It was added convertation of host name part of user name to lowercase.
[29 Sep 2010 13:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/119414

3498 Dmitry Shulga	2010-09-29
      Fixed bug#36742 - GRANT hostname case handling inconsistent.
     @ mysql-test/r/grant.result
        It was added result for test case for bug#36742.
     @ mysql-test/t/grant.test
        It was added test case for bug#36742.
     @ sql/sql_yacc.yy
        It was added convertation of host name part of user name to lowercase.
[13 Oct 2010 5:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/120614

3527 Dmitry Shulga	2010-10-13
      Fixed bug#36742 - GRANT hostname case handling inconsistent.
     @ mysql-test/r/grant.result
        It was added result for test case for bug#36742.
     @ mysql-test/t/grant.test
        It was added test case for bug#36742.
     @ sql/sql_yacc.yy
        It was added convertation of host name part of user name to lowercase.
[13 Oct 2010 6:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/120617

3239 Dmitry Shulga	2010-10-13 [merge]
      Auto-merge from mysql-5.1-bugteam for bug#36742.
[14 Oct 2010 14:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/120777

3528 Dmitry Shulga	2010-10-14
      Follow up for bug#36742.
     @ mysql-test/t/grant3.test
        Removed test case for bug#19828 because of currently hostname
        stored in db in lowercase.
[14 Oct 2010 14:38] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/120779

3530 Dmitry Shulga	2010-10-14
      Follow up for bug#36742. Removed test case for bug#19828
      because of currently hostname stored in db in lowercase.
[18 Oct 2010 14:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/120978

3530 Dmitry Shulga	2010-10-18
      Follow up for bug#36742. Changed test case for bug#19828
      because currently hostname stored in db in lowercase.
[18 Oct 2010 14:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/120979

3532 Dmitry Shulga	2010-10-18
      Follow up for bug#36742. Changed test case for bug#19828
      because currently hostname stored in db in lowercase.
[18 Oct 2010 15:40] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/120992

3248 Dmitry Shulga	2010-10-18 [merge]
      Auto-merge from mysql-5.1-bugteam for bug#36742.
[18 Oct 2010 16:21] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/120998

3249 Dmitry Shulga	2010-10-18
      Follow-up for bug#36742: changed results for test ipv4_as_ipv6 because hostname is case-insensitive.
[18 Oct 2010 16:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/121000

3274 Dmitry Shulga	2010-10-18 [merge]
      Auto-merge from mysql-5.5-bugteam for bug#36742.
[13 Nov 2010 16:17] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:30] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
[16 Nov 2010 3:44] Paul Dubois
Noted in 5.1.53, 5.5.8 changelogs.

Handling of host name lettercase in GRANT statements was
inconsistent.
[18 Nov 2010 15:56] Bugs System
Pushed into mysql-5.1 5.1.54 (revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (version source revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (merge vers: 5.1.54) (pib:21)
[16 Dec 2010 22:27] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)