How to Create a MySQL User and Grant Permissions (MySQL 8.4)

In MySQL 8.4, create an account with CREATE USER, assign only the privileges it needs with GRANT, and verify the result with SHOW GRANTS. A newly created account starts with no privileges and a default role of NONE, so creating the login and authorizing it are separate steps.

Need a server to try this on? Antihost offers Linux VPS plans with NVMe storage, full root access and a 14-day free trial.

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

The example gives app_user four data privileges on the shopdb database when connecting from the local server. Replace the account name, host, password, database, and privilege list with values that match your application.

#	Preview	Product	Price
1		Murach's MySQL: Training & Reference	$35.99	Buy on Amazon
2		MySQL Pocket Reference	$2.34	Buy on Amazon
3		MySQL(TM): The Complete Reference	$39.72	Buy on Amazon
4		MySQL Commands Cheat Sheet Reference Guide – Beginner to Advanced | Essential MySQL Commands for...	$14.99	Buy on Amazon
5		Mysql Administrator's Guide an dLanguage Reference	$19.36	Buy on Amazon

As an Amazon Associate I earn from qualifying purchases.

What you are creating

A MySQL account is identified by both a user name and a host. In other words, 'app_user'@'localhost' and 'app_user'@'10.0.0.%' are different account identities even though they use the same user name. The host part determines where a connection may originate.

CREATE USER creates the account itself. It does not give the account permission to read a table, change data, create objects, or perform administrative work. The account begins with no privileges and with no default role. GRANT is the statement that assigns permissions, either directly to the account or to a role that is later assigned to accounts.

#1 Best Overall

Sale

Murach's MySQL: Training & Reference

$35.99

Buy on Amazon

Every GRANT has three essential parts:

• Privilege type: for example, SELECT, INSERT, UPDATE, or DELETE.
• Privilege level: the objects covered by the grant, such as shopdb.* or *.*.
• Recipient: the account or role after the TO keyword.

That separation is why a successful CREATE USER followed by a failed application connection can still mean the account has no usable database access: the authorization step has not been completed, or it was granted to a different host identity.

Plan the account before running SQL

Choose the exact account identity

Decide the user name and the host pattern together. Use an explicit host rather than relying on the default so that the account’s permitted origin is visible in scripts and audits.

If the host portion is omitted in account syntax, MySQL defaults it to %. Writing the host explicitly avoids accidentally creating a broadly reachable identity when a narrower one was intended. A narrower host reduces exposure, but it must match the way the client actually connects.

Choose the smallest useful privilege scope

The object level in the ON clause controls how far the grant reaches. A database-level grant such as shopdb.* covers objects in that database. A global grant such as *.* is broader and should be reserved for administrative accounts that genuinely need server-wide authority.

Prepare a real secret

The password in the examples is illustrative. Substitute a unique secret managed according to your organization’s credential practices. Do not copy the sample password into production or commit it to application source code.

Step 1: create the MySQL account

Run CREATE USER from an account that is allowed to create other accounts. The basic form is:

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

This creates the app_user identity for local connections and sets its authentication password. At this point, the account still has no database privileges.

Rank #2

MySQL Pocket Reference

• Used Book in Good Condition

$2.34

Buy on Amazon

Create a user for a private network

If the application connects from a known network range, put that range in the host part:

CREATE USER 'app_user'@'10.0.0.%' IDENTIFIED BY 'StrongPassword!';

The account created here is not the same as 'app_user'@'localhost'. Granting permissions to one does not grant them to the other. Use the same exact user-and-host pair in later statements.

Why not leave the host out?

Account syntax with no explicit host uses % as the default host. That can be much wider than the intended deployment. An explicit localhost or network range documents the boundary and makes the subsequent GRANT and SHOW GRANTS statements unambiguous.

Step 2: grant database permissions

Grant the permissions the application actually needs. For a shop application that reads and changes rows in shopdb, use:

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

The line breaks are optional; they only make the three parts of the statement easier to see. The privilege list gives the account four specific capabilities at the database level. It does not automatically turn the account into a server administrator.

Read-only access

A reporting process that only reads data can receive a narrower grant:

GRANT SELECT
ON shopdb.*
TO 'report_user'@'localhost';

Keeping the list short makes the intended behavior clear and avoids granting write access to a process that does not need it.

Rank #3

Sale

MySQL(TM): The Complete Reference

$39.72

Buy on Amazon

Read and write access

An application that must create, change, and remove rows can receive the corresponding data privileges:

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

Use the same account identity that was created. If the account was created for 10.0.0.%, the recipient must be 'app_user'@'10.0.0.%', not 'app_user'@'localhost'.

When a broader grant is justified

MySQL also supports an all-privileges grant at a chosen object level. For example:

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

This is still limited to the shopdb database because the object level is shopdb.*. Changing the level to *.* would make the grant global, covering all databases and global objects to which those privileges apply. Treat a global grant as an administrative decision, not as the default way to make an application work.

Read the statement as a sentence

Before executing a grant, read it from left to right: “give these privileges, on these objects, to this exact account.” In GRANT SELECT, INSERT ON shopdb.* TO 'app_user'@'localhost', the privileges are SELECT and INSERT, the level is shopdb.*, and the recipient is 'app_user'@'localhost'. This simple check catches many mistakes before they become an access problem.

Step 3: verify the result with SHOW GRANTS

After creating the account and granting access, ask MySQL to display the account’s effective grant statements:

SHOW GRANTS FOR 'app_user'@'localhost';

The result is returned as GRANT statements. Check all of the following in the output:

Rank #4

MySQL Commands Cheat Sheet Reference Guide – Beginner to Advanced | Essential MySQL Commands for Database Management

$14.99

Buy on Amazon

• The user name and host are the identity you intended.
• The privilege list contains the capabilities the application needs.
• The ON target is shopdb.* or another deliberately chosen level.
• No unexpected global scope such as *.* appears.

Use the same host in SHOW GRANTS FOR that you used in CREATE USER and GRANT. Checking 'app_user'@'localhost' does not display the grants for 'app_user'@'10.0.0.%'.

A complete local-application walkthrough

The following sequence creates a local account, gives it database-level read/write data privileges, and verifies the result:

1. Create the account.

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

2. Grant the required privileges.

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

3. Inspect the grants.

   SHOW GRANTS FOR 'app_user'@'localhost';

4. Compare the output with the application requirement. Confirm that the account is limited to the intended database and that every required privilege is present.

The first statement establishes the login, the second authorizes it, and the third provides an auditable representation of what MySQL has assigned. Do not treat a successful first step as proof that the application is authorized.

Use roles when several users need the same access

Direct grants are manageable for one or two accounts. When several accounts have the same job, a role gives you a shared permission set. Grant privileges to the role, then grant the role to each user.

Create a role and give it database privileges

CREATE ROLE 'shop_rw'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE
ON shopdb.*
TO 'shop_rw'@'localhost';

The role now represents the shared read/write access to shopdb. The privileges are assigned to the role rather than repeated independently for every employee or service account.

Assign the role to a user

GRANT 'shop_rw'@'localhost'
TO 'app_user'@'localhost';

Repeat the role assignment for other accounts that need the same access, using each account’s exact host identity. When the shared permission set changes, update the role’s grant instead of maintaining a separate privilege list for every user.

Check both sides of a role-based setup

For a role design, verify the role’s own grants and the account-to-role assignment. SHOW GRANTS FOR can be run for the role and for the user, using the appropriate user-and-host pair in each statement. This distinguishes “the role has the right permissions” from “the user was actually assigned that role.”

Best Value

Sale

Mysql Administrator's Guide an dLanguage Reference

$19.36

Buy on Amazon

Choose between direct grants and roles

Both approaches still require an explicit privilege list, object level, and recipient. A role changes who receives the grant; it does not remove the need to choose an appropriate database scope or host.

Troubleshoot the common failure modes

The account can connect but cannot use the database

Connection authentication and authorization are separate. Re-run SHOW GRANTS FOR with the exact account identity and verify that a grant exists. A newly created account with no successful GRANT remains unprivileged.

The grant appears to have no effect

Check the host portion first. If the client is matching 'app_user'@'10.0.0.%' but you granted rights to 'app_user'@'localhost', you authorized a different account. Inspect and correct the identity used in all three statements.

The application needs access from more than one location

Decide whether those locations belong to one controlled network range. If so, create and authorize an account whose host pattern represents that range. Do not broaden an existing grant to % merely because the first host choice was wrong; select the narrowest host pattern that covers the real connection sources.

The account has too much authority

Inspect the ON level in the displayed grant. A global *.* grant reaches farther than a database-level shopdb.* grant. Replace a broad design with a database-level grant and a smaller privilege list when the application does not need server-wide access.

A role-based account still lacks the expected access

Verify the role’s grant and the role assignment separately. The role must have the required privileges on the intended object level, and the user must be the exact account named in the role grant. A mismatch in either user or host leaves the connecting identity without the expected permissions.

Security checklist before production use

• Use MySQL 8.4 syntax and verify procedures against the MySQL 8.4 Reference Manual; the evidence for this guide does not establish behavior for MariaDB, MySQL 5.7, or other older versions.
• Write the host explicitly in every account, grant, and verification statement.
• Prefer shopdb.* to *.* when the application only works with one database.
• List only the privileges the workload requires instead of using an all-privileges grant by default.
• Use roles for repeated permission sets so shared access is maintained in one place.
• Run SHOW GRANTS FOR after authorization and check the returned statements, not just whether the SQL command succeeded.
• Keep the example password out of production configuration and source repositories.

Quick reference

The reliable sequence is always the same: identify the connecting host, create the exact account, grant the narrowest useful privileges at the correct object level, and verify the resulting grant statements.

Quick Recap

SaleBestseller No. 1

Murach's MySQL: Training & Reference

$35.99

Bestseller No. 2

MySQL Pocket Reference

Used Book in Good Condition

$2.34

SaleBestseller No. 3

MySQL(TM): The Complete Reference

$39.72

Bestseller No. 4

MySQL Commands Cheat Sheet Reference Guide – Beginner to Advanced | Essential MySQL Commands for Database Management

$14.99

SaleBestseller No. 5

Mysql Administrator's Guide an dLanguage Reference

$19.36

Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.