How to grant all privileges to root user in MySQL 8.0

Question

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.

Answer 1

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

Answer 2

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