Users and Authentication

Article · 2:00

Creating accounts, what the host part of a username means, and the plugin that broke everybody's driver.

A MySQL account is a user and a host together. 'app'@'localhost' and 'app'@'%' are two different accounts that can have different passwords and different privileges, and this is the source of a great deal of confusion: an account created for localhost cannot connect over the network, and the error says access denied, which sounds like a password problem.

The host part can be a literal name, an IP address, a pattern with %, or % alone for anywhere. Use the narrowest that works. An application on a known server should be pinned to that address rather than allowed from everywhere.

MySQL 8 changed the default authentication plugin to caching_sha2_password, which is better and which older client libraries cannot speak - the symptom is The server requested authentication method unknown to the client. The right fix is to update the driver; mysql_native_password exists as a fallback and should be a temporary one.

Three accounts worth having: one per application, with only the privileges that application needs; one per person who needs direct access, so that SHOW PROCESSLIST and the audit log name a human; and root, which should not be used by anything routine and should not be reachable remotely at all.

-- user AND host together make the account. These are two accounts.
CREATE USER 'app'@'localhost'    IDENTIFIED BY 'a-long-random-password';
CREATE USER 'app'@'10.0.1.%'     IDENTIFIED BY 'a-different-one';

-- An account created for localhost CANNOT connect over the network, and
-- the error is "access denied", which reads like a wrong password.

-- MySQL 8's default. Older drivers answer "authentication method unknown
-- to the client" - update the driver rather than weakening the server.
CREATE USER 'reports'@'%'
  IDENTIFIED WITH caching_sha2_password BY 'another-long-one';

-- A temporary fallback for a legacy client, not a destination.
-- ALTER USER 'legacy'@'%' IDENTIFIED WITH mysql_native_password BY '...';

-- Expiry and reuse policy, per account.
ALTER USER 'reports'@'%'
  PASSWORD EXPIRE INTERVAL 90 DAY
  PASSWORD HISTORY 5
  FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

-- Who exists, and how each one authenticates.
SELECT user, host, plugin, account_locked, password_expired FROM mysql.user;

-- A person, not an application, so the processlist names a human.
CREATE USER 'ada'@'%' IDENTIFIED BY '...' PASSWORD EXPIRE;

DROP USER 'old_app'@'%';