Access denied for user 'root'@'%'

  • I used to access the root user in MySQL just fine. But recently, I am no longer able to.

    I am able to login fine :

     mysql -u root -p
    

    Here is the mysql status after login :

    mysql> status
    --------------
    mysql  Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (i686) using readline 6.2
    
    Connection id:      37
    Current database:   
    Current user:       [email protected]
    SSL:            Not in use
    Current pager:      stdout
    Using outfile:      ''
    Using delimiter:    ;
    Server version:     5.5.28-0ubuntu0.12.04.3 (Ubuntu)
    Protocol version:   10
    Connection:     Localhost via UNIX socket
    Server characterset:    latin1
    Db     characterset:    latin1
    Client characterset:    utf8
    Conn.  characterset:    utf8
    UNIX socket:        /var/run/mysqld/mysqld.sock
    Uptime:         4 min 16 sec
    
    Threads: 1  Questions: 112  Slow queries: 0  Opens: 191  
    Flush tables: 1  Open tables:  6  Queries per second avg: 0.437
    --------------
    

    But when I want to do any action, such as :

    mysql> CREATE DATABASE moyennegenerale;
    ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'moyennegenerale'
    

    I understand % is used to signify any host , but my status clearly states localhost. Does somebody have an idea of what might be going on?

  • I think you have anonymous users

    Try running this:

    SELECT user,host,password FROM mysql.user WHERE user='';
    

    This will show what anonymous users exist. Most likely, you will see a line with a blank user, host %, and a blank password as shown below:

    mysql> select user,host,password from mysql.user;
    +-----------+-------------+-------------------------------------------+
    | user      | host        | password                                  |
    +-----------+-------------+-------------------------------------------+
    | lwdba     | 127.0.0.1   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
    | lwdba     | localhost   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
    | lwdba     | %           | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
    | root      | localhost   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
    | root      | 127.0.0.1   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
    | vanilla   | localhost   |                                           |
    | mywife    | %           |                                           |
    |           | %           |                                           | <<<--- LOOK !!!
    | replicant | 10.64.113.% | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
    | kumar     | %           |                                           |
    +-----------+-------------+-------------------------------------------+
    

    So, how did you login? Run this query:

    SELECT USER(),CURRENT_USER();
    

    What does this tell you?

    • USER() reports how you attempted to authenticate in MySQL
    • CURRENT_USER() reports how you were allowed to authenticate in MySQL

    The second function CURRENT_USER() reveals how which anonymous user was used to log in.

    What privileges did you have when you logged in?

    Please run

    SHOW GRANTS;
    

    This will unveil what privileges you had at the time you logged in. The fact that you were blocked from creating a database shows you were not root but some lower-privileged user.

    Please clean up your user grants.

    As for resetting the root password, please do the following:

    echo "SET PASSWORD FOR [email protected]=PASSWORD('password');" > /var/lib/mysql/rootpwd.sql
    service mysql restart
    rm -f /var/lib/mysql/rootpwd.sql
    

    I learned this efficient method from @ShlomiNoach.

    Give it a Try !!!

  • I know what you did.

    Do this:

    SELECT `User`, `Grant_priv` FROM `mysql`.`user` WHERE `User` = 'root';
    

    You will probably notice it returns a 'N' for Grant_priv. So do this:

    UPDATE `mysql`.`user` SET `Grant_priv` = 'Y' WHERE `User` = 'root';
    FLUSH PRIVILEGES;
    SELECT `User`, `Grant_priv` FROM `mysql`.`user`;
    

    And walla! Hope that helps.

    also, for security reasons, you should always delete the root user after creating a new dba.. just make sure they are GRANT ALL PRIVILEGES then at the end WITH GRANT OPTION.. that bit is important.

    This, combined with closing, and re-opening the connection (in my case from HeidiSQL) did the trick!

  • After you enter as the root user check your privileges:

     mysql> show grants for 'root'@'localhost';
    

    After checking your privileges you can try to give another user all the privileges, or you can try to give the root user all privileges again:

     mysql> grant all privileges on *.* to 'root'@'localhost';
    

    If your root user doesn't have privileges you can try to restore them, so:

    Stop the mysqld server

    Restart the server this way mysqld_safe --skip-grant-table

    Restore root privileges with:

     mysql> flush privileges;
     mysql> grant all privileges on *.* to 'root'@'localhost' with grant option;
    

    This is what i get after the show grants for 'root'@'localhost' command : ERROR 1141 (42000): There is no such grant defined for user 'root' on host 'localhost'

    What's the output of the second command?

    There are a "better" way to do this in Debian, where you don't need to restart the server. Just as `root` connect as `[email protected]` and use the password in `/etc/mysql/debian.cnf`. That user have ALL privileges in MySQL. The good thing is you don't need to restart your server.

    Try this. sudo mysql --defaults-file=/etc/mysql/debian.cnf

  • Your privileges may be be reduced?

    Also, you can try to create another account with root privileges.

    And delete root user, and re-create with specified privileges.

    This may be be helpful: http://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html

    C.5.4.1.2 - LINUX (Make file with query who create, a new user with full root privileges)

    C.5.4.1.1 - Windows (Make file with query who create, a new user with full root privilegles)

    Query: (Create root user)

      CREATE USER 'new_root'@'%' IDENTIFIED BY '***';
      GRANT ALL PRIVILEGES ON *.* TO 'new_root'@'%' IDENTIFIED BY '***' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
    

    MySQL Manual Create User: http://dev.mysql.com/doc/refman/5.5/en/adding-users.html

  • Check if you are running the import query against information_schema db, I fell for that for a while because I was using a new MysQL client and didn't realize that the query was trying to create tables in information_schema db (since the new db to be created didn't exist when running the query, the connection fell back to the first db in the server).

    I tried every single answer here and took me nowhere as root access was used and privileges seemed right.

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM