CODEWITHSUNDEEP
phpmysqlphpmyadmin
ReneFroger
ReneFroger

Reputation: 530

Mysql allows access for root user, but acces is denied for user with all privileges as root user

I have PHPMyAdmin and MySQL installed on WAMP, and running.

The user root is created by default in PHPmyAdmin, and is not using a password.

I use a interface to log in to the MySQL. Ok, when I pass the login details:

Datasource : dbi:mysql:foobar:localhost:3306 
 User Name : root
     Auth  :

Dbi stands for database engine, foobar is the table, localhost = address, and port. I get access to the database, everything works fine.

Okay, then I create a new user in PHPMyAdmin.

The new user is johnsmith with the password foobar. He get all privilges, same as the root

It looks like this:

enter image description here

So in the same interface, I fill the following: 

 Datasource : dbi:mysql:foobar:localhost:3306 
 User Name  : johnsmith
 Auth       : foobar

But for this user I got an error from mysql:

 Access denied for user 'johnsmith'@'localhost' (using password: YES)

Tested it again with root-user, it gets access, but the user johnsmith don't get access, even when he have all privileges and admin rights. So I suspect I'm missing a feature in PHPAdmin..

Could anyone point out where I went wrong? It would be greatly appreciated.

Upvotes: 1

Views: 476

Answers (2)

dave
dave

Reputation: 64725

Run this:

CREATE USER 'johnsmith'@'localhost' IDENTIFIED BY 'foobar';
GRANT ALL PRIVILEGES ON *.* TO 'johnsmith'@'localhost' WITH GRANT OPTION;
CREATE USER 'johnsmith'@'%' IDENTIFIED BY 'foobar';
GRANT ALL PRIVILEGES ON *.* TO 'johnsmith'@'%' WITH GRANT OPTION;
CREATE USER 'johnsmith'@'127.0.0.1' IDENTIFIED BY 'foobar';
GRANT ALL PRIVILEGES ON *.* TO 'johnsmith'@'127.0.0.1' WITH GRANT OPTION;
FLUSH PRIVILEGES;

For whatever reason localhost is treated differently than others, and the % wildcard doesn't seem to apply to it. Then you have to FLUSH PRIVILEGES for it to take effect.

Upvotes: 2

Dominique Vermeersch
Dominique Vermeersch

Reputation: 43

Did you do a 'FLUSH PRIVILEGES' after you created the user? MySQL needs te re-read the privileges table in order to see the new changes.

Upvotes: 1

Related Questions