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

score 53 · Accepted Answer · answered Nov 15 '20 at 05:40

53

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.

answered Nov 15 '20 at 05:40

danblack

12,130
2
22
41

1

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? – PKHunter Jan 03 '21 at 20:24
@PKHunter please ask this as a new question and include details like the source and destination version. – danblack Jan 03 '21 at 23:49
@danblack 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! – flokoe Mar 24 '21 at 09:43
I should have mentioned [RENAME USER](https://mariadb.com/kb/en/rename-user/) too. – danblack Jun 01 '21 at 23:26
5

You can do ```RENAME USER "root"@"localhost" TO "root"@"%";``` – Luis Pedro Bonomi Oct 31 '22 at 06:37
getting `Operation RENAME USER failed for 'user'@'localhost' ` – Moeez Apr 28 '23 at 07:29

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

1 Answers1

Linked

Related