MySQL Bugs: #36316: Inconsistent behavior of views on information

Bug #36316	Inconsistent behavior of views on information_schema tables
Submitted:	24 Apr 2008 13:59	Modified:	4 Jun 2008 15:24
Reporter:	Deborah Van Vlack	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.0.26Max, 5.0.41	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	information_schema, VIEW

Description:
I tried to create a view on information_schema.STATISTICS where the view DDL selected a subset of columns from the table.  I expected to be able to create the view on the information schema table.  I got the error message:

error 1143 with the following message:
ANY command denied to user 'myID'@'192.168.1.213' for column 'TABLE_CATALOG' in table '/var/lib/mysql/.tmp/#sql_5f3c_0'

I tested the select statement on its own and it works.  I then tried creating a view using the DDL:
CREATE VIEW `MYDB`.`statMetadata` AS SELECT * FROM `information_schema`.`STATISTICS`;

This view create DDL worked.

When I use Query Browser to edit the view I see the following DDL:
DROP VIEW IF EXISTS `MYDB`.`statMetadata`;
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`myID`@`%` SQL SECURITY INVOKER
VIEW `MYDB`.`statMetadata` AS
select `STATISTICS`.`TABLE_CATALOG` AS `TABLE_CATALOG`,
`STATISTICS`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,
`STATISTICS`.`TABLE_NAME` AS `TABLE_NAME`,
`STATISTICS`.`NON_UNIQUE` AS `NON_UNIQUE`,
`STATISTICS`.`INDEX_SCHEMA` AS `INDEX_SCHEMA`,
`STATISTICS`.`INDEX_NAME` AS `INDEX_NAME`,
`STATISTICS`.`SEQ_IN_INDEX` AS `SEQ_IN_INDEX`,
`STATISTICS`.`COLUMN_NAME` AS `COLUMN_NAME`,
`STATISTICS`.`COLLATION` AS `COLLATION`,
`STATISTICS`.`CARDINALITY` AS `CARDINALITY`,
`STATISTICS`.`SUB_PART` AS `SUB_PART`,
`STATISTICS`.`PACKED` AS `PACKED`,
`STATISTICS`.`NULLABLE` AS `NULLABLE`,
`STATISTICS`.`INDEX_TYPE` AS `INDEX_TYPE`,
`STATISTICS`.`COMMENT` AS `COMMENT`
from `information_schema`.`STATISTICS`;

When I attempt to run this DDL to drop and rebuild the view, the view is dropped and I get error 1143 with the following message:
ANY command denied to user 'myID'@'192.168.1.213' for column 'TABLE_CATALOG' in table '/var/lib/mysql/.tmp/#sql_5f3c_0'

I get the same error if I create the view using SQL SECURITY DEFINER.

The ID I use does not have administrator privileges, but does have all privileges granted at the schema level. I have no problems creating views on tables I create within my database, only when I try to create a view on an information_schema table.

I get the same error when trying to create a view that selects only some of the columns from the information_schema table:
CREATE VIEW `MYDB`.statMetadata2 AS SELECT TABLE_SCHEMA, TABLE_NAME, NON_UNIQUE, INDEX_NAME FROM `information_schema`.`STATISTICS`;

Why can I create a view using "select * from `information_schema`.... " but not when I explicitly name all or some columns in the view creation DDL?

How to repeat:
Create a view in a user database using the DDL:

create or replace `MYDB`.statMetadata as select * from `information_schema`.STATISTICS;

Using the Query Browser Schemata window, right click on the view name and select "Edit View".  The DDL will appear as:
DROP VIEW IF EXISTS `MYDB`.`statMetadata`;
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`myID`@`%` SQL SECURITY INVOKER
VIEW `MYDB`.`statMetadata` AS
select `STATISTICS`.`TABLE_CATALOG` AS `TABLE_CATALOG`,
`STATISTICS`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,
`STATISTICS`.`TABLE_NAME` AS `TABLE_NAME`,
`STATISTICS`.`NON_UNIQUE` AS `NON_UNIQUE`,
`STATISTICS`.`INDEX_SCHEMA` AS `INDEX_SCHEMA`,
`STATISTICS`.`INDEX_NAME` AS `INDEX_NAME`,
`STATISTICS`.`SEQ_IN_INDEX` AS `SEQ_IN_INDEX`,
`STATISTICS`.`COLUMN_NAME` AS `COLUMN_NAME`,
`STATISTICS`.`COLLATION` AS `COLLATION`,
`STATISTICS`.`CARDINALITY` AS `CARDINALITY`,
`STATISTICS`.`SUB_PART` AS `SUB_PART`,
`STATISTICS`.`PACKED` AS `PACKED`,
`STATISTICS`.`NULLABLE` AS `NULLABLE`,
`STATISTICS`.`INDEX_TYPE` AS `INDEX_TYPE`,
`STATISTICS`.`COMMENT` AS `COMMENT`
from `information_schema`.`STATISTICS`;

Attempting to execute the DDL with fully qualified column names will result in view being dropped create statement fails with error 1143 and the message stating that "ANY command denied to user...."

CREATE VIEW `MYDB`.statMetadata2 AS SELECT TABLE_SCHEMA, TABLE_NAME, NON_UNIQUE, INDEX_NAME FROM `information_schema`.`STATISTICS`;

Attempting to execute this DDL will fail with error 1143 and the message stating that "ANY command denied to user...."

This applies to all of the tables in information_schema where I tested this.

Suggested fix:
Allow creation of views on information_schema tables whether the DDL uses "select *", explicitly names all columns, explicitly names a subset of columns.  This should apply to simple views as described here or more complex views that require joins of two or more information_schema tables.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.51a, and inform about the results.

The newest version of MySQL that I have access to is 5.0.41 community running on Windows XP Professional 2002, SP2.

The issue is reproducible in 5.0.41 when I access the instance using an ID that has been granted all permissions on MYDB at the schema level.

The root user does not have this issue in 5.0.41.  

My guess would be that this issue is related to permissions for the limited user ID.  However, if the limited user ID can create the view as "select *" I don't think it should be prevented from creating a view using the fully qualified column names equivalent to "select *" or a limited subset of columns.

Thank you for the feedback.

Please provide output of SHOW GRANTS FOR `myID`@`%`;

In the 5.0.26 version on Linux, myID has the following privileges:
'GRANT USAGE ON *.* TO 'myID'@'%' IDENTIFIED BY PASSWORD '*0AC10D0E02DDDEE2400A6DB12CAFD104863A6C02''
GRANT ALL PRIVILEGES ON `MYDB`.* TO 'myID'@'%'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE VIEW ON `MY-WEB-D`.* TO 'myID'@'%'
as well as GRANT ALL PRIVILEGES on many other databases on this instance

In the 5.0.41 version on Windows, myID has the following privileges:
GRANT USAGE ON *.* TO 'myID'@'%' IDENTIFIED BY PASSWORD '*D98CD257A1E2246DBC8639DF49FACD77BEB7F723'
GRANT ALL PRIVILEGES ON `test`.* TO 'myID'@'%' WITH GRANT OPTION

Thank you for the feedback.

Please upgrade to current verison 5.0.51b and try with it as I can not repeat described behavior with current development version and rights you provided for version 5.0.41

Sorry - I'm not in a position to do that.  I don't have the privileges to upgrade MySQL to version 5.0.51b, and would need more justification than testing for a bug that doesn't materially affect our use of the software.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html