MySQL Create User: Syntax, Grants & Examples

Our MySQL database support team creates and audits database accounts on client servers daily. This page documents the CREATE USER statement and the MySQL commands that surround it: host patterns, GRANT syntax, the permission sets used for application, reporting, backup, and replication accounts, password management, and account removal. All statements are as documented in the MySQL 8.4 reference manual.

CREATE USER syntax

The basic form, per the CREATE USER reference:

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'password-here';

Common variants:

-- Fail silently if the account already exists
CREATE USER IF NOT EXISTS 'appuser'@'localhost' IDENTIFIED BY 'password-here';

-- Specify the authentication plugin explicitly
CREATE USER 'appuser'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'password-here';

-- Force a password change at first login
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'temp-password' PASSWORD EXPIRE;

-- Limit resource use
CREATE USER 'reporting'@'10.0.0.%' IDENTIFIED BY 'password-here'
  WITH MAX_QUERIES_PER_HOUR 500 MAX_USER_CONNECTIONS 5;

CREATE USER requires the global CREATE USER privilege, or the INSERT privilege for the mysql system schema. Creating a user grants it no privileges beyond USAGE (connect only); privileges are added separately with GRANT.

The 'user'@'host' part

A MySQL account is the combination of a user name and a host, not the user name alone. 'appuser'@'localhost' and 'appuser'@'10.0.0.5' are two separate accounts with separate passwords and separate privileges. The host part controls where the client may connect from, as documented in Specifying Account Names:

When a client connects, the server picks the most specific matching account row. A connection from the local machine matches 'appuser'@'localhost' before 'appuser'@'%', so two such accounts with different passwords produce access-denied errors that look intermittent. Avoid creating both unless there is a reason.

Authentication plugins

caching_sha2_password has been the default authentication plugin since MySQL 8.0. Accounts created without an IDENTIFIED WITH clause use it. The older mysql_native_password plugin was deprecated in 8.0, disabled by default in 8.4, and removed in MySQL 9.0, per the native pluggable authentication page. Old client libraries and connectors that only speak mysql_native_password fail against 9.x servers; the fix is updating the client library, not downgrading the account. On 8.4, mysql_native_password can still be enabled with --mysql-native-password=ON for transition purposes.

GRANT syntax and privilege levels

The GRANT reference defines four privilege levels, set by the ON clause:

GRANT ALL PRIVILEGES ON *.* TO 'admin2'@'localhost';        -- global
GRANT SELECT, INSERT ON appdb.* TO 'appuser'@'localhost';   -- database
GRANT SELECT ON appdb.orders TO 'reporting'@'10.0.0.%';     -- table
GRANT SELECT (id, email) ON appdb.users TO 'audit'@'localhost'; -- column

Rules that matter in practice:

• The account must already exist. GRANT does not create accounts (the implicit-create behavior was removed in MySQL 8.0; it now returns ERROR 1410 when NO_AUTO_CREATE_USER conditions are not met — use CREATE USER first).
• Granting requires holding the same privileges yourself, plus the GRANT OPTION privilege, or the UPDATE privilege on the grant tables.
• WITH GRANT OPTION lets the account grant its own privileges onward. Application accounts do not need it.
• Database-level wildcards exist (ON app%.*) but the partial_revokes system variable changes how they behave; explicit database names are simpler to audit.

Common permission sets

FLUSH PRIVILEGES: when it is needed

Account-management statements — CREATE USER, GRANT, REVOKE, ALTER USER, DROP USER — take effect immediately. The server reloads the grant tables into memory on its own after these statements. FLUSH PRIVILEGES is required only after modifying the grant tables directly with INSERT, UPDATE, or DELETE against the mysql schema, as stated in the privilege change documentation. Running it after every GRANT does nothing except reload tables that are already current. Direct edits to the grant tables are themselves discouraged; use the account-management statements.

Viewing grants

SHOW GRANTS FOR 'appuser'@'10.0.0.%';   -- one account
SHOW GRANTS;                             -- current account
SELECT user, host, plugin FROM mysql.user;  -- list all accounts

SHOW GRANTS output is valid GRANT syntax, which makes it the fastest way to copy a permission set to a new account: run SHOW GRANTS on the old account, edit the account name, execute.

Changing passwords

ALTER USER is the documented statement for password changes (SET PASSWORD exists but ALTER USER is preferred):

ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'new-password';
ALTER USER 'appuser'@'localhost' PASSWORD EXPIRE;          -- force change at next login
ALTER USER 'appuser'@'localhost' ACCOUNT LOCK;             -- disable without dropping

Password strength rules come from the validate_password component, installed with INSTALL COMPONENT 'file://component_validate_password';. It enforces length, mixed case, numeric, and special-character policies through the validate_password.* system variables (LOW, MEDIUM, STRONG policy levels), per the validate_password documentation. When a CREATE USER or ALTER USER statement fails with ERROR 1819 Your password does not satisfy the current policy requirements, this component is the reason; check SHOW VARIABLES LIKE 'validate_password%';.

Removing users and revoking privileges

REVOKE INSERT, UPDATE, DELETE ON appdb.* FROM 'appuser'@'10.0.0.%';  -- reduce
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'olduser'@'%';              -- strip everything
DROP USER 'olduser'@'%';                                             -- remove the account
DROP USER IF EXISTS 'olduser'@'%';

DROP USER removes the account and its privilege rows, but it does not close the account's open sessions. An existing connection continues working until it disconnects; kill its sessions (SHOW PROCESSLIST, then KILL id) when removal must be immediate. Objects the dropped user created (tables, views, stored procedures with DEFINER set to it) remain and can produce definer-not-found errors later; reassign definers before dropping accounts that own routines.

Remote access checklist

Three layers must all permit the connection. Check them in this order:

1. bind-address. The server must listen on a reachable interface. bind_address = 127.0.0.1 in mysqld configuration limits the server to local connections; set it to the server's LAN address or 0.0.0.0 and restart mysqld. Verify with SHOW VARIABLES LIKE 'bind_address';.
2. Firewall. Port 3306 must be open from the client network: ufw allow from 10.0.0.0/24 to any port 3306 or the firewalld/cloud-security-group equivalent. Do not open 3306 to the internet; restrict by source.
3. Account host. The account's host part must match the client address. 'appuser'@'localhost' cannot connect remotely regardless of the first two layers; create 'appuser'@'10.0.0.%' or the appropriate subnet.

Test from the client machine with mysql -h server-ip -u appuser -p. A timeout points at layers 1-2; an immediate error points at layer 3 or the password. Tools such as phpMyAdmin sit behind the same three layers when hosted on a separate machine.

Common errors

When to hand it over

Account sprawl — dozens of 'user'@'%' rows with ALL PRIVILEGES, no record of which application uses which — is the normal state of a MySQL server that has passed through several administrators. Our MySQL support team audits accounts against actual connection logs, rebuilds least-privilege grants, and handles user management as part of ongoing server administration. A DBA is available on live chat 24/7.