Creating a MySQL user with a password fails on MySQL 8 and later
GRANT ALL ON db.* TO 'app'@'%' IDENTIFIED BY 'secret' is rejected with a syntax errorShort 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.
Fix it fast
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';2Match what you see, then jump to its fix
- 1
A 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
A statement that wraps the password in PASSWORD('...') failsPASSWORD() or SET PASSWORD = PASSWORD() was usedFix - 3
A CREATE USER or GRANT that carries a hash after IDENTIFIED BY PASSWORD failsIDENTIFIED BY PASSWORD 'hash' was used with a stored hashFix - 4
The 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
- 1
What does the error look like?
GRANT ALL ON db.* TO 'app'@'%' IDENTIFIED BY 'secret' is rejected with a syntax errorSET PASSWORD = PASSWORD('secret') is rejected on MySQL 8The 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
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.1Confirmed by dev.mysql.com
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
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-hostConfirmed by dev.mysql.com
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
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 fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix