Can't set permissions on MySQL user

Question

I am trying to set the permissions for a MySQL user using the following command.

GRANT ALL ON joomla.* to user@localhost;

I have tried so many versions but they all return: ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'joomla2'

Which seems to indicate that the username "user" is not getting passed? I have tried many versions of the command above, is there something else I need to be aware of.

PS: my name is not actually "user" just in case someone mentions that it is a reserved word or something similar.

Answer 1

Try having a look in the privilege tables. For instance:

> use mysql;
> select * from db;

That might give you a hint as to what's going wrong.

You might also try connecting over tcp rather than unix socket by specifying user@'127.0.0.1' and connecting with the -H 127.0.0.1 flag (if you're using the mysql cmd line client).

Have you tried specifying a password using the 'IDENTIFIED BY "mypwd"' part of the GRANT statement?

Answer 2

This has worked for me:

GRANT ALL PRIVILEGES ON joomla.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

I believe that the problem is that you are not giving a password.

Also, are you using a user that can grant privileges to other users? Do you connect as root or as a regular user?

Answer 3

According to the MySQL Ref Manual I'd say you missed the ' in the above statement.

Did you try:

GRANT ALL ON joomla.* to 'user'@'localhost';

Answer 4

Are you logged into MySQL as user@localhost when you try and run this command?

If so you won't be able to assign yourself the privileges you will need to do it while logged in as the root user, or another user who is able to assign the privileges.

Answer 5

Thanks everyone. It doesn't answer the question, but I am going to reinstall mysql and give it a proper root username and password this time. Last time I left these empty. The long solution but it will solve the problem.

Answer 6

Have you restarted the server afterwards? If not, you need to issue FLUSH PRIVILEGES