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'
%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:
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?
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.
Feb 17, 2012: MySQL : Why are there "test" entries in mysql.db?
Feb 17, 2012: What is the mysql.db table used for?
Jan 18, 2012: MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
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.
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.
After you enter as the
rootuser 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
rootuser all privileges again:
mysql> grant all privileges on *.* to 'root'@'localhost';
rootuser doesn't have privileges you can try to restore them, so:
Restart the server this way
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.
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.18.104.22.168 - LINUX (Make file with query who create, a new user with full root privileges)
C.22.214.171.124 - 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_schemadb, 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_schemadb (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.