Understanding MySQL Authentication: Host Wildcards and Grant Tables
I've lost more hours than I like to admit to "access denied" errors where the password was perfectly fine. The real problem was that I kept thinking of an account as a username. It isn't. It's a 'user'@'host' pair, and the server picks which pair applies using rules that aren't obvious until they bite you.
I'm testing on MySQL 5.7, but 5.6 and MariaDB 10.1 behave the same way.
Start with the grant tables
Everything starts with mysql.user. Each row is one account, and the Host column is the one most people never look at.
SELECT User, Host, plugin, authentication_string FROM mysql.user ORDER BY User, Host;
On a typical 5.7 server you will see root@localhost, mysql.sys@localhost, mysql.session@localhost and whatever you created. The Host column can hold a literal hostname, an IP address, an IP with netmask (192.168.1.0/255.255.255.0), or a pattern using the SQL LIKE wildcards % and _.
Privileges beyond the global level live in mysql.db, mysql.tables_priv, mysql.columns_priv and mysql.procs_priv. You rarely need to read these by hand; SHOW GRANTS assembles them for you.
How the server picks a row
When a client connects, the server knows the username it supplied and the address it came from. Then it looks for a matching row in mysql.user, and here's the rule that matters: the server sorts rows so that the most specific host wins. A literal hostname or IP beats a pattern with wildcards, and a pattern with fewer wildcards beats one with more. Among equally specific hosts, a more specific username wins over the blank (anonymous) username.
Concretely, if you have both of these rows:
CREATE USER 'app'@'%' IDENTIFIED BY 'wildcard-secret';
CREATE USER 'app'@'10.0.0.5' IDENTIFIED BY 'specific-secret';
a connection from 10.0.0.5 will be authenticated against 'app'@'10.0.0.5' and only that row. If the client sends wildcard-secret, it gets access denied, even though a row with that password exists. Each account has its own password and its own privileges; they do not merge.
What localhost really means
'user'@'localhost' matches only connections over the Unix socket, or TCP connections from 127.0.0.1 when the server resolves that to localhost. A connection to the machine's own external IP, such as mysql -h 192.168.1.20, does not match localhost. If you only created a localhost account, that connection falls through to 'user'@'%' if it exists, or to an anonymous account, or fails.
Also, '%' does not include localhost on Ubuntu and Debian builds. That is why so many tutorials create both:
CREATE USER 'app'@'localhost' IDENTIFIED BY 'secret';
CREATE USER 'app'@'%' IDENTIFIED BY 'secret';
GRANT ALL ON appdb.* TO 'app'@'localhost';
GRANT ALL ON appdb.* TO 'app'@'%';
Which account am I, really?
MySQL will tell you both the account you claimed and the one it matched you to. This is the first thing I run when something is weird:
SELECT USER(), CURRENT_USER();
USER() returns the username and the host the client connected from, for example app@10.0.0.5. CURRENT_USER() returns the grant-table row that was used, for example app@%. When these differ in the host part, you have found your problem. Follow up with:
SHOW GRANTS FOR CURRENT_USER();
DNS, and why I turn it off
By default the server tries to reverse-resolve client IPs so hostnames in the Host column can match. It's slow and unpredictable, and a flaky DNS server turns into flaky logins. I set this on every production server:
[mysqld]
skip-name-resolve
After this, only IP addresses, netmasks and % patterns match. If you had accounts like 'app'@'web1.internal', they stop working and must be recreated with IPs. Check /etc/my.cnf on CentOS or /etc/mysql/mysql.conf.d/mysqld.cnf on Ubuntu, then restart.
Testing it
Test from the actual client host, not from the server. The source address decides the match:
mysql -h db.internal -u app -p -e "SELECT USER(), CURRENT_USER();"
Then confirm the privilege set is what you intended:
SHOW GRANTS FOR 'app'@'10.0.0.%';
When it goes wrong
ERROR 1045 (28000): Access denied for user 'app'@'10.0.0.5' (using password: YES) with a password you are sure is correct. Run the mysql.user query from the top and look for a more specific host row for that user. It is almost always a leftover 'app'@'10.0.0.5' or 'app'@'localhost' with an old password.
ERROR 1130 (HY000): Host '10.0.0.5' is not allowed to connect to this MySQL server means no row matched at all. Either the host pattern is wrong or bind-address is fine but the account only exists for localhost.
Anonymous users. If mysql_secure_installation was never run, rows with an empty User exist. Because a specific host with an anonymous user can outrank 'app'@'%', real users get matched to the anonymous account and lose their privileges. Remove them:
DROP USER ''@'localhost';
DROP USER ''@'hostname';
Changes not taking effect. GRANT, CREATE USER and DROP USER reload the in-memory tables automatically. Only direct UPDATE mysql.user ... edits need FLUSH PRIVILEGES.
Check CURRENT_USER() before you touch a single password. I wish somebody had told me that years ago.