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

Question

I have tried query but there is an error.
Does anybody solved the error?

MariaDB [mysql]> UPDATE user SET Host='%' WHERE User='root'; 
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

Ruralize

Answer 1

MariaDB-10.4+ the mysql.user is a view rather than a table.

Its recommend to stop copying off old blogs to do any authentication relates changes in MySQL and MariaDB, the mechanisms are being updated and no longer apply. Always check the official documentation.

Use SET PASSWORD or ALTER USER to manage user authentication.

Also modifying a user/host component of the username will put triggers, events, plugins, grants, roles etc out of sync with the combined username (aka broken). So just DROP/CREATE users rather than manipulate them.

Confabulate

I'm facing this issue now I suppose. I copied all the old Wordpress databases that were created with older version of MariaDB (via mysqldump and then restore via source). I suppose now the old users don't have access to the databases. How should I now enable all the old users to have access to the right database? Even I log in as root, and issue the command: SELECT user FROM mysql.user; it gives the above error. What is the solution? – Wordless

@Wordless please ask this as a new question and include details like the source and destination version. – Confabulate

@Confabulate the real mvp! We recently upgraded from MariaDB 10.3. Didn't thought about that the mechanism could change and used the old one as I always did. Should've known better, Thanks a lot! – Linder

I should have mentioned RENAME USER too. – Confabulate

You can do RENAME USER "root"@"localhost" TO "root"@"%"; – Pavyer

getting Operation RENAME USER failed for 'user'@'localhost' – Petree

Answer 2

With MariaDB you can not alter users via user because the later one is a view, not a table; and you will get errors whenever you try to do that.

This is what I ended up, to overcome this issue on Debian 12:

sudo mysql -u root

USE mysql;

GRANT ALL PRIVILEGES ON *.* TO `begueradj`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT
 OPTION ;

Query OK, 0 rows affected (0.004 sec) // result

And I connect successfully:

mysql -u begueradj -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 10.11.6-MariaDB-0+deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

Umberto

Answer 3

This error comes when referencing table is dropped.

mysql> create table f(a float);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into f values(1.34),(33.56);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from f;
+-------+
| a     |
+-------+
|  1.34 |
| 33.56 |
+-------+
2 rows in set (0.00 sec)

mysql> create view fv as select * from f where a<10
;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from fv;
+------+
| a    |
+------+
| 1.34 |
+------+
1 row in set (0.00 sec)

mysql> drop table f;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from fv;
ERROR 1356 (HY000): View 'revision.fv' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql>

Crying

Answer 4

mysql -u root -p{Provide your root password here}
USE mysql;
ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password;
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('Provide your root password here');
FLUSH PRIVILEGES;
exit;

This will grant the necessary permissions you need to proceed.

Source:https://discuss.frappe.io/t/error-1356-hy000-view-mysql-user-references-invalid-table-s-or-column-s-or-function-s/93951/4

Hermaphrodite

USE mysql and FLUSH PRIVILEGES not needed when performing direct SQL user creation/modification. – Confabulate