Databases
StackOverflow2013
Postsdbo
POAccess denied for user 'root'@'localhost' while attempting to grant privileges. How do I grant privileges?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAccess denied for user 'root'@'localhost' while attempting to grant privileges. How do I grant privileges?
    primarykey
    data
    text
    <p>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. :-)</p> <p>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.)</p> <p>Background:</p> <p>Logged in to the shell of the machine running the MySQL server via ssh, I log in as root:</p> <pre><code>[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; </code></pre> <p>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</p> <pre><code>mysql&gt; FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql&gt; </code></pre> <p>Next make sure I am who I think I am:</p> <pre><code>mysql&gt; SELECT user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) </code></pre> <p>...and really <em>really</em> make sure:</p> <pre><code>mysql&gt; SELECT current_user(); +----------------+ | current_user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql&gt; </code></pre> <p>So far so good. Now what privileges do I have?</p> <pre><code>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) </code></pre> <p>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):</p> <pre><code>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) </code></pre> <p>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?</p> <pre><code>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) </code></pre> <p>How could I have screwed up something this basic?</p> <p>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.</p> <p>Thank you!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCary Bondoc
    1. USSteven Scotten
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAccess denied for user 'root'@'localhost' while attempting to grant privileges. How do I grant privileges?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POAccess denied for user 'root'@'localhost' while attempting to grant privileges. How do I grant privileges?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POAccess denied for user 'root'@'localhost' while attempting to grant privileges. How do I grant privileges?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COwhich OS are using?
      singulars
      1. POAccess denied for user 'root'@'localhost' while attempting to grant privileges. How do I grant privileges?
      1. USMithun Sasidharan
    2. COFedora Linux 15
      singulars
      1. POAccess denied for user 'root'@'localhost' while attempting to grant privileges. How do I grant privileges?
      1. USSteven Scotten
    3. COI'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!
      singulars
      1. POAccess denied for user 'root'@'localhost' while attempting to grant privileges. How do I grant privileges?
      1. USRichard Fawcett
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload