Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Creating a MySQL user with a password fails on MySQL 8 and later

ErrorGRANT ALL ON db.* TO 'app'@'%' IDENTIFIED BY 'secret' is rejected with a syntax error

Short answer

MySQL 8.0 removed GRANT as a way to create users, the PASSWORD() function and IDENTIFIED BY PASSWORD. Create the account with CREATE USER ... IDENTIFIED BY, give privileges with a separate GRANT, and change passwords with ALTER USER. A user that still cannot log in is usually a host mismatch.

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Confirm the server version, then list the accounts that match the user name. MySQL 8 matches both user and host, so the host column matters.

    SELECT VERSION();
    SELECT user, host FROM mysql.user WHERE user = 'app';
  2. 2Match what you see, then jump to its fix

    1. 1A one-line GRANT with IDENTIFIED BY (copied from a 5.x tutorial) returns a syntax errorGRANT ... IDENTIFIED BY was used to create the accountFix
    2. 2A statement that wraps the password in PASSWORD('...') failsPASSWORD() or SET PASSWORD = PASSWORD() was usedFix
    3. 3A CREATE USER or GRANT that carries a hash after IDENTIFIED BY PASSWORD failsIDENTIFIED BY PASSWORD 'hash' was used with a stored hashFix
    4. 4The account is created without errors but login fails; the list from the first step shows only 'localhost' or a different hostThe account was created for a different host than the client usesFix

What does the error look like?

  • GRANT ALL ON db.* TO 'app'@'%' IDENTIFIED BY 'secret' is rejected with a syntax error
  • SET PASSWORD = PASSWORD('secret') is rejected on MySQL 8
  • The account exists but the client still gets access denied

Cause 1 of 4

GRANT ... IDENTIFIED BY was used to create the account

1Check

MySQL 8.0.11 removed creating users and setting passwords through GRANT. See whether your statement contains IDENTIFIED BY after a GRANT.

SELECT VERSION();

2Fix

Split it in two: CREATE USER defines the account and password, GRANT defines privileges.

CREATE USER 'app'@'%' IDENTIFIED BY 'a-strong-password';
GRANT ALL ON mydb.* TO 'app'@'%';

3Verify

The grants show the privileges on the account.

SHOW GRANTS FOR 'app'@'%';

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 4

PASSWORD() or SET PASSWORD = PASSWORD() was used

1Check

The PASSWORD() function was removed in 8.0, and with it the SET PASSWORD ... = PASSWORD('...') form.

SELECT VERSION();

2Fix

Change the password with ALTER USER. The plain text goes in IDENTIFIED BY and the server hashes it.

ALTER USER 'app'@'%' IDENTIFIED BY 'a-new-strong-password';

3Verify

Connect with the new password from the client.

mysql -u app -p -h 127.0.0.1

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 4

IDENTIFIED BY PASSWORD 'hash' was used with a stored hash

1Check

That syntax was removed in 8.0.11. The replacement names the authentication plugin and supplies a hash in that plugin's format.

SELECT VERSION();

2Fix

Use IDENTIFIED WITH plugin AS 'hash' (the hash must already be in the format of the named plugin), or just set a fresh password with IDENTIFIED BY.

CREATE USER 'app'@'%' IDENTIFIED WITH caching_sha2_password BY 'a-strong-password';

3Verify

The account is listed.

SELECT user, host FROM mysql.user WHERE user = 'app';

Confirmed by dev.mysql.com

Did this fix it?

Cause 4 of 4

The account was created for a different host than the client uses

1Check

A server matches the user name and the client host together. An account for 'localhost' does not match a client on another machine.

SELECT user, host FROM mysql.user WHERE user = 'app';
SELECT CURRENT_USER();

2Fix

Create the account with a host part that covers the client: an exact IP, a subnet pattern, or % for any host (broad, so prefer a subnet).

CREATE USER 'app'@'198.51.100.%' IDENTIFIED BY 'a-strong-password';
GRANT ALL ON mydb.* TO 'app'@'198.51.100.%';

3Verify

Connect from the client machine.

mysql -u app -p -h your-db-host

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Keep user setup in a script that runs CREATE USER and GRANT as two statements.
  • Do not paste account statements from guides written for MySQL 5.x without checking them against the 8.4 manual.
  • Use the narrowest host part that works for the client.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Databases

CREATE USER fails with error 1396

CREATE USER is refused for an account whose user and host already exist (error 1396)

Error 1396 on CREATE USER means an account with that exact user and host already exists, often left from an earlier attempt.

3 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.