执行GRANT权限授权命令时遭遇语法错误求助

Hey there! Let's figure out why your MySQL GRANT command is hitting a syntax error and how to fix it properly.

Your command uses a syntax that worked in MySQL 5.x and earlier, but it's no longer valid in MySQL 8.0 and newer versions. Here's the key reason:

• Starting with MySQL 8.0, the GRANT statement was updated to strictly handle only permission assignments. The IDENTIFIED BY clause (used to set passwords) was removed from GRANT to separate user management (like creating users or setting passwords) from permission management. This change also aligns with the default use of the more secure caching_sha2_password authentication plugin.

Scenario 1: The root@% user doesn't exist yet #

You need to create the user first, then grant permissions:

1. Create the user and set a password:

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

2. Grant all privileges:

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

3. Flush privileges to apply the changes immediately:

   FLUSH PRIVILEGES;
   

Scenario 2: The root@% user already exists #

If the user is already in your MySQL instance, update the password (if needed) then grant permissions:

1. Update the user's password (skip this if you don't need to change it):

   ALTER USER 'root'@'%' IDENTIFIED BY 'password';
   

2. Grant the required privileges:

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

3. Flush privileges to make the changes take effect:

   FLUSH PRIVILEGES;
   

Quick Note for MySQL 5.x Users #

If you're still on a 5.x version, your original command should work fine. But it's still better to follow the split approach for consistency, especially if you plan to upgrade later.

内容的提问来源于stack exchange，提问作者Naishu Babu