MySQL Create User – CREATE USER, GRANT, Roles, and Host Examples | DbSchema

Table of Contents

1. Quick syntax
2. MySQL create user syntax by version
3. Create a user for localhost
4. Grant database privileges
5. Allow remote access safely
6. Use roles in MySQL 8.0 and 8.4
7. Show grants, revoke access, and drop a user
8. Use the account in DbSchema
9. FAQ

In MySQL, an account is identified by both the username and the host, such as 'app_user'@'localhost' or 'report_user'@'10.0.%'. That is why user alone is not enough when you create or grant access.

If you are still trying to recover the root account, read MySQL default username and password first.

Quick syntax

The most common pattern is:

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword!';
GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO 'app_user'@'localhost';

You do not need FLUSH PRIVILEGES after CREATE USER or GRANT. MySQL applies those account-management statements immediately.

MySQL create user syntax by version

Related grant syntax is stable across these versions:

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

Create a user for localhost

First open MySQL:

mysql -u root -p

Then create a local application user:

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword!';

Why use localhost?

• it limits the account to local connections
• it is safer than allowing the user from any host
• it is often perfect for a web app or local development stack running on the same machine

Grant database privileges

Most applications do not need ALL PRIVILEGES ON *.*. Grant only what the workload needs.

Read/write access to one database

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

Read-only reporting user

CREATE USER 'report_user'@'localhost' IDENTIFIED BY 'StrongPassword!';
GRANT SELECT ON analytics.* TO 'report_user'@'localhost';

Full privileges on a single schema

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

That is still far safer than granting on *.*, which covers every database on the server.

Allow remote access safely

MySQL lets you specify the host part precisely.

Specific subnet

CREATE USER 'api_user'@'10.0.%' IDENTIFIED BY 'StrongPassword!';
GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO 'api_user'@'10.0.%';

Any host

CREATE USER 'integration_user'@'%' IDENTIFIED BY 'StrongPassword!';
GRANT SELECT ON integrationdb.* TO 'integration_user'@'%';

Use '%' only when you truly need it. In most cases, a hostname, IP, or subnet pattern is better.

If the user will connect from a design tool, creating a read-only or schema-specific account is usually enough for browsing and documentation.

Use roles in MySQL 8.0 and 8.4

Roles were introduced in MySQL 8.0, so this section does not apply to 5.7.

CREATE ROLE 'reporting_readonly';
GRANT SELECT ON analytics.* TO 'reporting_readonly';

CREATE USER 'analyst'@'%' IDENTIFIED BY 'StrongPassword!';
GRANT 'reporting_readonly' TO 'analyst'@'%';
SET DEFAULT ROLE 'reporting_readonly' TO 'analyst'@'%';

Roles are useful when several users need the same privilege bundle. Instead of repeating grants for every account, you assign the role once and reuse it.

Show grants, revoke access, and drop a user

Check what a user can do

SHOW GRANTS FOR 'app_user'@'localhost';

Revoke a privilege

REVOKE INSERT, UPDATE, DELETE
ON shopdb.*
FROM 'app_user'@'localhost';

Remove the account

DROP USER 'app_user'@'localhost';

Those three statements are the basics of day-to-day MySQL account maintenance.

Use the account in DbSchema

DbSchema fits naturally into a least-privilege workflow:

1. create a dedicated MySQL user for design, reporting, or development
2. connect with the MySQL JDBC driver
3. follow the Connect to Database guide
4. browse tables and relationships in the diagram view
5. publish interactive schema documentation without sharing the root account

That way, the team can inspect schemas, build ER diagrams, and document the database without reusing the most powerful login on the server.

If your next step is understanding the structure itself, continue with Create ER Diagrams for MySQL.

FAQ

How do I create a MySQL user for localhost?

Use:

CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'StrongPassword!';

How do I grant privileges to a MySQL user?

Use GRANT privileges ON database_name.* TO 'user_name'@'host';.

Do I need FLUSH PRIVILEGES after CREATE USER or GRANT?

No. MySQL applies those account-management statements immediately. FLUSH PRIVILEGES is only needed after editing the mysql grant tables directly.

Can I create roles in MySQL 5.7?

No. Roles are available in MySQL 8.0 and 8.4, not in 5.7.

Should I grant ALL PRIVILEGES ON *.* to application users?

Usually no. It is better to grant only the needed privileges on the specific database or schema.

How do I see what privileges a user already has?

Use SHOW GRANTS FOR 'user_name'@'host';.