| 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: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.21-community-nt | OS: | Windows (win32 - XP SP2) |
| Assigned to: | Tatiana Azundris Nuernberg | CPU Architecture: | Any |
[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)
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)