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:
None 
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
Description:
When selecting from the views table of the information schema the view_definition only appears for views I have created.  

Since my account has the SUPER privilege, I should be able to see all definitions.  

How to repeat:
As a "lesser" user, create a view.

Create table foo( foo_id int unsigned not null auto_increment primary key);
insert into foo values (null),(null),(null),(null),(null),(null),(null),(null),(null),(null),(null),(null),(null),(null),(null),(null),(null);
create view v_foo as select * from view;

log in as user with SUPER.

select * from information_schema.views;

note that the veiw_definition column is empty for view v_foo.

Suggested fix:
SUPER priv should mean the user sees all definitions.
[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)