Access denied for user 'test'@'ip'(using password: YES)

Question

I have found many posts for this error, tried everything but still getting same error.I am trying to connect to mysql on ubuntu server from my remote app and mysql client. Let me post whatever steps I have taken already :

1. Stop the firewall on ubuntu server : iptables -F. Also tried with sudo service ufw stop.
2. Commented "bind-address" in /etc/mysql/my.cnf and restarted mysql.
3. Added user in mysql : CREATE USER 'test'@'%' IDENTIFIED BY 'testpwd';
4. GRANT ALL PRIVILEGES ON . TO 'test'@'%' IDENTIFIED BY 'testpwd' WITH GRANT OPTION;
5. FLUSH PRIVILEGES;
6. I can see the entry of new user by "Select host, user from mysql.user";
7. restarted mysql again. Still the same error!
8. Now I thought maybe there is some problem still with firewall, so added :iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT Problem still persist.

Answer 1

"Access denied for user 'test'@'ip'(using password: YES)" is a MySQL error.

This means that at the network level everything is working, because to be denied access as a given user, the server must have understood which user you were trying to connect as. So network, firewall, routing, and so on and so forth, must all be working; the server must be listening, etc..

The problem lies "simply" in the authentication.

Try connecting locally to the database (to override the authentication) and inspect the privilege table:

USE mysql;
SELECT User, Host, Password from user WHERE User = 'test';

and remember that the line you're interested in is the one mentioning the IP (since the error message specifies the IP, and not the host name - in which case, it could have been a DNS problem; the host name is the hostname that the server believes you are coming from, not the hostname you are really coming from).

The user/host matching goes from more specific to less specific. So if you already had:

user      host     password
test      1.2.3.4  foo

and ran,

GRANT... TO test@'%' ... PASSWORD bar

...this grant would work from everywhere except 1.2.3.4, where the password would remain 'foo'.

From the manual (link above):

The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address is not affected by whether it has a netmask, so 192.168.1.13 and 192.168.1.0/255.255.255.0 are considered equally specific.) The pattern '%' means “any host” and is least specific. The empty string '' also means “any host” but sorts after '%'. Rows with the same Host value are ordered with the most-specific User values first (a blank User value means “any user” and is least specific). For rows with equally-specific Host and User values, the order is indeterminate.

You might be forced to do

USE mysql;
DELETE FROM user WHERE User = 'test';
GRANT ALL PRIVILEGES ON database.* TO 'test'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

to ensure that there're no spurious lines in the grant table referring to the user 'test'.

(Also, the GRANT should be, I think,

GRANT ALL PRIVILEGES ON databasename.*

)

---

Security doubt (unrelated to the answer)

The manual above says: The specificity of a literal IP address is not affected by whether it has a netmask, so 192.168.1.13 and 192.168.1.0/255.255.255.0 are considered equally specific.

Now at a very first glance 127.0.0.1/0.0.0.0 seems very specific (and harmless) for localhost. The netmask, if I'm not mistaken, ensures that it is equivalent to %, except that it is incredibly specific and will run first. Therefore

test     bar         %           
test     localfoo    127.0.0.1/0.0.0.0

means that the password for test from anywhere it's not "bar" at all, but it is "localfoo".

No one would insert such a grant by mistake, but there's mistake and mistake.