00:00

Help Us Improve

We're sorry this tutorial wasn't helpful. Please tell us what we can do better.

MySQL Create User

Last updated: 4 min read

Every connection to MySQL logs in as an account. Using root for everything is risky: one leaked password or one buggy query can damage every database. CREATE USER creates separate accounts for each application and person, and GRANT gives each one only the rights it needs.

1. Accounts Are user@host

A MySQL account is a user name plus a hostThe account 'report'@'10.0.0.%' means user report connecting from any address in 10.0.0.x. The same user name from another host is a different account, or is refused. After logging in, privileges decide what the account may do.'report'@'10.0.0.%'user namehost it may connect from1. Authenticatepassword / plugin2. AuthoriseGRANTed privileges'localhost'same machine only'10.0.0.%'a network range'%'any host (be careful)
Figure 1: MySQL identifies an account by both name and host, then checks its privileges.
syntax.sql 
CREATE USER [IF NOT EXISTS] 'user_name'@'host'
  IDENTIFIED BY 'password'
  [DEFAULT ROLE role]
  [WITH MAX_QUERIES_PER_HOUR n ...]      -- resource limits come before password options
  [PASSWORD EXPIRE INTERVAL n DAY]
  [FAILED_LOGIN_ATTEMPTS n PASSWORD_LOCK_TIME n]
  [ACCOUNT LOCK | ACCOUNT UNLOCK];

2. Creating a User

create.sql 
CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'Sh0p#App2026';
SELECT user, host, plugin, account_locked FROM mysql.user WHERE user = 'shop_app';

Result: 1 row

userhostpluginaccount_locked
shop_applocalhostcaching_sha2_passwordN
MySQL 8+ uses the secure caching_sha2_password plugin by default. Very old client libraries may need an upgrade to connect.

3. A New User Has No Privileges

query.sql 
SHOW GRANTS FOR 'shop_app'@'localhost';

Result: 1 row

Grants for shop_app@localhost
GRANT USAGE ON *.* TO `shop_app`@`localhost`

USAGE means "can log in, nothing more". Logged in as shop_app, reading the orders table fails:

as shop_app 
SELECT * FROM shop.orders;
Output 
ERROR 1142 (42000): SELECT command denied to user 'shop_app'@'localhost' for table 'orders'

4. Granting Privileges

grant.sql 
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'localhost';
SHOW GRANTS FOR 'shop_app'@'localhost';

Result: 2 rows

Grants for shop_app@localhost
GRANT USAGE ON *.* TO `shop_app`@`localhost`
GRANT SELECT, INSERT, UPDATE, DELETE ON `shop`.* TO `shop_app`@`localhost`
as shop_app 
SELECT * FROM shop.orders;

Result: 2 rows

idtotal
1499.00
21299.00
as shop_app 
DROP TABLE shop.orders;
Output 
ERROR 1142 (42000): DROP command denied to user 'shop_app'@'localhost' for table 'orders'
LevelSyntaxExample
GlobalON *.*Administrators only
DatabaseON shop.*An application's own database
TableON shop.ordersA reporting user for one table
ColumnGRANT SELECT (id, total) ON shop.ordersHide sensitive columns

5. IF NOT EXISTS and Duplicate Users

query.sql 
CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'x';
Output 
ERROR 1396 (HY000): Operation CREATE USER failed for 'shop_app'@'localhost'
query.sql 
CREATE USER IF NOT EXISTS 'shop_app'@'localhost' IDENTIFIED BY 'x';
SHOW WARNINGS;
Output 
Query OK, 0 rows affected, 1 warning

Result: 1 row

LevelCodeMessage
Note3163Authorization ID 'shop_app'@'localhost' already exists.

Note that the same name with another host is a different account:

two-hosts.sql 
CREATE USER 'shop_app'@'10.0.0.%' IDENTIFIED BY 'Sh0p#Net2026';
SELECT user, host FROM mysql.user WHERE user = 'shop_app' ORDER BY host;

Result: 2 rows

userhost
shop_app10.0.0.%
shop_applocalhost

6. Roles: Reusable Groups of Privileges

Instead of granting the same list to every analyst, create a role once and grant the role:

roles.sql 
CREATE ROLE 'reader';
GRANT SELECT ON shop.* TO 'reader';
CREATE USER 'meera'@'localhost' IDENTIFIED BY 'Me3ra#2026' DEFAULT ROLE 'reader';
GRANT 'reader' TO 'meera'@'localhost';
SHOW GRANTS FOR 'meera'@'localhost' USING 'reader';

Result: 3 rows

Grants for meera@localhost
GRANT USAGE ON *.* TO `meera`@`localhost`
GRANT SELECT ON `shop`.* TO `meera`@`localhost`
GRANT `reader`@`%` TO `meera`@`localhost`

7. Password Expiry, Lockout and Limits

policies.sql 
CREATE USER 'intern'@'localhost' IDENTIFIED BY 'Int3rn#2026'
  WITH MAX_QUERIES_PER_HOUR 500
  PASSWORD EXPIRE INTERVAL 90 DAY
  FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1;
SELECT user, password_lifetime, max_questions FROM mysql.user WHERE user = 'intern';

Result: 1 row

userpassword_lifetimemax_questions
intern90500
lock.sql 
ALTER USER 'intern'@'localhost' ACCOUNT LOCK;
SELECT user, account_locked FROM mysql.user WHERE user = 'intern';

Result: 1 row

useraccount_locked
internY

A locked account can't log in until ALTER USER … ACCOUNT UNLOCK.

8. Best Practices

  • One account per application and per person; never share root.
  • Restrict the host: 'app'@'10.0.0.%' rather than '%'.
  • Grant the minimum privileges (least privilege), at database or table level.
  • Use roles for teams; use strong passwords and consider the validate_password component.
  • Store application passwords in a secrets manager or environment variables, not in source code.

9. Summary

  • An account is 'user'@'host'; CREATE USER … IDENTIFIED BY creates it with a password.
  • New accounts have no privileges (USAGE); add them with GRANT … ON db.* TO ….
  • Roles bundle privileges; policies add expiry, lockout and limits.
  • Follow least privilege and restrict hosts.