Access denied for user 'root'@'localho

Question

I've looked at a number of similar questions and so I'm demonstrating that I've checked the basics. Though of course, that doesn't mean I haven't missed something totally obvious. :-)

My question is: why am I denied access on a user with the privileges to do what I'm trying to do and where I have already typed the password and been granted access? (For the sake of completeness, I tried typing the wrong password just to make sure that MySQL client would deny me access at program start.)

Background:

Logged in to the shell of the machine running the MySQL server via ssh, I log in as root:

[myname@host ~]$ mysql -u root -p -hlocalhost
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 62396
Server version: 5.5.18-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Awesome. My reading of the answers to similar questions suggests that I should make sure the the privileges are current with what is in the grant tables

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> 

Next make sure I am who I think I am:

mysql> SELECT user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

...and really really make sure:

mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> 

So far so good. Now what privileges do I have?

mysql> SHOW GRANTS FOR 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                                                                        |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '[OBSCURED]' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Now that's a little hard to read, so lets try this way (you will also get to see that there is a non-localhost 'root' user):

mysql> SELECT * FROM mysql.user WHERE User='root'\G
*************************** 1. row ***************************
                 Host: localhost
                 User: root
             Password: *[OBSCURED]
          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
             ssl_type: 
           ssl_cipher: 
          x509_issuer: 
         x509_subject: 
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
*************************** 2. row ***************************
                 Host: [HOSTNAME].com
                 User: root
             Password: *[OBSCURED]
          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
             ssl_type: 
           ssl_cipher: 
          x509_issuer: 
         x509_subject: 
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
 2 rows in set (0.00 sec)

Awesome! MySQL thinks that I am root@localhost and root@localhost has all those privileges. That means I ought to be able to do what I want, right?

mysql> GRANT ALL PRIVILEGES ON *.* TO 'steves'@'[hostname].com' IDENTIFIED BY '[OBSCURED]' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

How could I have screwed up something this basic?

Side note: for anyone who wants to suggest that I not have a user named root with all privileges, that's great and something I'll consider doing once I can give another user some privileges.

Thank you!

Answer 1

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 *.*

Answer 2

On Debian (Wheezy, 7.8) with MySQL 5.5.40, I found SELECT * FROM mysql.user WHERE User='root'\G showed the Event_priv and 'Trigger_priv` fields were present but not set to Y.

Running mysql_upgrade (with or without --force) made no difference; I needed to do a manual:

update user set Event_priv = 'Y',Trigger_priv = 'Y' where user = 'root'

Then finally I could use:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION

…and then use it more precisely on an individual database/user account.

Answer 3

You may have come to this question with MySQL version 8 installed (like me) and not found a satisfactory answer. You can no longer create users like this in version 8:

GRANT ALL PRIVILEGES ON *.* TO 'steves'@'[hostname].com' IDENTIFIED BY '[OBSCURED]' WITH GRANT OPTION;

The rather confusing error message that you get back is: ERROR 1410 (42000): You are not allowed to create a user with GRANT

In order to create users in version 8 you have to do it in two steps:

CREATE USER 'steves'@'[hostname].com' IDENTIFIED BY '[OBSCURED]';
GRANT ALL PRIVILEGES ON *.* TO 'steves'@'[hostname].com' WITH GRANT OPTION;

Of course, if you prefer, you can also supply a limited number of privileges (instead of GRANT ALL PRIVILEGES), e.g. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER

Answer 4

Basically this error comes when you have not specified a password, it means that you have an incorrect password listed in some option file.

Read this DOC on understanding how to assign and manage Passwords to accounts.

Also , Check if the permission on the folder /var/lib/mysql/mysql is 711 or not.

Answer 5

I run at this when I tried to add privileges to performance_schema, which is mysql bug http://bugs.mysql.com/bug.php?id=44898 (workaround to add --single-transaction).

Answer 6

This happened to me when I tried to install a higher MySQL version than the one coming with the distribution.

I erased the old version then installed the new one (rpm -e ... then rpm -i MySQL-server* ) But did not realize that the files in /var/lib/mysql were still from the older version (with differences as explained by Marc Alff - thanks!)

I could have done an mysql_upgrade, but as I wanted to start from scratch I did:

# su - mysql
$ rm -rf /var/lib/mysql/*
$ mysql_install_db
# /etc/init.d/mysql start

Then set root password (/usr/bin/mysqladmin -u root password), and all worked as expected with the GRANT commands...