Access denied for user 'test'@'localhost' (using password: NO)

September 25, 2019 192 views
MySQL LAMP Stack

My login attempts fail for a new user account that I have created, and I have spent several hours learning how authentication is handled by MySQL, with no progress.

Everything is on the same server:

  • MySQL 5.7
  • phpMyAdmin 4.9
  • Ubuntu 18.04

The end result doesn’t change, regardless of whether I:

1) Run the query (as root)
2) Open a browser on a different machine and run a simple PHP connection script

Here’s what I have tried from phpMyAdmin (logged in as root):

flush privileges;
create user 'testuser'@'localhost' identified by 'password!';
grant * on inventory to 'testuser'@'localhost';
flush privileges;

While logged into Ubuntu via ssh:
mysql -u testuser -p
mysql -u testuser -h 127.0.0.1 -p
mysql -u testuser -h localhost -p
mysql -u testuser -h <the-actual-hostname> -p

When attempting to log in, depending on whether I used 'testuser'@'%' or 'testuser'@'localhost' I’ll get one of two errors:

Access denied for user ‘testuser’@’%’ (using password: YES)
Access denied for user 'testuser’@'localhost’ (using password: YES)

To rule out this being a permissions issue, I assigned the user all global permissions, but the error message didn’t change, so I reverted the change.

Running “'select host,user,plugin,authenticationstring,accountlocked from mysql.user where user=testuser;”’ shows:

host user plugin authentication_string account_locked
localhost testuser mysqlnativepassword blahblahblah N
  • There are no users with blank usernames in the table mysql.user
  • The password doesn’t have special characters
  • I’ve tried localhost, 127.0.0.1 and the actual hostname itself

Just to show another way I’m trying to connect, I have a LAMP stack running and navigate to this php page in multiple web browsers:

<?php
$dbhost='localhost';
$dbuser='testuser';
$dbpass='password';
$dbname='inventory';

$conn = new mysqli($dbhost,$dbuser,$dbpass,$dbname);
if($conn->connect_error) {
    die("Error: Couldn't connect: ".$conn->connect_error);
}
?>

I’ve tried deleting the user and re-creating again after having confirmed that there’s no blank usernames or duplicates of this user.

/var/log/mysql/error.log only shows the exact same error message.

2 Answers

Hello,

I think that you have a typo in your grant privileges query. I’ve tried your queries and it worked as normal, I just had to adjust the grant privileges query a little bit.

Here’s what I did:

  • I created a database:
CREATE DATABASE inventory;
  • I created user and grant privileges for the new database:
GRANT ALL ON inventory.* TO 'testuser'@'localhost' IDENTIFIED BY 'password';
  • Then I tested your sample connection script and it worked as normal.

I could suggest just trying this from scratch, just drop the user and try to create a new one.

Hope that this helps!
Regards,
Bobby

lol wrong server, time for coffee, thanks Bobby

Have another answer? Share your knowledge.