MySQL user help please!

[judas]

I've got a MySQL database sitting behind a web application and everything's running fine. But now the client wants to be able to pull all the data into an external Access database and generate reports etc - that's not a problem, I just use ODBC to link the tables. The problem is that I want to create a new user with read-only (select) privileges for the ODBC link, but I have no idea what the client's IP address will be at any one time (they could be using dial-up). But when I create the new user using '%' as the host identifier it won't let me log in and gives an 'access denied' message.

I've tried reading through the MySQL documentation, but it may as well be written in Chinese for the help it is - it's clearly aimed at people who find command line stuff exciting whereas I use a nice Windows GUI to do all the management stuff. The user is being created ok with all the right privileges and host settings etc, it just won't work.

Any clues as to where I'm going wrong.

Give me SQL Server any day...

[john_p]

http://dev.mysql.com/doc/mysql/en/GRANT.html

From the mysql client logged in as root:

GRANT SELECT ON database.table TO username@'%' IDENTIFIED BY 'password'

remember to open port 3306 on the firewall




>> Edited by john_p on Monday 27th September 11:52

[judas]

Thanks John, but I've done all that. The user is being created OK, it's just that having a wildcard as the host doesn't work.

Firewall is not a problem either as the db is on a remote server anyhow.

The error message is from the ODBC drive is as follows:

[MySQL][ODBC 3.51 Driver]Access is denied for user: '[e-mail]' (Using password: YES)

The problem appears not to be specific to this user, but to any using wilcard hosts - which makes emergency db admin from home impossible too. Is there some kind of global option to allow/disallow wildcards in hosts on a server wide basis?

[Plotloss]

Have you deleted the anonymous user from the access table and then flushed the priviledges?

[judas]

Matt - you're a star! Thank you - I owe you a pint or two now at BTaP

[Plotloss]

Worked then?

Cool!

I will never understand why they shipped a nousername/nopassword user...

[judas]

Me neither - especially with no explicit explanation of how this will affect other user accounts.

Anyhow - thanks again. At least now I know why half the logins I created never worked and it wasn't me doing things wrong.