ERROR 1044 (42000): Access denied for 'root' With All Privileges

Question

I have strange error. I am logged in local Mysql as root via command line. After creating database:

create database some_db; 

Then giving privileges to some user:

grant all privileges on some_db.* to some_user@'localhost' identified by 'password'; 

This is giving error:

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'some_db' 

The permissions for the root(show grants;) shows:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*8919C53DC7A4DFBF3F8584382E96463583EB7FDA'  

I am also making sure i am logged in as root:

select current_user(); 

And this confirms that i am logged in as 'root'@'localhost'

I have created database and assigned user multiple times and never had an issue. Why i am unable assign user to the database created by root while logged in as root?

p.s. from other posts, i am thinking the issue may be caused due to some strange users

select host, user from mysql.user; 

Some users that i have not added but does show up:

 MY_COMPuTER_name.local |       ''  MY_COMPuTER_name.local |     root 

I tried to delete these users

drop user 'root'@'MY_COMPuTER_name.local'; drop user ''@'MY_COMPuTER_name.local'; 

However, while it states query run successful, the users are not dropped even after flush privileges. Why i am unable delete users? Any help is much appricated

Answer 1

First, Identify the user you are logged in as:

 select user();  select current_user(); 

The result for the first command is what you attempted to login as, the second is what you actually connected as. Confirm that you are logged in as root@localhost in mysql.

Grant_priv to root@localhost. Here is how you can check.

mysql> SELECT host,user,password,Grant_priv,Super_priv FROM mysql.user; +-----------+------------------+-------------------------------------------+------------+------------+ | host      | user             | password                                  | Grant_priv | Super_priv | +-----------+------------------+-------------------------------------------+------------+------------+ | localhost | root             | ***************************************** | N          | Y          | | localhost | debian-sys-maint | ***************************************** | Y          | Y          | | localhost | staging          | ***************************************** | N          | N          | +-----------+------------------+-------------------------------------------+------------+------------+ 

You can see that the Grant_priv is set to N for root@localhost. This needs to be Y. Below is how to fixed this:

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root'; FLUSH PRIVILEGES; GRANT ALL ON *.* TO 'root'@'localhost'; 

I logged back in, it was fine.

Answer 2

If you get an error 1044 (42000) when you try to run SQL commands in MySQL (which installed along XAMPP server) cmd prompt, then here's the solution:

1. Close your MySQL command prompt.

2. Open your cmd prompt (from Start menu -> run -> cmd) which will show: C:\Users\User>_

3. Go to MySQL.exe by Typing the following commands:

C:\Users\User>cd\ C:\>cd xampp C:\xampp>cd mysql C:\xxampp\mysql>cd bin C:\xampp\mysql\bin>mysql -u root

1. Now try creating a new database by typing:

   mysql> create database employee; 

   if it shows:

   Query OK, 1 row affected (0.00 sec) mysql> 

   Then congrats ! You are good to go...