Development Shack Technology Understood

MySQL authenticates without password?  

When using the command-line mysql command with a specified username, it might authenticate without a password sometimes.

If you run: mysql -u john -p

And it authenticates without a password (and you know the user has a password), then try running this while logged in:

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| @localhost     |
+----------------+

You might be authenticated as a "localhost" because the host "localhost" is more specific to your authentication request than the user name. (See this confusing explanation on the MySQL documentation):

It is a common misconception to think that, for a given user name, all rows that explicitly name that user are used first when the server attempts to find a match for the connection. This is not true. The preceding example illustrates this, where a connection from thomas.loc.gov by jeffrey is first matched not by the row containing "jeffrey" as the User column value, but by the row with no user name. As a result, jeffrey is authenticated as an anonymous user, even though he specified a user name when connecting.

To fix this, make sure to give the CREATE USER a host more specific than "%"

CREATE USER 'john'@'localhost' IDENTIFIED BY 'my-password';