MySQL 8 create new user with password not working

Question

I am using MySQL for several years and the command for the creating the new user till the MySQL 5.x version is as follow:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Recently I installed the MySQL 8. In that, this command is not working.

It is throwing following error while firing above command:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'password'' at line 1

Is there any change of syntax in MySQL 8? What is the correct syntax for creating new user command in MySQL 8?

Note: I tried this syntax in MySQL 5.x versions. It is working correctly in that.

The manual page for 5.7 says: Use of GRANT to define account authentication characteristics is deprecated as of MySQL 5.7.6. Instead, establish or change authentication characteristics using CREATE USER or ALTER USER. This GRANT capability will be removed in a future MySQL release. — Álvaro González
– Álvaro González, Commented May 18, 2018 at 11:00
The correct command for creating a user in MySQL 8 is CREATE USER. Apparently the option to create the user when granting privileges has been removed - see GRANT syntax. Best of luck. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented May 18, 2018 at 11:00
Good question, and good answers. And still, why deprecate this? Make everybody waste time, and change their scripts just because the new syntax is "cleaner"? Hmm. — mircealungu
– mircealungu, Commented Jul 23, 2018 at 10:37

Delete · Accepted Answer · 2018-05-18 15:11:26Z

77

Try this:

use mysql;
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'username'@'localhost';
flush privileges;

answered May 18, 2018 at 15:11

Delete

9627 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ruslaniv Over a year ago

I actually had to do 'root'@'rhel' where 'rhel' was my hostname

BarneySchmale Over a year ago

'flush privileges' is not needed when working with CREATE USER and 'GRANT`

Jimbo Over a year ago

When running the line that begins with GRANT ALL, I get this error: "ERROR 1410 (42000): You are not allowed to create a user with GRANT." Despite the fact that I literally just created the user using the CREATE USER command.

Linga Over a year ago

It wont work in mysql8

Lounis Aug 13 at 7:50

No explanation or documentation is provided.

Susobhan Das · Accepted Answer · 2021-08-01 14:40:30Z

10

From MySQL recent releases -

To create a user -

CREATE USER 'newuser1'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Password@123';
GRANT ALL ON *.* TO 'newuser1'@'localhost';

This is two lines code to create a new user, and granting permissions.

Worked with MySQL Community server version 8.0.26 windows 10 x64 bit server.

answered Aug 1, 2021 at 14:40

Susobhan Das

1,31013 silver badges25 bronze badges

Comments

Linga · Accepted Answer · 2024-11-29 08:05:37Z

MySql 8.0.27 introduced authentication_policy which determines the default authentication plugin when creating/altering users that do not name a plugin explicitly.

As of MySQL 8.0.27, the default_authentication_plugin is still used, but in conjunction with authentication_policy. So, the default_authentication_plugin is deprecated as of MySQL 8.0.27 and will be removed soon.

The default value for authentication_policy is '*,,' which permits creating or altering accounts with one, two, or three factors. So, there are multiple ways to handle. One simple way is to define the auth_plugin during the user creation like below.

CREATE USER 'linga'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; // Here the auth_plugin is mysql_native_password

and then you can apply the GRANT.

Collectives™ on Stack Overflow

MySQL 8 create new user with password not working

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related