Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

SHOW DATABASES lists only some of the databases that exist

ErrorSHOW DATABASES does not list all of my databases

Short answer

SHOW DATABASES is filtered by privileges: an account sees only the databases it holds some privilege on, unless it has the global SHOW DATABASES privilege. So the missing databases are hidden from this account, not gone. Check which account the server matched, what it was granted, and whether a role that carries the grants is active.

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Ask the server which account it matched and what that account may do. The two answers separate the causes below.

    SELECT CURRENT_USER();
    SHOW GRANTS;
    SELECT CURRENT_ROLE();
  2. 2Match what you see, then jump to its fix

    1. 1SHOW GRANTS lists grants for one or two named databases and no global SHOW DATABASESThe account has privileges on only some of the databasesFix
    2. 2SHOW GRANTS FOR the account USING the role shows the privileges, but CURRENT_ROLE() returns NONEThe privileges come from a role that is not active in your sessionFix
    3. 3CURRENT_USER() returns a value such as @localhost, with an empty user nameThe server matched a different account than the one you meantFix

What does the error look like?

  • SHOW DATABASES does not list all of my databases
  • A database is visible to an administrator account and missing for the application user

Cause 1 of 3

The account has privileges on only some of the databases

1Check

An account without the global SHOW DATABASES privilege sees only the databases on which it holds some privilege. A grant on one database makes that one database visible and nothing else.

SHOW GRANTS FOR CURRENT_USER;
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

2Fix

Decide what the account should see. Either grant a privilege on each missing database, or, if seeing every database name is acceptable, give the account the global SHOW DATABASES privilege. Run the GRANT as an administrator. Note the opposite risk: any static global privilege also shows every database name, except databases restricted by partial revokes.

GRANT SHOW DATABASES ON *.* TO 'appuser'@'localhost';
-- or one database at a time
GRANT SELECT ON reports.* TO 'appuser'@'localhost';

3Verify

Reconnect as that account and list again.

SHOW DATABASES;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The privileges come from a role that is not active in your session

1Check

In MySQL 8, a role that has been granted to an account is not active by default when the account connects. Until it is active, its privileges do not count, and the databases it covers stay hidden.

SELECT CURRENT_ROLE();
SHOW GRANTS FOR 'appuser'@'localhost';
SHOW GRANTS FOR 'appuser'@'localhost' USING 'app_read';

2Fix

Make the roles default roles, so they are active at each login, or switch the roles on for the current session. A server-wide alternative is to enable the activate_all_roles_on_login system variable.

SET DEFAULT ROLE ALL TO 'appuser'@'localhost';
-- in an open session, without reconnecting
SET ROLE DEFAULT;

3Verify

Reconnect, check that CURRENT_ROLE() lists the role, and list the databases.

SELECT CURRENT_ROLE();
SHOW DATABASES;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

The server matched a different account than the one you meant

1Check

The server sorts its account rows and takes the first one that matches the connecting host and user name. Rows with a blank user name sort after named users only for the same host value. A blank-user row for localhost therefore beats a named user whose host is %, and you are treated as the anonymous user with the anonymous account's privileges.

SELECT CURRENT_USER();
SELECT User, Host FROM mysql.user ORDER BY User, Host;

2Fix

Have an administrator remove the anonymous row, or create the named account for the exact host you connect from, so it is the most specific match. Then connect again.

DROP USER ''@'localhost';

3Verify

After reconnecting, CURRENT_USER() shows the account you meant.

SELECT CURRENT_USER();

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Give each application account the grants it needs by name, and write them in one script that is kept with the project.
  • When you use roles, set the default roles in the same script that grants them, and test with a fresh login.
  • Remove anonymous accounts on every new server, and look at CURRENT_USER() whenever privileges are not what you expect.
  • Treat the global SHOW DATABASES privilege as a visibility grant on all database names, and give it only to accounts that may see them.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.