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.
Try (logged in to Linux as any user): mysql -uchris or mysql -uchris fred
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' and mysql -uchris -p fred .. which prompts for the password, or: mysql -uchris -pmypassword fred
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".
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 :-)