Mysql error 1410 42000 you are not allowed to create a user with grant

Tried

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

Getting

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 ‘root’ WITH GRANT OPTION’ at line 1.

Note: The same is working when tried in previous versions.

Also tried

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

Getting

ERROR 1410 (42000): You are not allowed to create a user with GRANT

MySQL (8.0.11.0) username/password is root/root.

Starting with MySQL 8 you no longer can (implicitly) create a user using the GRANT command. Use CREATE USER instead, followed by the GRANT statement:

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'PASSWORD';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

Caution about the security risks about WITH GRANT OPTION, see:

• Grant all privileges on database

I see a lot of (wrong) answers, it is just as simple as this:

USE mysql;
CREATE USER 'user'@'localhost' IDENTIFIED BY '[email protected]';
GRANT ALL ON *.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

Note: instead of a self-created user you can use root to connect to the database. However, using the default root account to let an application connect to the database is not the preferred way. Alternative privileges can be applied as follows (be careful and remember the least-privilege principle):

-- Grant user permissions to all tables in my_database from localhost --
GRANT ALL ON my_database.* TO 'user'@'localhost';

-- Grant user permissions to my_table in my_database from localhost --
GRANT ALL ON my_database.my_table TO 'user'@'localhost';

-- Grant user permissions to all tables and databases from all hosts --
GRANT ALL ON *.* TO 'user'@'*';

If you would somehow run into the following error:

ERROR 1130 (HY000): Host ‘1.2.3.4’ is not allowed to connect to this
MySQL server

You need add/change the following two lines in /etc/mysql/my.cnf and restart mysql:

bind-address           = 0.0.0.0
skip-networking

1) This worked for me. First, create a new user. Example: User foo with password bar

> mysql> CREATE USER 'foo'@'localhost' IDENTIFIED WITH mysql_native_password BY 'bar';

2) Replace the below code with a username with ‘foo’.

> mysql> GRANT ALL PRIVILEGES ON database_name.* TO'foo'@'localhost';

Note: database_name is the database that you want to have privileges, . means all on all

3) Login as user foo

mysql> mysql -u foo -p

Password: bar

4) Make sure your initial connection from Sequelize is set to foo with pw bar.

Just my 2 cents on the subject. I was having the exact same issue with trying to connect from MySQL Workbench. I’m running a bitnami-mysql virtual machine to set up a local sandbox for development.

Bitnami’s tutorial said to run the ‘Grant All Privileges’ command:

/opt/bitnami/mysql/bin/mysql -u root -p -e "grant all privileges on *.* to 'root'@'%' identified by 'PASSWORD' with grant option";

This was clearly not working, I finally got it to work using Mike Lischke’s answer.

What I think happened was that the [email protected]% user had the wrong credentials associated to it. So if you’ve tried to modify the user’s privileges and with no luck try:

1. Dropping the user.
2. Create the user again.
3. Make sure you have the correct binding on your MySQL config file.
   In my case I’ve commented the line out since it’s just for a sandbox environment.

1. Dropping the user.

From Mysql Console:

List Users (helpful to see all your users):

select user, host from mysql.user;

Drop Desired User:

drop user '{{ username }}'@'%';

2. Create the user again.

Create User and Grant Permissions:

CREATE USER '{{ username }}'@'%' IDENTIFIED BY '{{ password }}';
GRANT ALL PRIVILEGES ON *.* TO '{{ username }}'@'%' WITH GRANT OPTION;

Run this command:

FLUSH PRIVILEGES;

3. Make sure you have the correct binding on your MySQL config file.

Locate your MySQL config file (additional notes at the end). If you want to have MySQL listen for connections on more than one network find the following line on the config file:

bind-address=127.0.0.1

and comment it using a ‘#’:

#bind-address=127.0.0.1

For production environments you might want to use limit the network access (additional notes at the end).

Then restart your MySQL service.

Hope this helps someone having the same issue!

Binding: If you want to know more about this I suggest looking at the following
solution How to bind MySQL server to more than one IP address. It
basically says you can leave MySQL open and limit connections by using
a firewall, or natively if you have MySQL version 8.0.13 and above.

MySQL Config File The file could have different locations depending on your
Linux distribution and installation. On my system it was located at
'/etc/my.cnf'. Here are other suggested locations:

• /etc/mysql/mysql.conf.d
• /etc/mysql/my.cnf

You can also search for the config locations as shown in this website:
How to find locations of MySQL config files.

For those who’ve been confused by CREATE USER 'root'@'localhost' when you already have a root account on the server machine, keep in mind that your 'root'@'localhost' and 'root'@'your_remote_ip' are two different users (same user name, yet different scope) in mysql server. Hence, creating a new user with your_remote_ip postfix will actually create a new valid root user that you can use to access the mysql server from a remote machine.

For example, if you’re using root to connect to your mysql server from a remote machine whose IP is 10.154.10.241 and you want to set a password for the remote root account which is '[email protected]#', here are steps you would want to follow:

1. On your mysql server machine, do mysql -u root -p, then enter your password for root to login.

2. Once in mysql> session, do this to create root user for the remote scope:

   mysql> CREATE USER 'root'@'10.154.10.241' IDENTIFIED BY '[email protected]#';
   

3. After the Query OK message, do this to grant the newly created root user all privileges:

   mysql> GRANT ALL ON *.* TO 'root'@'10.154.10.241';
   

4. And then:

   FLUSH PRIVILEGES;
   

5. Restart the mysqld service:

   sudo service mysqld restart
   

6. Confirm that the server has successfully restarted:

   sudo service mysqld status
   

If the steps above were executed without any error, you can now access to the mysql server from a remote machine using root.

My Specs:

mysql --version
mysql  Ver 8.0.16 for Linux on x86_64 (MySQL Community Server - GPL)

What worked for me:

mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'desired_password';
mysql> GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'localhost' WITH GRANT OPTION;

Response in both queries:

Query OK, O rows affected (0.10 sec*)

N.B: I created a database (db_name) earlier and was creating a user credential with all privileges granted to all tables in the DB in place of using the default root user which I read somewhere is a best practice.

The specified user just doesn’t exist on your MySQL (so, MySQL is trying to create it with GRANT as it did before version 8, but fails with the limitations, introduced in this version).

MySQL’s pretty dumb at this point, so if you have ‘root’@’localhost’ and trying to grant privileges to ‘root’@’%’ it treats them as different users, rather than generalized notion for root user on any host, including localhost.

The error message is also misleading.

So, if you’re getting the error message, check your existing users with something like this

SELECT CONCAT("'", user, "'@'", host, "'") FROM mysql.user;

and then create missing user (as Mike advised) or adjust your GRANT command to the actual exisiting user specificaion.

Solution

Many thanks @Nebulastic
If you want to only allow remote IP using following command

CREATE USER 'user_test'@'113.yy.xx.94' IDENTIFIED BY 'YOUR_PWD';
GRANT ALL ON *.* TO 'user_test'@'113.yy.xx.94';
FLUSH PRIVILEGES;

You will get this error

ERROR 1410 (42000): You are not allowed to create a user with GRANT

If you are trying to run a GRANT on a user that doesn’t exist!

Therefore, first run this to make sure the user you use in your GRANT matches exactly to what you have:

select User, Host from user;

In particular pay attention whether the user you created is at localhost but the one you are trying to grant to is %

Copy this and use it at once:

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

Instead of using single lines of code such as:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Then:

GRANT ALL ON *.* TO 'username'@'localhost';

Then:

FLUSH PRIVILEGES;

in select statement, changing ‘user’@’%’ to ‘user’@’localhost’ solved my problem

this commands work for me:

1-login to mysql and see all users

sudo mysql -u root

select user, host from mysql.user;

2-delete old user

drop user [email protected];

3-create new user

CREATE USER 'root'@'localhost' IDENTIFIED BY 'mypassword'

4-add all privileges to it:

GRANT ALL ON *.* TO 'root'@'localhost'

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password
BY 'mypassword';

5-finally flush privileges

FLUSH PRIVILEGES;

Check out your username and domain is the same as created before. Mysql select account by the two colums in user table.If it is different, mysql may think you want to create a new account by grant,which is not supported after 8.0 version.

My Specs:

mysql --version
mysql  Ver 8.0.19 for Linux on x86_64 (MySQL Community Server - GPL)

What worked for me:

mysql> USE mysql;
mysql> UPDATE User SET Host='%' WHERE User='root' AND Host='localhost';

This worked for me:

mysql> FLUSH PRIVILEGES 
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES

In my case I wanted to do something similar, I followed some steps from here but the best way was as @nebulasic mentioned:

USE mysql;
CREATE USER 'user'@'localhost' IDENTIFIED BY '[email protected]';
GRANT ALL ON *.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

After this I encountered an error while trying to query the database or connect with SQLTools from VSCode.

Client does not support authentication protocol requested by server; consider upgrading MySQL client

Running this query will fix the problem:

ALTER USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY '[email protected]$w0Rd';

I also want to mention that these steps are ok to work in a local environment, when doing something in production is recommended to allocate each user to each database with generated password accordingly and different other security measures if necessary.

1. grant privileges

mysql> GRANT ALL PRIVILEGES ON . TO ‘root’@’%’WITH GRANT OPTION;

mysql> FLUSH PRIVILEGES

2. check user table:

mysql> use mysql

mysql> select host,user from user

3.Modify the configuration file

mysql default bind ip：127.0.0.1, if we want to remote visit services,just delete config

#Modify the configuration file
vi /usr/local/etc/my.cnf

#Comment out the ip-address option
[mysqld]
# Only allow connections from localhost
#bind-address = 127.0.0.1

4.finally restart the services

brew services restart mysql

Well, I just had the same problem. Even if route had ‘%’ could not connect remotely. Now, having a look at my.ini file (config file in windows) the bind-address statement was missed.

So… I putted this bind-address = * after [mysqld] and restarted the service. Now it works!

Try this, i had the same issue and i tried few options, but the below worked.

GRANT ALL ON . TO ‘root’@’%’;

Reference used — https://www.digitalocean.com/community/tutorials/how-to-install-linux-apache-mysql-php-lamp-stack-on-ubuntu-20-04#step-6-%E2%80%94-testing-database-connection-from-php-optional

• ubuntu 22.04.1
• Mysql Ver 8.0.31-0

My root had no GRANT privileges so I could not grant new users any previligies.
Solution was to Drop current root user and create new one using ‘mysql_native_password’.

Commands as follows
Login to mysql with as root

mysql> DROP USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD' FROM mysql.user;
mysql> CREATE USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'locahost' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

This may work:

grant all on dbtest.* to 'dbuser'@'%' identified by 'mysql_password';

I had this same issue, which led me here. In particular, for local development, I wanted to be able to do mysql -u root -p without sudo. I don’t want to create a new user. I want to use root from a local PHP web app.

The error message is misleading, as there was nothing wrong with the default 'root'@'%' user privileges.

Instead, as several people mentioned in the other answers, the solution was simply to set bind-address=0.0.0.0 instead of bind-address=127.0.0.1 in my /etc/mysql/mysql.conf.d/mysqld.cnf config. No changes were otherwise required.

I had the same problem on CentOS and this worked for me (version: 8.0.11):

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'

Stary mysql with sudo

sudo mysql

Comments Section

are you connecting directly with mysql or do you use ssh?

directly from command prompt as root user

See the differences between 13.7.1.6 GRANT Syntax and 13.7.1.4 GRANT Syntax.

im too stuck with the same issue. I am launching the mysql shell using mysql -u root -p, then entering root password. Then I tried GRANT GRANT OPTION ON *.* TO 'root'@'%'; and I get the error ERROR 1410 (42000): You are not allowed to create a user with GRANT

This command will create a new user. But I want to grant privileges to existing root user. mysql> CREATE USER ‘root’@’%’ IDENTIFIED BY ‘root’; Query OK, 0 rows affected (0.31 sec) mysql> GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ WITH GRANT OPTION; Query OK, 0 rows affected (0.16 sec) mysql> SELECT User FROM mysql.user; +——————+ | User | +——————+ | root | | mysql.infoschema | | mysql.session | | mysql.sys | | root | +——————+ 5 rows in set (0.00 sec)

Now two users with name root are present in user table!! Also I am not able to connect remotely (root user). ————- Details: Type: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException SQL State: 08001

Weird. The error message in your question indicates this user doesn’t exist. This is the only reason why I suggested to create it first. And it’s impossible to have the same user twice in single MySQL instance. They differ either in the name or in the host part.

These 2 users connect from different hosts. Run SELECT User, Host FROM mysql.user; instead.

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 ‘. TO ‘root’@’%» at line 1

Got that error: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

mysql> GRANT ALL PRIVILEGES ON . TO ‘root’@’%’; 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 ‘. TO ‘root’@’%» at line 1 mysql>

This does by far not answer the question. It’s about global permissions, and not database specific permissions.

This was a valid solution for me! I simply had failed to type the [email protected] line precisely. Be sure to check this before trying the other solutions suggested. For me it was: CREATE USER ‘user’@’%domain.com’ and then when I issued the failing GRANT statement, my syntax was off by one ‘.’. It looked like this GRANT …. TO ‘user’@’%.domain.com’. I forgot to put the ‘.’ in my create statement and it made all the difference in fixing this issue.

Rather than toss out code, also explain why it’s the appropriate solution. The goal is to educate so the OP knows what to do the next time, not merely solve the immediate problem.

Although it would work in your specific situation. This pieces of sql syntax updates a user, and it does not set a user account. It also does not grant privileges.

Could you elaborate on why it doesn’t work instead of just complaining? That way I can help you out or post additional information.

This answer doesn’t even sort of address the question of root user privilege.

This is actually the closest to the correct answer.

Updated the answer accordingly.

This doesn’t even fully answer the question.

Best answer, now it’s GRANT ALL ON for MySQL 8.0. Also I believe instead of disabling bind-address you can bind to 127.0.0.1 and include --protocol=tcp in any commands. The performance long-term is also better than with localhost.

What all answers beside mine miss here is that the OP wondered why the user can no longer be created with the GRANT command. It was never asked how to use GRANT to grant privileges. But somehow people refuse to read what has been asked and answered before.

i had a typo in the user and this was what happend!

@Praveen are you ever going to accept an answer here?

This should be accepted answer! Same here, it’s what after @ that matters and it must be correct. Stupid Mysql

In MySQL 8 FLUSH PRIVILEGES; is redundant, the changes are applied immediately.

Thanks a lot, your answer was sort of clarifying to me. mysql v8 deals ‘host:localhost’ only locally, but host:% is being dealt with for remote connection, so we need either a new user with host ‘%’ in mysql.user table or modify one on the table to that host and grant all permissions to be able to use remotely. Although you better clarify it more and provide a full solution, you’re on the stack, so you better get points

Yeah, for some reason this gives me the error message ‘You have an error in your SQL syntax’

I too had same problem — do double check if your create user actually created a user as expected

Only answer in the internet that work for my version.

This was it. Thank you! The user had different host name (e.g. 'user'@'%' vs 'user'@'data').

Related Topics
mysql
mysql-8.0
mysql-error-1064

Mentions
The Tin Man
Nebulastic
Praveen
User
Toni
Acv
Stephan Vierkant
Mike Lischke
Sergey Podushkin
Zenglong Chen
Zero
Shivangg
Minding
Fatemeh Sadeghi
Bumi Ke
Rajeshk
Broodje Be
Nuel Makara
Armando Juarez
Devo Chen
Ehab Reda
Anup Joshi
Marshall Fungai
Cloudkollektiv
Edd Michira

References
stackoverflow.com/questions/50177216/how-to-grant-all-privileges-to-root-user-in-mysql-8-0