Bug #42242 Can't connect using newly created user
Submitted: 21 Jan 2009 15:29 Modified: 25 Mar 2009 9:35
Reporter: Andrey Hristov Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.1.31, 5.0,6.0 OS:Linux
Assigned to: CPU Architecture:Any
Tags: regression

[21 Jan 2009 15:29] Andrey Hristov
Description:
See how to repeat.

How to repeat:
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.31-debug |
+--------------+
1 row in set (0.00 sec)

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> grant all on *.* to nosuchuser@'%' identified by 'nosuchuser';
Query OK, 0 rows affected (0.00 sec)

-------------
andrey@winnie:~$ mysql -u nosuchuser -pnosuchuser
ERROR 1045 (28000): Access denied for user 'nosuchuser'@'localhost' (using password: YES)
[21 Jan 2009 15:30] Andrey Hristov
Here comes the show grants for the new user:

mysql> show grants for nosuchuser@'%';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for nosuchuser@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'nosuchuser'@'%' IDENTIFIED BY PASSWORD '*12886D564BADEC011128520056CE1B55F67C310A' |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[21 Jan 2009 15:55] Jonas Sundin
Sorry, guess I didn't read it properly. Seems like I get the same result using:

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.0.67    |
+-----------+
1 row in set (0.00 sec)
[21 Jan 2009 20:52] MySQL Verification Team
Thank you for the bug report. Verified as described.
[22 Jan 2009 14:28] Egor Y
flush privileges;
[25 Mar 2009 9:35] Valeriy Kravchuk
Sorry, but I can't repeat this on recent 5.0.79 and 5.1.32. Look:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.32-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant all on *.* to nosuchuser@'%' identified by 'nosuchuser';
Query OK, 0 rows affected (0.41 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -unosuchuser -pnosuchuser -P33
10 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.32-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select user();
+----------------------+
| user()               |
+----------------------+
| nosuchuser@localhost |
+----------------------+
1 row in set (0.05 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.79-enterprise-gpl-nt MySQL Enterprise Server - Pro Edition (
GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant all on *.* to nosuchuser@'%' identified by 'nosuchuser';
Query OK, 0 rows affected (0.17 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -unosuchuser -pnosuchuser -P33
08 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.79-enterprise-gpl-nt MySQL Enterprise Server - Pro Edition (
GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select user();
+----------------------+
| user()               |
+----------------------+
| nosuchuser@localhost |
+----------------------+
1 row in set (0.03 sec)
[25 Mar 2009 9:37] Valeriy Kravchuk
Not repeatable on 6.0.9 also.
[11 May 2012 9:59] Rémy Dernat
Hi, I have a similar problem with my fresh install.

Server version: 5.5.22-0ubuntu1 (Ubuntu)

show grants for myuser@'%';
+----------------------------------------------------------------------------------------------------+
| Grants for myuser@%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '*FE67D8F737018C7CCB4FA9611AE4E009C0BFC0E1' |
| GRANT ALL PRIVILEGES ON `MBB`.* TO 'myuser'@'%' WITH GRANT OPTION                                     |
+----------------------------------------------------------------------------------------------------+
[31 May 2012 4:14] Tony Carter
I saw the same issue. It was because there were "any" users.. I just deleted them