MySQL User Granted All Privileges but Can't See Tables

You just ran a GRANT ALL PRIVILEGES for a user, but when they log in and try SHOW TABLES, they get ERROR 1142 (42000): command denied or just an empty list. Frustrating, right? The fix is usually one command away.

Immediate Fix

Run this as root or a user with SUPER privilege:

FLUSH PRIVILEGES;

That’s it. Have your user reconnect and try again. If it still fails, the issue is something else – read on.

Why This Happens

MySQL caches privilege data in memory. When you run a GRANT statement, MySQL writes the change to the mysql.user and mysql.tables_priv tables, but it doesn’t automatically reload them into memory for all existing connections. New connections started after the grant will see the updated privileges. But any connection that was already open before the grant still runs against the old cache.

What’s actually happening here is that the user’s session was created before the GRANT took effect, or the grant itself didn’t include the right scope. FLUSH PRIVILEGES forces MySQL to re-read all privilege tables into memory, so every session picks up the change immediately.

The Most Common Mistake: Wrong Grant Scope

The second reason you can’t see tables is that you granted privileges on the wrong database. Look at your original GRANT:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';  -- This is global, works for all databases
GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'localhost'; -- This is database-specific

If you wrote ON mydb.*, the user can only see tables in mydb. If you wrote ON *.*, they see all databases. A common mistake is running:

GRANT ALL PRIVILEGES TO 'user'@'localhost';  -- Missing ON clause! This is invalid syntax in MySQL 8+

MySQL 8+ requires the ON clause explicitly. If you skip it, the statement fails silently or gives a syntax error. Fix it with:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';

Check Effective Privileges

Still stuck? Verify what the user actually has:

SHOW GRANTS FOR 'user'@'localhost';

This shows the exact grants. Look for USAGE – that’s the default grant, which means no real privileges. If you see USAGE ON *.*, your GRANT didn’t apply. Maybe you forgot the TO part, or the user didn’t exist yet. Create the user first:

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

Less Common Variations

1. MySQL 8 caching_sha2_password: If the client library doesn’t support it, the connection fails before any table visibility check. Fix: alter user to use mysql_native_password.
2. Revoked privileges on a specific table: If you granted all on mydb.* but earlier revoked on mydb.sensitive_table, the user sees everything except that one table. Check mysql.tables_priv.
3. View definitions: If the user has SHOW VIEW but not SELECT, they see table names but can’t query them. Run SHOW FULL TABLES to see the type.

Prevention

Always run FLUSH PRIVILEGES right after any GRANT or REVOKE, especially in production scripts. Yes, MySQL says it’s automatic with GRANT, but that’s only for the global cache – existing sessions are not updated. Make it a habit: grant, flush, test.

Also, never grant ALL PRIVILEGES ON *.* to an application user. Give them only what they need: SELECT, INSERT, UPDATE, DELETE on their specific database. Less surface area for mistakes.

Real trigger: You’re setting up a new app user in a Laravel or Django project, run the grant, then the app connects and gets Base table or view not found – even though the tables exist. The session is your problem.