MySQL CREATE USER: Accounts, Hosts, Passwords

A MySQL account is made of two parts: a user name and a host the user may connect from. 'asha'@'localhost' and 'asha'@'%' are two different accounts, possibly with different passwords and privileges.

query.sql

SQL

CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'S3cret!app#Pass';
CREATE USER 'shop_app'@'10.0.0.%' IDENTIFIED BY 'An0ther!Strong#Pass';

SELECT user, host, plugin FROM mysql.user WHERE user = 'shop_app' ORDER BY host;

Output

OUTPUT

+----------+-----------+-----------------------+
| user     | host      | plugin                |
+----------+-----------+-----------------------+
| shop_app | 10.0.0.%  | caching_sha2_password |
| shop_app | localhost | caching_sha2_password |
+----------+-----------+-----------------------+
2 rows in set

The host part

Host value	Meaning
`'localhost'`	Connections from the same machine (through a socket, or `127.0.0.1` in some setups)
`'127.0.0.1'`	Local TCP connections
`'10.0.0.5'`	One IP address
`'10.0.0.%'`	Any address in that range (`%` is a wildcard)
`'%'`	Any host. Avoid when you can
`'app.example.com'`	A host name (needs DNS lookups; see `skip_name_resolve`)

When a client connects, MySQL picks the most specific matching account. Make the host as narrow as your network allows. An application account that can connect only from the web server’s address is much safer than one reachable from anywhere.

Passwords and the authentication plugin

An account has an authentication plugin that decides how the password is checked. The default in MySQL 8.0 and later is caching_sha2_password, which stores a salted SHA-256 hash and is checked with a challenge, so the password is never sent in the clear (use TLS as well, see Encrypted Connections).

query.sql

SQL

SELECT user, host, plugin, password_lifetime, account_locked FROM mysql.user WHERE user = 'shop_app' AND host = 'localhost';

Output

OUTPUT

+----------+-----------+-----------------------+-------------------+----------------+
| user     | host      | plugin                | password_lifetime | account_locked |
+----------+-----------+-----------------------+-------------------+----------------+
| shop_app | localhost | caching_sha2_password |              NULL | N              |
+----------+-----------+-----------------------+-------------------+----------------+
1 row in set

The older plugin mysql_native_password (SHA-1 based) is not loaded by default in MySQL 8.4 and was removed in MySQL 9.0. An attempt to use it fails:

query.sql

SQL

CREATE USER 'old_app'@'localhost' IDENTIFIED WITH mysql_native_password BY 'x';

Output

OUTPUT

ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded

(Shown from real MySQL 8.4.11 and 9.7.1 servers. MySQL 8.0 accepts the statement.) Why does this matter? Old drivers and old PHP versions that cannot speak caching_sha2_password fail to connect with errors such as “The server requested authentication method unknown to the client”. The fix is to upgrade the driver (PHP 7.4 and later with mysqlnd support caching_sha2_password), not to downgrade the server’s security.

Password rules

query.sql

SQL

CREATE USER 'report_user'@'localhost' IDENTIFIED BY 'R3port!er#Pass'
    PASSWORD EXPIRE INTERVAL 180 DAY
    PASSWORD HISTORY 5
    FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

SELECT user, password_lifetime, JSON_EXTRACT(user_attributes, '$.Password_locking') AS locking FROM mysql.user WHERE user = 'report_user';

Output

OUTPUT

+-------------+-------------------+------------------------------------------------------------+
| user        | password_lifetime | locking                                                    |
+-------------+-------------------+------------------------------------------------------------+
| report_user |               180 | {"failed_login_attempts": 5, "password_lock_time_days": 1} |
+-------------+-------------------+------------------------------------------------------------+
1 row in set

Option	Meaning
`PASSWORD EXPIRE INTERVAL n DAY`	The password must be changed every n days
`PASSWORD EXPIRE`	The password is expired now: the user must change it at first login
`PASSWORD HISTORY n`	The last n passwords cannot be reused
`FAILED_LOGIN_ATTEMPTS n PASSWORD_LOCK_TIME d`	After n wrong passwords, the account is locked for d days (8.0.19 and later)
`ACCOUNT LOCK` / `ACCOUNT UNLOCK`	Block or allow logins

Do not set password expiry on application accounts without a plan: when the password expires, your application stops working. Expiry suits human accounts. For application accounts, rotate passwords with the dual-password feature (below).

Changing and rotating passwords

query.sql

SQL

1
2

ALTER USER 'shop_app'@'localhost' IDENTIFIED BY 'N3w!Secret#Value' RETAIN CURRENT PASSWORD;
SELECT user, host, JSON_EXTRACT(user_attributes, '$.additional_password') IS NOT NULL AS has_second_password FROM mysql.user WHERE user = 'shop_app' AND host = 'localhost';

Output

OUTPUT

+----------+-----------+---------------------+
| user     | host      | has_second_password |
+----------+-----------+---------------------+
| shop_app | localhost |                   1 |
+----------+-----------+---------------------+
1 row in set

RETAIN CURRENT PASSWORD keeps the old password working as a second password. You can then roll the new password out to all application servers, one at a time, without downtime. When all of them use the new one:

query.sql

SQL

1
2

ALTER USER 'shop_app'@'localhost' DISCARD OLD PASSWORD;
SELECT JSON_EXTRACT(user_attributes, '$.additional_password') IS NOT NULL AS has_second_password FROM mysql.user WHERE user = 'shop_app' AND host = 'localhost';

Output

OUTPUT

+---------------------+
| has_second_password |
+---------------------+
|                   0 |
+---------------------+
1 row in set

Locking and dropping

query.sql

SQL

ALTER USER 'report_user'@'localhost' ACCOUNT LOCK;
SELECT user, account_locked FROM mysql.user WHERE user = 'report_user';
DROP USER 'report_user'@'localhost';
DROP USER IF EXISTS 'old_account'@'localhost';

Output

OUTPUT

+-------------+----------------+
| user        | account_locked |
+-------------+----------------+
| report_user | Y              |
+-------------+----------------+
1 row in set

A locked account cannot log in, but keeps its privileges, useful when someone leaves or an account is suspect. DROP USER removes the account and its privileges. Check that no application or DEFINER of a view or routine depends on it first (Views).

Text

PLAIN

1
2

ERROR 1045 (28000): Access denied for user 'shop_app'@'localhost' (using password: YES)    <- wrong password
ERROR 3118 (HY000): Access denied for user 'locked_user'@'localhost'. Account is locked.    <- locked account

(Both are real messages from MySQL 8.4.) Notice that the message for a wrong password and for an unknown user is the same, so that an attacker cannot tell which usernames exist.

The root account

root is the all-powerful administrator. Use it only for administration, from the server itself. Never use root in an application, and never leave it with an empty or a default password (Application Database Users, Security Basics).

Do not edit mysql.user directly

Always use CREATE USER, ALTER USER, GRANT and DROP USER. They update the data dictionary correctly, and refresh the server’s in-memory copy. (The tables in the mysql schema are managed by the server. Updating them by hand is unsupported, and in MySQL 8 the grant tables are InnoDB tables that the server expects to control.)

Account names in practice

Account	Purpose
`shop_app`@`10.0.0.%`	The web application
`shop_migrate`@`10.0.0.%`	Schema changes (more privileges, used only during deployments)
`shop_report`@`10.0.0.%`	Read-only reporting
`shop_backup`@`localhost`	Backups
`dba_asha`@`%` or similar	One named account per administrator, so that logs show who did what

lightbulb

One account per purpose

Do not share one account between the app, reports and people. A separate account per purpose can be granted exactly what it needs, rotated independently, and traced in the logs.

The host part

Passwords and the authentication plugin

Password rules

Changing and rotating passwords

Locking and dropping

What a login check looks like

The root account

Do not edit mysql.user directly

Account names in practice

One account per purpose