All geek questions in one place

How do I understand why MySQL does not allow me to request a view?

I am using MySql 5.6. I request a submission and get this error

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

I see a definition of a view and it doesn't seem like something is wrong.

mysql>  SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE table_schema = 'my_db' and TABLE_NAME = 'report_toc_item';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| VIEW_DEFINITION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| select `ti`.`ID` AS `ID`,`ti`.`PARENT_ID` AS `PARENT_ID`,`ti`.`TOC_ID` AS `TOC_ID`,`ti`.`TITLE` AS `TITLE`,`ti`.`DESCRIPTION` AS `DESCRIPTION`,`ti`.`TYPE_ID` AS `TYPE_ID`,`ti`.`ORDER_NUM` AS `ORDER_NUM`,`ti`.`MY_OBJECT_SEGMENT_ID` AS `MY_OBJECT_SEGMENT_ID`,`ti`.`MY_OBJECT_SEGMENT_ORDER_NUM` AS `MY_OBJECT_SEGMENT_ORDER_NUM`,`ti`.`ELEMENT_ID` AS `ELEMENT_ID`,`ti`.`UNIT_TOC_ITEM_ID` AS `UNIT_TOC_ITEM_ID`,`ti`.`SHORT_NAME` AS `SHORT_NAME`,`ti`.`THIRD_PARTY_PROMPT_ID` AS `THIRD_PARTY_PROMPT_ID`,`pti`.`TYPE_ID` AS `PARENT_TYPE_ID` from (`my_db`.`toc_item` `ti` join `my_db`.`toc_item` `pti` on((`pti`.`ID` = `ti`.`PARENT_ID`))) where ((`ti`.`TYPE_ID` = 'sub_segment') and ((`pti`.`TYPE_ID` = 'lesson') or (`pti`.`TYPE_ID` = 'activity') or (`pti`.`TYPE_ID` = 'activity_practice') or (`pti`.`TYPE_ID` = 'unit_opener'))) |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

How to determine which columns and tables are invalid? Please note that during the execution of both commands I registered as root.

Edit: Here is the result of “SHOW CREATE VIEW report_toc_item” as requested.

mysql> show create view report_toc_item;
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View               | Create View                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | character_set_client | collation_connection |
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| report_toc_item | CREATE ALGORITHM=UNDEFINED DEFINER=`myuser`@`localhost` SQL SECURITY DEFINER VIEW `report_toc_item` AS select `ti`.`ID` AS `ID`,`ti`.`PARENT_ID` AS `PARENT_ID`,`ti`.`TOC_ID` AS `TOC_ID`,`ti`.`TITLE` AS `TITLE`,`ti`.`DESCRIPTION` AS `DESCRIPTION`,`ti`.`TYPE_ID` AS `TYPE_ID`,`ti`.`ORDER_NUM` AS `ORDER_NUM`,`ti`.`MY_OBJECT_SEGMENT_ID` AS `MY_OBJECT_SEGMENT_ID`,`ti`.`MY_OBJECT_SEGMENT_ORDER_NUM` AS `MY_OBJECT_SEGMENT_ORDER_NUM`,`ti`.`ELEMENT_ID` AS `ELEMENT_ID`,`ti`.`UNIT_TOC_ITEM_ID` AS `UNIT_TOC_ITEM_ID`,`ti`.`SHORT_NAME` AS `SHORT_NAME`,`ti`.`THIRD_PARTY_PROMPT_ID` AS `THIRD_PARTY_PROMPT_ID`,`pti`.`TYPE_ID` AS `PARENT_TYPE_ID` from (`toc_item` `ti` join `toc_item` `pti` on((`pti`.`ID` = `ti`.`PARENT_ID`))) where ((`ti`.`TYPE_ID` = 'sub_segment') and ((`pti`.`TYPE_ID` = 'lesson') or (`pti`.`TYPE_ID` = 'activity') or (`pti`.`TYPE_ID` = 'activity_practice') or (`pti`.`TYPE_ID` = 'unit_opener'))) | utf8                 | utf8_general_ci      |
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

Edit 2:

Here are the grants assigned to this user

mysql> show grants for 'myuser'@'localhost';
+-------------------------------------------------+
| Grants for myuser@localhost                |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'localhost' |
+-------------------------------------------------+
1 row in set (0.00 sec)
+4
source share
5 answers

Full error message:

... or definer/invoker of view lack rights to use them

Check the view qualifier, and if there is one, make sure that the qualifier account can make a selection.

, :

SHOW CREATE VIEW

SELECT DEFINER FROM INFORMATION_SCHEMA.VIEWS WHERE ...
+3

DEFINER=myuser@localhost SQL SECURITY DEFINER , myuser@localhost ( ), . :

  • , , , DEFINER. . (, EXECUTE SELECT ), , DEFINER. , , . DEFINER (, root), , .
  • , , , invoker . DEFINER , , invoker.

, , , myuser@localhost, definer , , sql security invoker. , .

definer sql security, alter ( ) , as select .... show create view report_toc_item . definer sql security definer.

+1

, , .

, , . , toc_item, .

select `ti`.`ID` AS `ID`,`ti`.`PARENT_ID` AS `PARENT_ID`,`ti`.`TOC_ID` AS TOC_ID\, `ti`. `TITLE` AS` TITLE`, `ti`.`DESCRIPTION` AS` `, `ti````TYPE_ID`` `` `TYPE_ID`,` ti```ORDER_NUM` AS `ORDER_NUM`,` ti`.`MY_OBJECT_SEGMENT_ID` AS `MY_OBJECT_SEGMENT_ID`,` ti`.`MY_OBJECT_SEGMENT_ORDER_NUM` AS `MY_OBJECT_SEGMENT_ORDER_NUM`,` ti`.`ELEMENT_ID` AS `ELEMENT_ID`,` ti`.`UNIT_TOC_ITEM_ID` AS ` UNIT_TOC_ITEM_ID`, `ti`.`SHORT_NAME` AS` SHORT_NAME`, `ti```THIRD_PARTY_PROMPT_ID` AS` THIRD_PARTY_PROMPT_ID`, `pti`.`TYPE_ID` AS` PARENT_TYPE_ID` (`my_db`.`toc_item`` ti` `my_db`.`toc_item`` pti` on ((`pti``````` `` `` `` `` `PARENT_ID`)), ((` ti````TYPE_ID` = 'sub_segment') (( `pti`.`TYPE_ID` = 'lesson') (` pti`.`TYPE_ID` = 'activity') (`pti`.`TYPE_ID` = 'activity_practice') (` pti`.`TYPE_ID` = 'unit_opener'))); >

. :

 create table test
 (
    x integer not null,
    y integer not null
 );

 create or replace view test_view as select * from test;

 alter table test drop column y;

, .

 select * from test_view;

.

 show create view test_view;

 drop table test;

, .

 select * from test_view;

, !

 show create view test_view;

+1

myuser root, myuser.

0

MySQL localhost myuser. , .

MySQL: " , -. , FLUSH PRIVILEGES mysqladmin flush-privileges mysqladmin reload".

"ALGORITHM = UNDEFINED DEFINER = myuser @localhost SQL Defence Defence".

Place the conditional DROP TABLE IF EXISTS at the top of the comprehensive experimental file (which combines all settings and test)

Run the test and ask it to work in the simplest case, and then gradually add restrictions and functions to the table to find the breakpoint. Get everything else to work, but a feature that violates access.

Then you can examine a function that violates access or does not decide to use it.

0
source

Source: https://habr.com/ru/post/1671815/

More articles:


All Articles