MySQL CREATE USER

Basic syntax

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Every MySQL user account has two parts: a username and a host. The host controls which machines that user can connect from. You cannot separate these two parts.

Creating a user

The most common case is creating a user for an application connecting from the same server:

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'str0ng_p@ssword';

For a read-only reporting user:

CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'r3port_p@ss';

The user@host format

The host portion of an account determines where connections are accepted from.

| Host value | Meaning | |------------|---------| | localhost | Only connections from the same machine | | % | Connections from any host | | 192.168.1.10 | Only from that specific IP address | | 192.168.1.% | Any IP in that subnet | | app.example.com | Only from that hostname |

Use localhost for applications running on the same server. Use % only when the client connects from a variable or unknown address, and make sure your firewall is doing the work of restricting access.

-- Application on the same machine
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'p@ssword';

-- Application on a specific remote server
CREATE USER 'app_user'@'10.0.1.50' IDENTIFIED BY 'p@ssword';

-- Backup user connecting from anywhere (use with caution)
CREATE USER 'backup_user'@'%' IDENTIFIED BY 'b@ckup_p@ss';

Setting a password

Passwords are set with IDENTIFIED BY. MySQL hashes the password automatically.

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'MyP@ssw0rd!';

To create a user without a password (rare, and not recommended for production):

CREATE USER 'app_user'@'localhost';

Authentication plugins

MySQL 8.0 uses caching_sha2_password as the default authentication plugin. MySQL 5.7 used mysql_native_password.

This matters because some older client libraries do not support caching_sha2_password. If you see connection errors with newer MySQL servers and older clients, switching the plugin usually fixes it.

MySQL 8.0 default (caching_sha2_password):

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'p@ssword';
-- caching_sha2_password is used automatically

Explicitly use mysql_native_password for compatibility:

CREATE USER 'app_user'@'localhost'
  IDENTIFIED WITH mysql_native_password BY 'p@ssword';

MySQL 8.4 note: mysql_native_password was deprecated in MySQL 8.0 and disabled by default in MySQL 8.4. Use caching_sha2_password and update your client libraries instead of switching backward.

IF NOT EXISTS

Running CREATE USER on an existing account raises an error. Use IF NOT EXISTS to make the statement safe to run multiple times (useful in setup scripts):

CREATE USER IF NOT EXISTS 'app_user'@'localhost' IDENTIFIED BY 'p@ssword';

MySQL will skip the creation and emit a warning instead of an error if the account already exists.

Granting privileges

Creating a user does not give them access to any databases. You grant privileges separately with GRANT.

Grant all privileges on a specific database:

GRANT ALL PRIVILEGES ON my_app.* TO 'app_user'@'localhost';

Grant read-only access:

GRANT SELECT ON my_app.* TO 'readonly_user'@'localhost';

Grant write access without delete:

GRANT SELECT, INSERT, UPDATE ON my_app.* TO 'app_user'@'localhost';

Grant access to a single table:

GRANT SELECT ON my_app.orders TO 'readonly_user'@'localhost';

Grant global privileges (use sparingly):

GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost';

After granting privileges, apply them:

FLUSH PRIVILEGES;

Modern MySQL versions reload the grant tables automatically after GRANT, but running FLUSH PRIVILEGES is a safe habit and required if you ever modify the mysql.user table directly.

To allow a user to grant the same privileges to others, add WITH GRANT OPTION:

GRANT ALL PRIVILEGES ON my_app.* TO 'app_user'@'localhost' WITH GRANT OPTION;

Viewing users

List all user accounts on the server:

SELECT user, host FROM mysql.user;

See what privileges a user has:

SHOW GRANTS FOR 'app_user'@'localhost';

Check the authentication plugin in use:

SELECT user, host, plugin FROM mysql.user;

Changing passwords

Use ALTER USER to change a password after the account exists:

ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new_p@ssword';

To change the current user's own password:

ALTER USER CURRENT_USER() IDENTIFIED BY 'new_p@ssword';

Dropping users

Remove an account with DROP USER:

DROP USER 'app_user'@'localhost';

This also removes all privileges for that account. Use IF EXISTS to avoid errors in scripts:

DROP USER IF EXISTS 'app_user'@'localhost';

Common errors

Access denied for user 'x'@'localhost'

This usually means the account does not exist, the password is wrong, or the host does not match. Check that 'username'@'localhost' and 'username'@'127.0.0.1' are treated as separate accounts in MySQL.

Authentication plugin 'caching_sha2_password' cannot be loaded

Your client library is too old to support the MySQL 8 default plugin. Either update the client, or create the user with mysql_native_password as a temporary workaround while you update.

Access denied... (using password: YES) on GRANT

Your current session does not have the GRANT OPTION privilege on the target database. Connect as root or another account that has GRANT OPTION, then run the GRANT statement.

User already exists

CREATE USER without IF NOT EXISTS fails if the account is already there. Add IF NOT EXISTS or check first with SELECT user, host FROM mysql.user.

Quick reference

| Statement | Description | |-----------|-------------| | CREATE USER 'u'@'h' IDENTIFIED BY 'p' | Create a user with a password | | CREATE USER IF NOT EXISTS 'u'@'h' ... | Create only if the account does not exist | | GRANT ALL ON db.* TO 'u'@'h' | Grant all privileges on a database | | GRANT SELECT ON db.* TO 'u'@'h' | Grant read-only access | | FLUSH PRIVILEGES | Reload grant tables | | SHOW GRANTS FOR 'u'@'h' | View a user's privileges | | ALTER USER 'u'@'h' IDENTIFIED BY 'p' | Change a password | | DROP USER 'u'@'h' | Delete an account | | SELECT user, host FROM mysql.user | List all accounts |