how to give grant privileges to mysql 8 root user to access from another host

The fastest way to know what privileges your current user has is:

Connect with the user rootand execute the following commands to verify what permissions each existing user has in your database manager:

USE mysql;

SELECT user, host, Grant_priv
FROM user;

Which should return something like the following:

mysql> SELECT Host, User, Grant_priv FROM user;
+-----------+------------------+------------+
| Host      | User             | Grant_priv |
+-----------+------------------+------------+
| localhost | demo             | N          |
| localhost | mysql.infoschema | N          |
| localhost | mysql.session    | N          |
| localhost | mysql.sys        | N          |
| localhost | root             | Y          |
+-----------+------------------+------------+

Where as you can verify the user demo does not have privileges assigned and especially does not have the privilegeGRANT , which is the one necessary for the command you are trying to execute

Given the above then the user cannot assign the permission GRANTto another user; for which we should do the following:

So we do:

GRANT ALL PRIVILEGES ON *.* TO 'demo'@'tuhost' WITH GRANT OPTION;

According to the MySQL 8 documentation in the other account characteristics section, it indicates:

The optional WITH clause is used to enable a user to grant privileges to other users. The WITH GRANT OPTION clause gives the user the ability to give other users any privileges the user has at the specified privilege level.

Which could be translated as:

The optional clause WITHis used to enable the user to grant permissions to other users. the option WITH GRANT OPTIONgives the user the ability to give other users whatever privileges the user has at the specified privilege level.

Now if the user in question already has privileges and just looking to add the user GRANT OPTIONSthen run the command like this:

GRANT USAGE ON *.* TO 'demo'@'tuhost' WITH GRANT OPTION;

So that your current user is in a position to grant permissions GRANTto any other user.

If you run SELECTthe top one again like so:

SELECT Host, Name, Grant_priv FROM user

You will get something like this:

mysql> SELECT Host, User, Grant_priv FROM user;
+-----------+------------------+------------+
| Host      | User             | Grant_priv |
+-----------+------------------+------------+
| localhost | demo             | Y          |
| localhost | mysql.infoschema | N          |
| localhost | mysql.session    | N          |
| localhost | mysql.sys        | N          |
| localhost | root             | Y          |
+-----------+------------------+------------+

Where you can already notice that the permission GRANTexists for the user demoand therefore you can grant it to other users

References

• Grant GRANTa user in MySQL 8
• List of available privileges

I can already access UPDATE user SET host='%' WHERE user='root';it is solved by saying that the root user can access from any host