Access denied for user 'root'@'localhost' while attempting to grant privileges. How do I grant privileges?

I also had the same problem with this but on Windows after upgrading to MySQL 5.5 from MySQL 5.1. I already tried changing, creating, and resetting password mentioned in here, here, here, and here, no clue. I still get the same error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I'm able to connect normally, show all databases, do selects and inserts, create and add users, and but when it comes to GRANT, I'm screwed up. Those access denied error shows up again.

I managed to solve this problem by fixing the privileges by the following command on the MySQL server bin/ directory as mentioned in here:

C:\MySQL Server 5.5\bin> mysql_upgrade

Then, the problem gone away. I hope this solution works on Linux too since usually MySQL provide the same command both on Linux and Windows.

This might happen when you attempt to grant all privileges on all tables to another user, because the mysql.users table is considered off-limits for a user other than root.

The following however, should work:

GRANT ALL PRIVILEGES ON `%`.* TO '[user]'@'[hostname]' IDENTIFIED BY '[password]' WITH GRANT OPTION;

Note that we use `%`.* instead of *.*

Notice how the output of

SHOW GRANTS FOR 'root'@'localhost';

did not say 'ALL PRIVILEGES' but had to spell out what root@localhost has.

GRANT ALL PRIVILEGES will fail, because a user can not grant what he/she does not have, and the server seem to think something is not here ...

Now, what's missing then ?

On my system, I get this:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.21-log |
+------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM mysql.user WHERE User='root' and Host='localhost'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password: 
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y <----------------------------- new column in 5.5
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: <------------------------------- new column in 5.5
 authentication_string: <------------------------------- new column in 5.5
1 row in set (0.00 sec)

There are also new tables in 5.5, such as mysql.proxies_user: make sure you have them.

When installing a brand new mysql server instance, the install script will create all the mysql.* tables with the proper structure.

When upgrading from an old version, make sure the proper upgrade procedure (mysql_upgrade) is used, which will add the missing tables / columns.

It is only a guess, but it seems mysql_upgrade was not done for this instance, causing the behavior seen.