Remove anonymous users and the test database

The fix

-- Anonymous accounts: no username, no password
SELECT user, host FROM mysql.user WHERE user = '';
DROP USER ''@'localhost';
DROP USER ''@'%';           -- if present

-- The test database, writable by anyone
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE db IN ('test', 'test\\_%');

-- Remote root
SELECT user, host FROM mysql.user WHERE user = 'root' AND host NOT IN ('localhost','127.0.0.1','::1');

FLUSH PRIVILEGES;

Why it matters

Anonymous accounts have an empty username and an empty password. Anyone who can reach the server connects as ''@'localhost' with no credentials at all. Historically MySQL created them at install so that a fresh server was immediately usable, and the account has a nasty second property: because MySQL sorts accounts by specificity, an anonymous entry for '%' can shadow a real named account, so myapp connects as anonymous and then gets permission errors that make no sense.

The test database was created with grants in mysql.db allowing any user — including anonymous — to create tables and write data in it. That’s a foothold: somewhere to stage data, somewhere to write, and on an install with FILE privileges intact, somewhere to build from.

This is mostly about upgraded and legacy servers

Be clear about scope, because a fresh install may already be fine:

• A fresh MySQL 8.4 initialised with mysqld --initialize creates no anonymous users and no test database. Nothing to do.
• A server upgraded from 5.x, or built from an old image, or provisioned by an automation template written years ago, very often still has them. Upgrades don’t remove accounts.
• MariaDB on some packagings still creates them.

So the version number doesn’t answer this — the mysql.user table does. That’s a three-second query and worth running even if you’re confident.

mysql_secure_installation is not a complete answer

mysql_secure_installation is the standard recommendation and it does handle these. Two caveats worth knowing:

It is interactive and one-shot, so it can’t be part of configuration management, and nothing re-checks afterwards. An account added later by a migration script or a restored dump is invisible to it.

It also only ever ran on the server where someone remembered to run it. On a fleet, “we ran mysql_secure_installation” is a claim about one afternoon, not an ongoing property. The queries in the verify block are what you want in a periodic check.

Verify it worked

The complete audit — every account with an empty username, empty password, or a wildcard host:

SELECT user, host, plugin,
       IF(authentication_string = '', 'EMPTY PASSWORD', 'set') AS pw
FROM mysql.user
WHERE user = ''
   OR authentication_string = ''
   OR host = '%'
ORDER BY user, host;

Any row with user empty is an anonymous account. Any row with EMPTY PASSWORD on a plugin that expects one is an account anybody can use.

Note unix_socket accounts legitimately show an empty authentication_string — that’s not a finding, that’s the plugin working. Check the plugin column before panicking.

Confirm test is gone, including its lingering grants:

SHOW DATABASES LIKE 'test';
SELECT * FROM mysql.db WHERE db LIKE 'test%';

Both should be empty. The mysql.db grants outlive the database, so dropping the database alone leaves the permission behind for the next thing named test.

Then prove it from outside:

mysql -h your.db -u '' --connect-timeout=5 -e 'SELECT CURRENT_USER()'

Expect access denied.

Before you apply this

Dropping an anonymous account can break an application that has been silently relying on it — and neither you nor the app’s author necessarily knows.

The shadowing behaviour is what makes this non-obvious. If ''@'localhost' exists, a connection from localhost as myapp may have been matching the anonymous entry all along, because MySQL picks the most specific host match first and an empty user matches anything. The app works, connects as anonymous, and drops dead the moment you remove it.

Check what a connection actually resolves to before you drop anything:

-- run this AS the application, from where it connects
SELECT USER(), CURRENT_USER();

USER() is what the client asked for; CURRENT_USER() is the account MySQL actually matched. If they differ, something is being shadowed — that’s the whole diagnostic, and it’s the one people miss.

Dropping test is nearly always safe, but check nothing points at it:

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test';

An empty test database with no tables can go without ceremony. One with tables in it means somebody used it for something real, and you should find out what before dropping it.

Related

• Authentication plugins — for the accounts you keep.
• Grant least privilege — what those accounts should be able to do.