How to debug 'Access denied' error for MySQL?

Question

I am running a MySQL server (v14.14) on an Apple desktop on my LAN. I can access it from localhost and via a laptop, from a MySQL client. I'm now trying to add access from another client machine and it is giving me Failed to connect to server: Error: Access denied for user 'user2'@'machine4.localdomain' (using password: YES) even though I've done

grant all privileges on *.* to 'user2'@'machine4.localdomain';

I've also tried it by IP address. No error server-side, it claims it's implemented the privilege change (and I did flush afterward) but I just can't get the client to pass the login. What might I be doing wrong and how to debug it? I can't figure out why some clients are able to connect and this new one can't.

Answer 1

The GRANT you were issued bound a user to a certain remote machine hostname. This binding goes through DNS.

Let's assume you have a computer in your network that has an IP address 10.0.0.4 and if you issue nslookup -t ptr 4.0.0.10.in-addr.arpa on the MySQL server machine, it'll reply 4.0.0.10.in-addr.arpa name=machine4.localdomain. Also, the nslookup machine4.localdomain should return 10.0.0.4; this is checked too.

If this is all true, you can issue a grant like

grant all privileges on *.* to 'user2'@'machine4.localdomain';

and then that user would only be able connect from that machine. If you are connecting from any other machine, no matter how you specify the server (by IP or by name), the authentication will fail.

There is a way to grant privileges to many machines in one shot. For example,

grant all privileges on *.* to 'user2'@'%';
grant all privileges on *.* to 'user2'@'%.localdomain';
grant all privileges on *.* to 'user2';

First of these grants will allow connection from any system, provided they know the password (or whatever is needed for this user). Second one will do the reverse DNS, and if the PTR record ends with .localdomain, the authentication may succeed (again, if the password or other credential is correctly passed too). Third one is the same as first; if the host name isn't given, it's '%' by default.

This percent sign is the usual MySQL wildcard; probably, you were already encountered it in queries like SELECT * FROM table WHERE field LIKE 'pre%';. It turns out it works for users too! See this reference for more info.

Also notice you can have different grants for the user for different machines. It's not hard to figure out how to do this.

Bonus: to set or change the password, use

ALTER USER user2 IDENTIFIED BY 'new_password';

(from reference manual)

Answer 2

The explicit client source seems to be what is making it fail.

MySQL default setting is to only allow connections from localhost. To enable your mates to connect, open the MySQL configuration file. The typical location on macOS is /usr/local/etc/my.cnf.

sudo vim /usr/local/etc/my.cnf

Add or modify the following lines in the [mysqld] section of the configuration file to allow MySQL to listen on all network interfaces:

[mysqld]
bind-address = 0.0.0.0

On macOS, MySQL is often managed using brew services or launchctl.

If installed via Homebrew:

brew services restart mysql

If using launchctl:

sudo launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
sudo launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

Then open the MySQL command line interface and check the user privileges:

mysql -u root -p

Grant privileges to the user to connect from any IP address. Replace workmate_user with the actual username and workmate_password with the user's password.

GRANT ALL PRIVILEGES ON *.* TO 'workmate_user'@'%' IDENTIFIED BY 'workmate_password';
FLUSH PRIVILEGES;

Add more users if needed.

That should do it!

---

Now, the answer to your question:

The general query log can log all SQL queries received from clients, including failed login attempts, it is very verbose.

• Enable General Query Log (don't do that in Production!):

  SET GLOBAL general_log = 'ON'; SET GLOBAL general_log_file = '/usr/local/var/mysql/general.log';

Verify:

SHOW VARIABLES LIKE 'general_log%';

• Enable Error Log (Recommended)

Edit your MySQL configuration file. The typical location on macOS is /usr/local/etc/my.cnf. If this file does not exist, you might need to create it.

sudo vim /usr/local/etc/my.cnf

Add or edit the following lines under the [mysqld] section:

[mysqld]
log_error = /usr/local/var/mysql/error.log
log_warnings = 2

Restart the services.

Check the error log file to ensure it is capturing the necessary details:

tail -f /usr/local/var/mysql/error.log

Furthermore, you could enable audit logging (for detailed security auditing), but I think by now you've already figured out what was wrong.

Good luck!