How to Correctly Update MySQL User Privileges After MariaDB 10.4\'s `mysql.user` View Change?

MySql Error 1356: Invalid Table Reference in Update Query

When attempting to update the user table in MySQL with the query UPDATE user SET Host='%' WHERE User='root', users may encounter the following error:

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

This error occurs because the mysql.user table is no longer a table in MariaDB-10.4 but instead is a view. As a result, direct modifications to this view are no longer permitted.

Solution

To resolve this issue, it is recommended to use the following methods to manage user authentication in MySQL and MariaDB:

• SET PASSWORD: Use the SET PASSWORD statement to set or change user passwords.
• ALTER USER: Use the ALTER USER statement to update various user attributes, including the user's host.

Note:

• Do not attempt to modify user components (e.g., user, host) directly, as this can disrupt related database objects (triggers, events, etc.).
• Instead, it is advisable to drop and recreate users as needed, ensuring a clean and synchronized database configuration.

The above is the detailed content of How to Correctly Update MySQL User Privileges After MariaDB 10.4\'s `mysql.user` View Change?. For more information, please follow other related articles on the PHP Chinese website!