Why Can\'t I Update the `mysql.user` Host Value and How Can I Modify User Privileges Instead?

Unable to Modify User Hosts Due to View Error

Question:

When attempting to update the Host value for the root user via the query UPDATE user SET Host='%' WHERE User='root';, an error is encountered:

ERROR 1356 (HY000): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Answer:

In MySQL MariaDB 10.4 , the mysql.user table has been replaced with a view. Therefore, direct updates to the mysql.user table are no longer feasible.

To manage user authentication, use the following methods instead:

• SET PASSWORD or ALTER USER: These commands allow you to modify the password or other attributes of a user account.

It's crucial to note that modifying the Host component of the username will disrupt related triggers, events, plugins, and grants. To ensure proper functionality, it's recommended to create and drop users rather than manipulating their existing accounts.

The above is the detailed content of Why Can\'t I Update the `mysql.user` Host Value and How Can I Modify User Privileges Instead?. For more information, please follow other related articles on the PHP Chinese website!