Bug #19828 Case sensitivity in hostname leads to inconsistent behavior
Submitted: 15 May 2006 19:04 Modified: 17 Nov 2010 1:19
Reporter: Erica Moss Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.21-community-nt OS:Microsoft Windows (win32 - XP SP2)
Assigned to: Tatiana Azundris Nuernberg

[15 May 2006 19:04] Erica Moss
Description:
The fundamental question to be asked here is whether there is a good reason for the system to distinguish between upper/lower case in a hostname, or whether all occurrences of hostname should be canonicalized before storage or display.  I know of no system where such a distinction needs to be made, but there may be some.

If it is in fact necessary that we continue to make this distinction, then it is necessary to make all commands do so uniformly.  As the script below demonstrates, some commands distinguish, while others do not to varying degrees.

How to repeat:
### TEST SCRIPT

GRANT ALTER ON test.* TO
           'alter-all'@'localhost' IDENTIFIED BY 'alter-all';
GRANT ALTER ON test.* TO
          'alter-all'@'LOCALHOST' IDENTIFIED BY 'alter-all';
SELECT user, host FROM user;
SELECT user, host, db, alter_priv FROM db;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'alter-all'@'LOCALHOST';
SELECT user, host, db, alter_priv FROM db;
SHOW GRANTS FOR 'alter-all'@'LOCALHOST';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'alter-all'@'localhost';
SELECT user, host, db, alter_priv FROM db;
DROP USER 'alter-all'@localhost , 'alter-all'@LOCALHOST;

##### ANNOTATED OUTPUT:

### GRANT IS HAPPY TO RUN BOTH WAYS AND MAKE TWO DIFFERENT RECORDS
### IN THE USER TABLE
mysql> GRANT ALTER ON test.* TO
    ->       'alter-all'@'localhost' IDENTIFIED BY 'alter-all';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALTER ON test.* TO
    ->      'alter-all'@'LOCALHOST' IDENTIFIED BY 'alter-all';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user, host FROM user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+  ### Is this logical from a network viewpoint? 
| alter-all | LOCALHOST |
| alter-all | localhost |
| root      | localhost |
+-----------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT user, host, db, alter_priv FROM db;
+-----------+-----------+------+------------+
| user      | host      | db   | alter_priv |
+-----------+-----------+------+------------+
| alter-all | LOCALHOST | test | Y          |
| alter-all | localhost | test | Y          |
+-----------+-----------+------+------------+
2 rows in set (0.00 sec)

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'alter-all'@'LOCALHOST';
ERROR 1141 (42000): There is no such grant defined for user 'alter-all' on host 'LOCALHOST'

### REVOKE seems to understand that something is amiss and barks at you
### but it does what you ask anyway...

mysql> SELECT user, host, db, alter_priv FROM db
+-----------+-----------+------+------------+
| user      | host      | db   | alter_priv |
+-----------+-----------+------+------------+
| alter-all | localhost | test | Y          |
+-----------+-----------+------+------------+
1 row in set (0.00 sec)

### SHOW GRANTS however, doesn't seem to understand
### the difference between upper/lower case. 

mysql> SHOW GRANTS FOR 'alter-all'@'LOCALHOST';
+--------------------------------------------
| Grants for alter-all@LOCALHOST
|
| GRANT USAGE ON *.* TO 'alter-all'@'LOCALHOST' IDENTIFIED BY...
|
| GRANT ALTER ON `test`.* TO 'alter-all'@'LOCALHOST'
2 rows in set (0.00 sec)

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'alter-all'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user, host, db, alter_priv FROM db;
Empty set (0.00 sec)

mysql> SELECT user, host FROM user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| alter-all | LOCALHOST |
| alter-all | localhost |
| root      | localhost |
+-----------+-----------+
3 rows in set (0.02 sec)

DROP USER 'alter-all'@localhost , 'alter-all'@LOCALHOST;
Query OK, 0 rows affected (0.00 sec)
[30 May 2007 20:53] Martin Friebe
patch to fix revoke (revoke will be case sensitive)

Attachment: grant_case.patch (text/x-patch), 10.63 KiB.

[30 May 2007 20:54] Martin Friebe
proposal to make "show grants" display the actual case of hostnames

Attachment: grant_case_show.patch (text/x-patch), 3.61 KiB.

[30 May 2007 21:02] Martin Friebe
revoke failed, because the memory operation would find all different cases of a hostname (applies to db, table, column...; BUT NOT to global privileges).
It would then attempt for each found entry to remove it, using the user-specified case. This means that
  REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'alter-all'@'LOCALHOST'
would attempt to revoke the priveleges twice on the uppercase LOCALHOST.

In addition I noted, that if hostname appear in multiply cases, privileges of all spellings are available to the user (which is fine, as the doc says comparisation is not case sensitive).

With one EXCEPTION: global-privileges. 
Even if multiply entries are available for the user, only one will be used. (this is similiar like the behaviour that applies, if several wildcarded hostnames would match)

This is not consistent, as this behaviour only applies to global priveleges, and db, table, column are handled different.

So there may still be a case to change the code in future mysql versions, and normalize hostnames to all lowercase.

The 2nd patch is a proposal:
Show grants (for db, table, column grants) shows all entries for all spellings. (which is consistent with the fact that they are available to the user.)

The case of the hostname is taken from the specification of the user supplied sql query. It may be more sensible to display the actual case in the grant tables?
[18 Sep 2007 5:36] Tatiana Azundris Nuernberg
Patch looks good. (And comes with working test, Martin, you rock.)

I had half a heart to also change

replace_user_table()
    ...
    old_row_exists = 0;
+   // magic goes here (~ line 1810)
    restore_record(table,s->default_values);
    table->field[0]->store(combo.host.str,combo.host.length,
                           system_charset_info);
    ...

to include a test that will throw a NOTICE if host isn't lowercase,
but that may create more confusion than it's worth on the Windows
side?  In fact, if tackling that at all, the correct solution would
possibly to scan the ACLs for that host case-insensitive, and if any
entries exist where the case is different from that in the new entry,
throw a warning (only one, obviously :). Likely more hassle than it's
worth, though.
[18 Sep 2007 7: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/34362

ChangeSet@1.2476, 2007-09-18 09:46:18+02:00, tnurnberg@mysql.com +5 -0
  Bug#19828: Case sensitivity in hostname leads to inconsistent behavior
  
  clean up SHOW GRANTS so it will show host-names with case as entered.
  make REVOKE and friends case-sensitive to make things more intuitive.
  Patch by Martin Friebe.
[20 Sep 2007 17:58] 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/34446

ChangeSet@1.2476, 2007-09-20 18:10:35+02:00, tnurnberg@sin.intern.azundris.com +5 -0
  Bug#19828: Case sensitivity in hostname leads to inconsistent behavior
  
  clean up SHOW GRANTS so it will show host-names with case as entered.
  make REVOKE and friends case-sensitive to make things more intuitive.
  Patch by Martin Friebe.
[6 Oct 2007 0:48] Tatiana Azundris Nuernberg
pushed to 5.0.52, 5.1.23 maint
[18 Oct 2007 21:33] Bugs System
Pushed into 5.1.23-beta
[18 Oct 2007 21:36] Bugs System
Pushed into 5.0.52
[24 Oct 2007 20:13] Paul Dubois
Noted in 5.0.52, 5.1.23 changelogs.

Hostnames sometimes were treated as case sensitive in
account-management statements (CREATE USER, GRANT, REVOKE, and so
forth).
[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.
[13 Nov 2010 16:19] 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:38] 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)
[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:32] 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)