[mysql] How to grant all privileges to root user in MySQL 8.0

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.

This question is related to mysql mysql-error-1064 mysql-8.0

The answer is


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 'Abcdef123!@#', 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 'Abcdef123!@#';
    
  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.


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.


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 %


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.


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

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

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.


This may work:

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

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

USE mysql;
CREATE USER 'user'@'localhost' IDENTIFIED BY 'P@ssW0rd';
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 (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

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.


This worked for me:

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

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!


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 'root';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

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


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 enter image description here

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


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.


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';

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 root@% 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 my.cnf config file. In my case I've commented the line out since it's just for a sandbox environment.

From Mysql Console:

List Users (helpful to see all your users):

select user, host from mysql.user;

Drop Desired User:

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

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;

Locate your mysql config file 'my.cnf' and look for a line that looks like this:

bind-address=127.0.0.1

and comment it using a '#':

#bind-address=127.0.0.1

Then restart your mysql service.

Hope this helps someone having the same issue!


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to mysql-error-1064

How to grant all privileges to root user in MySQL 8.0 Warning about SSL connection when connecting to MySQL database How can I fix MySQL error #1064? MySQL Error #1133 - Can't find any matching row in the user table MySql Inner Join with WHERE clause Check for database connection, otherwise display message ERROR 1064 (42000) in MySQL mysql is not recognised as an internal or external command,operable program or batch MySQL error: 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 SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO

Examples related to mysql-8.0

PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client How to grant all privileges to root user in MySQL 8.0 phpMyAdmin on MySQL 8.0 Authentication plugin 'caching_sha2_password' cannot be loaded How do you use the "WITH" clause in MySQL?