What unites DEFINER, “show table status” and ERROR 1143 (42000) in MySQL
For the very first time I had seen this error a couple of days ago:
mysql> show table status; ERROR 1143 (42000): SELECT command denied to user ''@'some_host_name_here' for column 'sid' in table 'masking'
What the heck was that?!
Turned out that a colleague of mine was doing a cleanup in mysql.user table a day before and deleted a number of non-existent users. So what? Well, the database I was working with didn’t have only “plain” tables:
mysql> show full tables in database_name_here where table_type not like '%table%'; +-----------------+------------+ | Tables_in_infra | Table_type | +-----------------+------------+ | v_pool | VIEW | | v_pool_pivot | VIEW | +-----------------+------------+ 2 rows in set (0.00 sec)
Aha moment! So we had two views. Checked if there was a proper“DEFINER”:
mysql> select TABLE_NAME, DEFINER from information_schema.views; +--------------+--------------------------------------------+ | TABLE_NAME | DEFINER | +--------------+--------------------------------------------+ | v_pool | some_user@some_host | | v_pool_pivot | some_user@some_host | +--------------+--------------------------------------------+ 10 rows in set (0.00 sec)
Of course, that was exactly the user which was deleted and thankfully, that was very easy to fix.
Just had to run “show create view” query to figure out how the view was created in the first place:
mysql> show create view v_pool_pivot\G
*************************** 1. row ***************************
View: v_pool_pivot
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`some_user@some_host` SQL SECURITY DEFINER VIEW `v_pool_pivot` AS (select followed by several lines of spaghetti SQL)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set, 1 warning (0.00 sec)
And after that just altered it:
mysql> alter DEFINER=CURRENT_USER view v_pool_pivot AS (select ...)
Puzzle solved.