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 gt     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 gt  FLUSH PRIVILEGES  Query OK  0 rows affected  0 00 sec   mysql gt     Next make sure I am who I think I am   mysql gt  SELECT user     ----------------    user              ----------------    root localhost    ----------------  1 row in set  0 00 sec       and really really make sure   mysql gt  SELECT current user     ----------------    current user      ----------------    root localhost    ----------------  1 row in set  0 00 sec   mysql gt     So far so good  Now what privileges do I have   mysql gt  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 gt  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 gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt  select version     ------------    version       ------------    5 5 21-log    ------------  1 row in set  0 00 sec   mysql gt  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 gt  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  lt ----------------------------- 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   lt ------------------------------- new column in 5 5  authentication string   lt ------------------------------- 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to mysql-error-1045