MySQL Bugs: #1670: Grant with % as host do not work

Bug #1670	Grant with % as host do not work
Submitted:	27 Oct 2003 0:05	Modified:	27 Oct 2003 1:34
Reporter:	Jonas Gauffin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.16	OS:	Windows (Win2k Server)
Assigned to:		CPU Architecture:	Any

Description:
Using % as host doesnt work, I get access denied.

Example:
GRANT insert,select,update,delete ON mydb.* TO auser IDENTIFIED BY 'ddddd'

it works if I use localhost instead:
GRANT insert,select,update,delete ON mydb.* TO auser@localhost IDENTIFIED BY 'ddddd'

How to repeat:
Create a user:

GRANT insert,select,update,delete ON mydb.* TO auser IDENTIFIED BY 'ddddd'

try to login with it.

Suggested fix:
Make sure that all hosts (none or % specified)

This is a common error scenario, which is described in our manual
(http://www.mysql.com/doc/en/Access_denied.html):

you can't figure out why you get Access denied , remove from the user 
table all entries that have Host values containing wildcards (entries 
that contain `%' or `_' ).  A very common error is to insert a new entry 
with Host ='%' and User ='some user' , thinking that this will allow you 
to specify localhost to connect from the same machine.  The reason that 
this doesn't work is that the default privileges include an entry with 
Host ='localhost' and User ='' .  Because that entry has a Host value 
'localhost' that is more specific than '%' , it is used in preference to 
the new entry when connecting from localhost !  The correct procedure is 
to insert a second entry with Host ='localhost' and User ='some_user', 
or to remove the entry with Host ='localhost' and User =''.

I do not think that your comment apply.

I have created a new fresh database and a new user.
The user is created by using
GRANT select,insert,update,delete ON mynewdb.* TO mynewuser IDENTIFIED BY 'mypw'

that line would allow me to use "mysql mynewdb -u mynewuser -pmypw" from all hosts, right? It do not work from any host (access denied).

If I use "GRANT select,insert,update,delete ON mynewdb.* TO mynewuser@localhost IDENTIFIED BY 'mypw'" it works without any errors

try this script:
create database accessTest;
use accessTest;
create table accessTest1 (acol int);
grant select,insert,update,delete on accessTest.* to accessUser identified by 'accessPw';

then try to connect with mysql.exe:
C:\>\mysql\bin\mysql.exe accessTest -u accessUser -pAccessPw;
ERROR 1045: Access denied for user: 'accessUser@127.0.0.1' (Using password: YES)

is this the correct behaviour?

ahh. ok. I understand.