MySQL Bugs: #62255: DROP USER can't drop users with legacy upper case host name anymore

Bug #62255	DROP USER can't drop users with legacy upper case host name anymore
Submitted:	24 Aug 2011 21:41	Modified:	11 Nov 2013 17:13
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	>= 5.1.53	OS:	Any (Linux, MacOS X)
Assigned to:		CPU Architecture:	Any

Description:
As DROP USER lowercases the host name part of the user name now (see bug #36742) it is not possible to drop users with host names with upper case letters in them (either from pre-5.1.53 installations or due to bug #62254)

Such users either need to be removed by either removing them from the mysql.user table using DELETE or by first converting the host name part in mysql.user to all lower case using UPDATE and LOWER

(See also bug #61525)

How to repeat:
(assuming that bug #62254 has not been fixed yet)

on the shell do 

  hostname FOOBAR

in the mysql command line client

  SELECT User, Hostname FROM mysql.user WHERE Host = 'FOOBAR';

  DROP USER 'root'@'FOOBAR';

  SELECT User, Hostname FROM mysql.user WHERE Host = 'FOOBAR';

the DROP will report "0 rows affected" and both SELECTs will return the same result, proving that no user was deleted by the DROP statement

Suggested fix:
Make DROP USER really case insensitive regarding to the host name part

Thank you for the bug report. Verified with current mysql-5.5 from bzr on Mac OS X also:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.17 Source distribution

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> select user, host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | MAC       |
| root | MAC       |
|      | localhost |
| root | localhost |
+------+-----------+
6 rows in set (0.00 sec)

mysql> drop user ''@'MAC';
Query OK, 0 rows affected (0.00 sec)

mysql> drop user 'root'@'MAC';
Query OK, 0 rows affected (0.00 sec)

mysql> select user, host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | MAC       |
| root | MAC       |
|      | localhost |
| root | localhost |
+------+-----------+
6 rows in set (0.00 sec)

I just had the same problem on CentOS 6.2 with MySQL 5.5.21. Any ETA for a fix?

Minimal fix: let mysql_upgrade take care of legacy entries with upper case letters in hostname: 

--- scripts/mysql_system_tables_fix.sql	2012-01-13 14:50:02 +0000
+++ scripts/mysql_system_tables_fix.sql	2012-05-22 11:07:34 +0000
@@ -228,6 +228,17 @@
   MODIFY type enum ('function','aggregate') COLLATE utf8_general_ci NOT NULL;
 
 #
+# host name handling changed to "always lower case" in 5.1.53
+#
+UPDATE columns_priv SET Host = LOWER(Host);
+UPDATE db           SET Host = LOWER(Host);
+UPDATE host         SET Host = LOWER(Host);
+UPDATE procs_priv   SET Host = LOWER(Host);
+UPDATE servers      SET Host = LOWER(Host);
+UPDATE tables_priv  SET Host = LOWER(Host);
+UPDATE user         SET Host = LOWER(Host);
+
+#
 # Modify log tables.
 #

Now, when diagnosis has been provided, a change in code is required. A change which will convert all host names to lowercase letters. There should be also a warning (probably at level 1) that host name is changed by server itself.

I do not think that it makes any sense to store host names in both cases.

Bug #69658 marked as duplicate of this one.

See the duplicate bug #69644

Noted in 5.1.73, 5.5.35, 5.6.15, 5.7.3 changelogs.

Host names in grant tables are stored in lowercase, but
mysql_install_db could fail to observe this convention, leading to
accounts that could not be dropped with DROP USER.

5.1$ bzr log -r 4059
------------------------------------------------------------
revno: 4059
committer: Venkata Sidagam <venkata.sidagam@oracle.com>
branch nick: 5.1
timestamp: Thu 2013-10-31 23:02:44 +0530
message:
  Bug #12917164 DROP USER CAN'T DROP USERS WITH LEGACY 
      UPPER CASE HOST NAME ANYMORE
  
  Description:
  It is not possible to drop users with host names with upper case
  letters in them. i.e DROP USER 'root'@'Tmp_Host_Name'; is failing
  with error.
  
  Analysis: Since the fix 11748570 we came up with lower case hostnames
  as standard. But in the current bug the hostname is created by
  mysql_install_db script is still having upper case hostnames. 
  So, if we have the hostname with upper case letters like(Tmp_Host_Name)
  then we will have as it is stored in the mysql.user table. 
  In this case if use "'DROP USER 'root'@'Tmp_Host_Name';" it gives 
  error because we do compare with the lower case of hostname since the 
  11748570 fix.
  
  Fix: We need to convert the hostname to lower case before storing into 
  the mysql.user table when we run the mysql_install_db script.