How to Grant All Privileges to the MySQL 8.0 Root User?

Granting All Privileges to the Root User in MySQL 8.0

In previous versions of MySQL, granting all privileges to the root user was straightforward using the GRANT command. However, in MySQL 8.0, this approach is met with syntax errors.

Error Encountered

When attempting to grant all privileges using the syntax:

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

the following error occurs:

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.

Revised Approach

Starting with MySQL 8, creating users with the GRANT command is no longer supported. Instead, the following two-step process is necessary:

1. Create the User

Use the CREATE USER command to create the root user:

CREATE USER 'root'@'%' IDENTIFIED BY 'PASSWORD';

2. Grant Privileges

Then, use the GRANT command to grant all privileges:

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

Final Step

To ensure the changes take effect, flush the privileges:

FLUSH PRIVILEGES;

Caution

Granting all privileges with the WITH GRANT OPTION carries significant security risks. It is recommended to exercise caution and only grant necessary privileges. Consult the MySQL documentation for more information on user privileges and security.

The above is the detailed content of How to Grant All Privileges to the MySQL 8.0 Root User?. For more information, please follow other related articles on the PHP Chinese website!