Fix: MySQL user shows no databases despite having privileges

Quick answer

Run SHOW GRANTS FOR 'user'@'host'; and check if the host matches the one you're connecting from. If not, drop and recreate the user with the correct host, or add a new grant with the right host. Then run FLUSH PRIVILEGES; and reconnect.

Why this happens

I've seen this dozens of times. A junior DBA runs GRANT ALL PRIVILEGES ON *.* TO 'user'@'%'; and thinks they're done. But the user actually connects from localhost or a specific IP, not %. MySQL treats 'user'@'localhost' and 'user'@'%' as completely separate accounts. The one with grants is sitting unused while the other has nothing. Another common cause: you granted privileges on specific databases but never granted the USAGE privilege — without it, the user can't even see the database list.

Step-by-step fix

Identify the user you're actually using. Connect to MySQL as that user and run:
```
SELECT CURRENT_USER();
```
You'll see something like 'appuser'@'192.168.1.100'. Write that down exactly.
Check what grants exist for that exact user. As root or a user with SELECT ON mysql.*, run:
```
SHOW GRANTS FOR 'appuser'@'192.168.1.100';
```
If you get an error (like ERROR 1141), that user account doesn't exist yet — that's your problem.
If the user doesn't exist, create it with the right host. Don't guess the host. Use the exact one from step 1:
```
CREATE USER 'appuser'@'192.168.1.100' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'appuser'@'192.168.1.100' WITH GRANT OPTION;
FLUSH PRIVILEGES;
```
After this, reconnect as appuser. Run SHOW DATABASES; — you should see them now.
If the user exists but has no databases showing, check for a missing USAGE grant. Run:
```
SHOW GRANTS FOR 'appuser'@'192.168.1.100';
```
If you see only GRANT USAGE ON *.* TO ... and nothing else, that's the issue. USAGE by itself gives zero database access. Fix it by running:
```
GRANT ALL PRIVILEGES ON *.* TO 'appuser'@'192.168.1.100' WITH GRANT OPTION;
FLUSH PRIVILEGES;
```
Then reconnect and test.
Still nothing? Check the mysql.user table directly. As root, run:
```
SELECT user, host, Grant_priv, Super_priv FROM mysql.user WHERE user = 'appuser';
```
If you see multiple rows for the same user with different hosts (like localhost and %), you have duplicate accounts. Drop the one you don't need:
```
DROP USER 'appuser'@'localhost';
FLUSH PRIVILEGES;
```
Then reconnect.

Alternative fix: force the host with a wildcard

If you can't control the client IP (for example, your app connects from a load balancer that changes IPs), use % as the host in the grant. But you must also grant on localhost separately — % does not cover localhost on MySQL. Run both:

CREATE USER 'appuser'@'%' IDENTIFIED BY 'your_password';
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'appuser'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'appuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

This covers all bases. Yes, it's two accounts, but MySQL handles them separately — each connection matches the right one automatically.

Prevention tip

When you create a user, always specify the exact host you'll connect from. Never use % unless you absolutely need it — it's a security risk anyway. Test immediately after creating the user by running SHOW DATABASES; from the client. If you see nothing, fix it before you close the root session. That five-second check will save you hours of head-scratching later.