On Sat, Nov 06, 2004 at 12:22:43PM -0000, Mark Rogers wrote:
Chris Green writes:
However I seem quite unable to create any more users that actually work, whatever I do I get permission errors. E.g. I have created a user 'chris' with 'GRANT ALL ON *.* TO chris' and if I run mysql as chris and try and create a database I get:- ERROR 1044: Access denied for user: '@localhost' to database 'chrisdb' Similarly if chris tries to use an already existing database:- ERROR 1044: Access denied for user: '@localhost' to database 'fred'
IIRC, GRANT ALL ON *.* TO chris .. is the equivalent of GRANT ALL ON *.* TO chris@'%' .. ie allow chris access to all databases from any machine, so that bit looks right (although rather insecure...)
I think that the error message is the clue: user '@localhost' suggests that you're not trying to connect as user chris. Which I think means that you're confusing mysql usernames and Linux users.
I don't think I'm confusing mysql users and linuc users, I have realised they are quite separate.
If I log in to mysql as follows:-
mysql -uchris
I get logged in OK, but when I try and access a database I get the error:-
mysql> use fred; ERROR 1044: Access denied for user: '@localhost' to database 'fred'
Try (logged in to Linux as any user): mysql -uchris or mysql -uchris fred
Both give the above error.
You don't say how you're connecting, but that's the key.
NB: I would recommend GRANT ALL ON *.* TO chris@localhost IDENTIFIED BY 'mypassword'
OK, I've done the above as the mysql root user and it returns an OK message.
and mysql -uchris -p fred .. which prompts for the password, or: mysql -uchris -pmypassword fred
... and this works too now! Aargh, I don't know what I was doing wrong before, oh well, thanks all!
Substitute localhost for '%' if you really mean any host.
Also, note that "GRANT ALL ON" really only grants most access rights - user "chris" won't be able to assign access rights to others. Use "GRANT ALL, GRANT OPTION ON".
Yes, I had read that bit.
This kind of thing is particularly relevant if installing something like phpMyAdmin (well recommended); you can't use phpMyAdmin to define users unless you give it the grant option, and if you allow worldwide rights without passwords (instead of just localhost with password) you're asking for trouble :-)
Thanks for all the help everyone, I seem to be sorted now.