Bug #39733 | informmation_schema.views does not show view_definition for user with SUPER | ||
---|---|---|---|
Submitted: | 29 Sep 2008 15:22 | Modified: | 3 Oct 2008 6:14 |
Reporter: | Boyd Hemphill | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
Version: | 5.1.24 | OS: | Linux |
Assigned to: | CPU Architecture: | Any | |
Tags: | DDL, information_schema, Views |
[29 Sep 2008 15:22]
Boyd Hemphill
[2 Oct 2008 17:57]
Sveta Smirnova
Thank you for the report. But user should have SHOW VIEW privilege to be able to see view definition, not SUPER. Please provide output of SHOW GRANTS for user with SUPER privilege if you believe it has SHOW VIEW privilege as well. See also http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html
[2 Oct 2008 20:09]
Boyd Hemphill
Sveta: Short version: I get that I can obtain the info with SHOW, but it is not convenient and means I need to use SHOW syntax when I want to use SQL. Here is the problem: I want to write a select statement from the information_schema to change the view properties locally. I cannot do this with Show b/c I cannot parse the info in SQL I have to drop to the shell and use Perl/Bash, etc. So a problem that would have taken 10 minutes (change all view security to INVOKER) became very manual. Since I have SUPER, I should be able to see everything about a view (and stored routine for that matter). This is why I view it as a minor bug. Here is a rough example: select concat('create or replace sql security invoker view ',table_name,' as ',view_definition) from information_schema.views into outfile 'foo.sql' ; source foo.sql ; Had it worked, 21 views would have been affected at once. Thanks for your time in this matter. Boyd
[3 Oct 2008 6:14]
Sveta Smirnova
Thank you for the feedback. SUPER is for administrative purpose and has nothing to do with VIEW definition. But problem described exists and already reported in bug #22763 So I close this report as duplicate of bug #22763.
[14 Oct 2009 15:52]
Sheeri Cabral
I found that if the current user is not the definer, the value will be blank, even if the current user has permissions to see the view definition. (in this example case, the current user is root@127.0.0.1 and can't see views defined by root@localhost). I posted the question last year at: http://www.pythian.com/news/1276 and just submitted a comment with the information about the behavior there. I noted the behavior on page 674 of the MySQL Administrator's Bible in the Data Dictionary Chapter. mysql> USE INFORMATION_SCHEMA; Database changed mysql> SELECT TABLE_NAME,VIEW_DEFINITION FROM VIEWS WHERE TABLE_SCHEMA='sakila'; +----------------------------+-----------------+ | TABLE_NAME | VIEW_DEFINITION | +----------------------------+-----------------+ | actor_info | | | customer_list | | | film_list | | | nicer_but_slower_film_list | | | sales_by_film_category | | | sales_by_store | | | staff_list | | +----------------------------+-----------------+ 7 rows in set (0.16 sec) mysql> SHOW CREATE VIEW sakila.actor_info\G *************************** 1. row *************************** View: actor_info Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `sakila`.`actor_info` AS select `a`.`actor_id` AS `actor_i d`,`a`.`first_name` AS `first_name`,`a`.`last_name` AS `last_name`,group_concat( distinct concat(`c`.`name`,': ',(select group_concat(`f`.`title` order by `f`.`t itle` ASC separator ', ') AS `GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ', ')` from ((`sakila`.`film` `f` join `sakila`.`film_category` `fc` on((`f`.`film _id` = `fc`.`film_id`))) join `sakila`.`film_actor` `fa` on((`f`.`film_id` = `fa `.`film_id`))) where ((`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_ id` = `a`.`actor_id`)))) order by `c`.`name` ASC separator '; ') AS `film_info` from (((`sakila`.`actor` `a` left join `sakila`.`film_actor` `fa` on((`a`.`actor _id` = `fa`.`actor_id`))) left join `sakila`.`film_category` `fc` on((`fa`.`film _id` = `fc`.`film_id`))) left join `sakila`.`category` `c` on((`fc`.`category_id ` = `c`.`category_id`))) group by `a`.`actor_id`,`a`.`first_name`,`a`.`last_name ` character_set_client: latin1 collation_connection: latin1_swedish_ci 1 row in set (0.02 sec) mysql> SHOW CREATE VIEW sakila.customer_list\G *************************** 1. row *************************** View: customer_list Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sakila`.`customer_list` AS select `cu`.`customer_id` AS ` ID`,concat(`cu`.`first_name`,_utf8' ',`cu`.`last_name`) AS `name`,`a`.`address` AS `address`,`a`.`postal_code` AS `zip code`,`a`.`phone` AS `phone`,`sakila`.`ci ty`.`city` AS `city`,`sakila`.`country`.`country` AS `country`,if(`cu`.`active`, _utf8'active',_utf8'') AS `notes`,`cu`.`store_id` AS `SID` from (((`sakila`.`cus tomer` `cu` join `sakila`.`address` `a` on((`cu`.`address_id` = `a`.`address_id` ))) join `sakila`.`city` on((`a`.`city_id` = `sakila`.`city`.`city_id`))) join ` sakila`.`country` on((`sakila`.`city`.`country_id` = `sakila`.`country`.`country _id`))) character_set_client: latin1 collation_connection: latin1_swedish_ci 1 row in set (0.00 sec)