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

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 that 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 let's 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.

Depone

I'm experiencing the same remotely (and select * FROM mysql.user where user= 'root' and host = '%' shows the same privileges yours does). HOWEVER, IT DOES WORK ON LOCALHOST even though the row in mysql.user with host = '%' is identical to the row with host = 'localhost' apart from that field. I'm running 5.0.45 on Windows Server 2003. Any answers gratefully accepted! – Alarmist

+1 for such an organized question! – Dray

make sure you have opend cmd by run as administrator – Angy

I just had a space between -p and the password. I know its silly but might help someone. – Thermobarometer

Answer 1

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.

Schug

This is useful information, but how do I fix the problem? – Wedlock

@augurar, Run mysql_upgrade – Schug

Or, check if you have another user with 'ALL PRIVILEGES' and switch to that one and change the privileges fo the localhost. For ex, I had all privileges on [email protected] and the root@localhost did not. – Simplex

@CarabusManuel thank goodness I saw your message. On my Virtualmin system show grants didn't have the grant all but there was root@127 that did. Also show grants showed the password for that root. So I fixed it using mysql -uroot -pnewpass -h127.0.0.1 – Omnipotence

This worked for me, I had to run mysql_upgrade as the root user. See related SO post #22565009 – Ottar

Note, the root password does NOT need to be passed on the command line. Just pass -p and a prompt for the password will be presented. – Ottar

Answer 2

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

Bedfellow

Please explain this. Why '%'.* works but not *.*? – Roy

This is the right way to do it. You have to login to MySQL as root and then try to grant privileges. – Tejada

I am getting Error 1044 access denied :( help me please – Skiplane

Thanks, was trying to access mysql from a python application using pymysql. Got error saying "Host is not allowed to connect to this MariaDB server", solved that by adding a new user given [here]( #19101743), then got another error saying "access denied for user", and solved it by your method. – Renie

If you use '%'.* instead of `%`.* does not work. You MUST use `%`.* – Pelkey

Answer 3

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.

Broncho

thanx.. in mac OS with mysql installed through homebrew run the following command cd /usr/local/Cellar/mysql/5.5.25a/bin && mysql_upgrade – Squamosal

If you are on a CentOs or have installed MySQL via a package manager this is likely the command you should run:/usr/bin/mysql_upgrade -u root -p enter password and Bob's Your Uncle! – Saturnalia

After fighting here and there with so many comments, this one helped me. Thanks a lot – Winkelman

I know this is older but this helped me a lot and I wanted to add one more comment for future visitors: if you're getting ACCESS DENIED trying to run mysql_upgrade, try running it like this instead: mysql_upgrade -p – Sun

On ubuntu 12.04 works perfectly after exactly the same privilege problem - as "mysql_upgrade -u <user> -p<password>" options – Incertitude

What I found is that if you exported databases from MySQL <= 5.1 via mysqldump ... --all-databases and then imported that into your MySQL >= 5.5, your users will have been replaced (of course), but your root will have the same problem as OP. And mysql_upgrade won't work - you have to add --force flag, i.e. mysql_upgrade -u root -p --force. Hope that helps someone here. – Gyrostatic

@DominikGoltermann here you can find the mysql_upgrade command description: http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html – Broncho

@Aryo, Is this a MySQL bug? – Roy

@Roy No, I don't think so. This is only happened when I tried to upgrade to a different minor version of MySQL (5.1 to 5.5). I think MySQL 5.5 has different permission (grant) table structures with MySQL 5.1, so executing mysql_upgrade command solve the problem. – Broncho

Could you guys please explain the risk of mysql_upgrade without backup? Facing same problem, but not afford to do a full backup because of the downtime. I posted a question here : stackoverflow.com/questions/49606663 – Filiano

Answer 4

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

Vaporescence

I see, this highlighted a problem with my user's name in the statement. – Houseline

Answer 5

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

Shorthorn

Thanks, this helped me out. Luckily, it didn't matter if I obliterated all the DBs... – Quickie

Answer 6

I had the same problem, i.e. all privileges granted for root:

SHOW GRANTS FOR 'root'@'localhost'\G
*************************** 1. row ***************************
Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*[blabla]' WITH GRANT OPTION

...but still not allowed to create a table:

 create table t3(id int, txt varchar(50), primary key(id));
ERROR 1142 (42000): CREATE command denied to user 'root'@'localhost' for table 't3'

Well, it was cause by an annoying user error, i.e. I didn't select a database. After issuing USE dbname it worked fine.

Menke

Answer 7

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.

Easton

This solved the problem for me, as well, but only after I found out that you need to FLUSH PRIVILEGES; afterwards and relogin to have the grant option set. – Logger

Answer 8

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.

Petta

This worked for me. It looks like mysql does not create a permissions for 'user'@'localhost'. So I had to create that user and make sure it had a password. – Thickleaf

The permissions were wrong! I am amazed. Thanks so much for posting that. – Trifurcate

Answer 9

I had the same problem and it took a lot of reading SO posts and Google's documentation. I finally found this from the Cloud SQL FAQ:

Google Cloud SQL does not support SUPER privileges, which means that GRANT ALL PRIVILEGES statements will not work. As an alternative, you can use GRANT ALL ON `%`.*

Prado

Answer 10

Typing SHOW GRANTS FOR 'root'@'localhost'; showed me some obscured password, so I logged into mysql of that system using HeidiSQL on another system (using root as the username and the corresponding password) and typed
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'thepassword' WITH GRANT OPTION;

and it worked when I went back to the system and logged on using
mysql -uroot -pthepassword;

Tejada

Answer 11

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

Erlond

Answer 12

One simple solution which always works for me when faced with mysql "access denied" errors: use sudo.

sudo mysql -u root

Then the necessary permissions exist for GRANT commands.

Arevalo

Answer 13

For those who still stumble upon this like I did, it's worth checking to make sure the attempted GRANT does not already exist:

SHOW GRANTS FOR username;

In my case, the error was not actually because there was a permission error, but because the GRANT already existed.

Phenetole

Answer 14

just open PHPmyAdmin and go to user accounts and give all privlage to pma@localhost

Rozina