MySQL Create User: GRANT, IDENTIFIED BY, and Best Practices

MySQL create user with CREATE USER 'name'@'host' IDENTIFIED BY 'password';, then GRANT privileges with GRANT SELECT, INSERT ON db.* TO 'name'@'host';. The team that follows least-privilege grants specific permissions, not ALL PRIVILEGES. The team that restricts hosts ('name'@'10.0.0.%') limits network access.

Table of contents

• CREATE USER syntax
• GRANT privileges
• FLUSH PRIVILEGES
• REVOKE for cleanup
• Show grants for a user
• Authentication plugin
• Best practices
• FAQ

CREATE USER syntax

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';

-- Multiple hosts
CREATE USER 'app_user'@'%' IDENTIFIED BY 'pwd';
CREATE USER 'app_user'@'10.0.0.%' IDENTIFIED BY 'pwd';

The host part is mandatory: 'user'@'host'. 'user'@'localhost' is local Unix socket only. 'user'@'%' is any host. The team that uses specific IPs/CIDRs has network-restricted users.

GRANT privileges

After creating the user, grant privileges:

-- Specific privileges on a database
GRANT SELECT, INSERT, UPDATE ON appdb.* TO 'app_user'@'%';

-- All privileges (avoid in production)
GRANT ALL PRIVILEGES ON appdb.* TO 'app_user'@'%';

-- Read-only
GRANT SELECT ON appdb.* TO 'readonly'@'%';

The team that grants specific permissions has least-privilege. The team that grants ALL has admin-equivalent access.

FLUSH PRIVILEGES

After creating users or granting privileges:

FLUSH PRIVILEGES;

Usually not needed - MySQL reloads grant tables automatically. The team that flushes manually has belt-and-suspenders for non-standard cases.

REVOKE for cleanup

Remove specific privileges:

REVOKE INSERT ON appdb.* FROM 'app_user'@'%';

The team that revokes as part of offboarding has clean permission revocations.

Show grants for a user

SHOW GRANTS FOR 'app_user'@'%';

The team that audits users uses SHOW GRANTS to see exactly what each has.

Authentication plugin

MySQL 8.0 default is caching_sha2_password. MySQL 5.7 default was mysql_native_password. The team that has older clients specifies the plugin:

CREATE USER 'app'@'%' IDENTIFIED WITH mysql_native_password BY 'pwd';

The team that uses caching_sha2_password has stronger auth; legacy clients may need mysql_native_password.

Best practices

1. Restrict hosts: don’t use '%' for production DBs.
2. Use specific privileges: SELECT, INSERT not ALL.
3. Use strong passwords (or password manager-generated).
4. Rotate passwords regularly.
5. Audit with SHOW GRANTS.
6. Document who has access to what.
7. Use separate users for separate apps (isolation).

FAQ

What’s the difference between ‘user’@‘localhost’ and ‘user’@’%’?

‘localhost’ means local Unix socket only (no network). ’%’ means any host (network). The team that uses specific hosts has network-restricted users.

Do I need to run FLUSH PRIVILEGES after CREATE USER?

Usually no - MySQL reloads grant tables automatically. The team that flushes manually has paranoia mode, which is fine.

How do I create a user with no password (insecure)?

CREATE USER 'name'@'%'; - empty password. Only acceptable for local dev. The team that uses this in production has a security incident.

How do I change a user’s password?

ALTER USER 'name'@'host' IDENTIFIED BY 'new_password';. The team that has password rotation uses this.

Can I see who is currently connected?

SHOW PROCESSLIST; shows active connections. The team that monitors connections runs this periodically.

If you are sizing the infrastructure for the kind of project this post covers, the RunxBuild hosting calculator is the right place to model the line items. The compute, the memory, the storage, the bandwidth, the database - each one is a separate number, and the team’s mental model for the platform is the sum of those numbers. The RunxBuild dashboard is where the team sees the actual usage in one place.

Useful related references:

• Create User In Postgres
• Create User Postgres
• Database/Introduction