MySQL users and privileges: CREATE USER, GRANT and authentication plugins

MySQL does not have one global list of users. Each account is a pair: 'user'@'host'. 'appuser'@'localhost' and 'appuser'@'10.0.0.%' are two different accounts with their own passwords and privileges. If you leave out the host part, it defaults to '%', which means any host.

1. Create the account

Connect as an administrative user (for example mysql -u root -p) and create the account. IDENTIFIED BY takes the password in clear text and lets the authentication plugin hash it.

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'CHANGE_ME';
CREATE USER 'appuser'@'10.0.0.%' IDENTIFIED BY 'CHANGE_ME';

-- list accounts and their plugins
SELECT user, host, plugin FROM mysql.user ORDER BY user, host;

Create the account first, then grant. In current MySQL versions GRANT does not silently create missing accounts; the manual describes the normal flow as CREATE USER followed by GRANT.

2. Grant only what the application needs

Privileges can be global (*.*), per database (shop.*) or per table (shop.orders). A typical web application needs data access on its own database, not global rights.

GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'appuser'@'localhost';

-- a separate account for schema migrations
CREATE USER 'migrator'@'localhost' IDENTIFIED BY 'CHANGE_ME';
GRANT ALL ON shop.* TO 'migrator'@'localhost';

SHOW GRANTS FOR 'appuser'@'localhost';

GRANT and REVOKE take effect without FLUSH PRIVILEGES. That command is only needed if someone edits the grant tables directly with INSERT, UPDATE or DELETE, which you should avoid.

3. Change, revoke and remove

ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'NEW_CHANGE_ME';
REVOKE DELETE ON shop.* FROM 'appuser'@'localhost';
DROP USER 'appuser'@'10.0.0.%';

MySQL does not revoke privileges automatically when you drop a database or table, so old grants can linger. Review SHOW GRANTS output when you clean up.

Roles

MySQL 8.0 and later support roles: named bundles of privileges you grant to several accounts.

CREATE ROLE 'app_read';
GRANT SELECT ON shop.* TO 'app_read';
GRANT 'app_read' TO 'reportuser'@'10.0.0.%';
SET DEFAULT ROLE ALL TO 'reportuser'@'10.0.0.%';

Authentication plugins: caching_sha2_password vs mysql_native_password

Every account has an authentication plugin, shown in the plugin column of mysql.user.

• caching_sha2_password is the default in MySQL 8.4 (and new accounts get it unless you say otherwise). It needs either a secure connection (TLS, a Unix socket file or shared memory) or an RSA key exchange the first time an account connects after its password is set or the cache is cleared. The mysql client can fetch the server key with --get-server-public-key.
• mysql_native_password is the older SHA-1 based plugin. It was deprecated in MySQL 8.0.34, is disabled by default in MySQL 8.4 and was removed in MySQL 9.0.0.

If an old client library cannot speak caching_sha2_password, the right fix is to upgrade the client or connector. As a temporary measure on 8.4 only, the server-side plugin can be enabled again with mysql_native_password=ON in the [mysqld] section, after which you can create an account that uses it:

CREATE USER 'legacyapp'@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY 'CHANGE_ME';

MariaDB notes

MariaDB uses the same CREATE USER/GRANT model, but its plugins differ. On fresh MariaDB installs (10.4 and later) root@localhost first tries the unix_socket plugin, so the operating system root user logs in with sudo mariadb and no database password. MariaDB’s compatibility notes also say that users created with MySQL’s SHA-256 password algorithm cannot be used in MariaDB.

Good habits

• One account per application and per purpose (app, migrations, backups, monitoring).
• Restrict the host part to the real client network instead of '%'.
• Never give applications ALL ON *.* or WITH GRANT OPTION.
• Keep passwords out of shell history: use -p without a value so the client prompts, or an option file readable only by you.

Official documentation

• MySQL 8.4: CREATE USER statement
• MySQL 8.4: GRANT statement
• MySQL 8.4: Native pluggable authentication (deprecation status)