cannot grant privileges to mysql database

Question

I have mysql Server version: 5.5.32-0ubuntu0.12.04.1 (Ubuntu) installed linux Ubuntu 12.04 LTS.

I seem to have all the permissions as root. I can create a user and a db. However, I cannot seem to give the user all the permissions to the db.

My .my.cnf:

[client]
user=root
password=test

I login through mysql -u root -h localhost -p, but I cannot login without the -p option though I have the .my.cnf (not an issue, but odd).

There were a bunch of root users, so I got rid of them and I have these users:

mysql> SELECT host,user,password FROM mysql.user;
+-----------+------------------+-------------------------------------------+
| host      | user             | password                                  |
+-----------+------------------+-------------------------------------------+
| localhost | root             | ***************************************** |
| localhost | debian-sys-maint | ***************************************** |
+-----------+------------------+-------------------------------------------+

mysql> SHOW GRANTS FOR 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*****************************************' |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                         |
+----------------------------------------------------------------------------------------------------------------------+

Now, I create a db, a user. The last line shows an error when I grant permissions. Can you please let me know why I am getting this error and what I can do to make this work?

mysql> create database staging;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER 'staging'@'localhost' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON staging.* TO 'staging'@'localhost';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'staging'

Answer 1

First, Identify the user you are logged in as:

 select user(); select current_user();

The result for the first command is what you attempted to login as, the second is what you actually connected as. Confirm that you are logged in as root@localhost in mysql.

The issue was that the installation I came up with did not provide Grant_priv to root@localhost. Here is how you can check.

mysql> SELECT host,user,password,Grant_priv,Super_priv FROM mysql.user;
+-----------+------------------+-------------------------------------------+------------+------------+
| host      | user             | password                                  | Grant_priv | Super_priv |
+-----------+------------------+-------------------------------------------+------------+------------+
| localhost | root             | ***************************************** | N          | Y          |
| localhost | debian-sys-maint | ***************************************** | Y          | Y          |
| localhost | staging          | ***************************************** | N          | N          |
+-----------+------------------+-------------------------------------------+------------+------------+

You can see that the Grant_priv is set to N for root@localhost. This needs to be Y. Here is how I fixed this:

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'root'@'localhost';

I did get some permission error, but when I logged back in, it was fine.

Answer 2

try flushing privileges after granting permissions

GRANT ALL PRIVILEGES ON staging.* TO 'staging'@'localhost' IDENTIFIED BY 'test';
FLUSH PRIVILEGES;