MySQL Bugs: #20081: can't create a view on information

Bug #20081	can't create a view on information_schema.tables
Submitted:	26 May 2006 1:20	Modified:	1 Jun 2006 0:18
Reporter:	Greg Mierle	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.00.2195	OS:	Windows (windows)
Assigned to:		CPU Architecture:	Any

Description:
An ordinary user cannot create a view on tables in information_schema database.   Attempting to create a view on any tables(s) in information_schema generates an error similar to:

ERROR 1143 (42000): ANY command denied to user 'zzUser'@'localhost' for column 'TABLE_NAME' in table 'F:\WINNT\TEMP\#sql_25c_0'

The problem does not occur with the root account.

How to repeat:
As root:

mysql> create schema zzTestView;
Query OK, 1 row affected (0.06 sec)

mysql> create user 'zzUser'@'localhost';
Query OK, 0 rows affected (0.02 sec)

mysql> grant all on zzTestView.* to 'zzUser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

As zzUser:

mysql> use zzTestView
Database changed
mysql> create view test as select table_name from information_schema.tables;
ERROR 1143 (42000): ANY command denied to user 'zzUser'@'localhost' for column '
TABLE_NAME' in table 'F:\WINNT\TEMP\#sql_25c_0'

Suggested fix:
The problem appears to lie in permissions on a temporary table.  Adjustment of the permissions may alleviate the problem.

This is a duplicate of bug report #19953.

The error message reported in #19953 is similar, and there may be a common underlying cause.  However, in the example that I provided the user account was  granted all privileges, and the error should not have arisen from the absence of SELECT privileges.

Hi Greg. User needs "SELECT_PRIV" on "information_schema" database for this to work which is obviously a bug. Will add note to #19953.

Hmmm.  You're right. There were no privileges for the zzUser account on the Information_schema db in the example.  The weird thing is that I can query the information_schema tables as the zzUser with select commands.  Another bug? A design feature?

Greg

Greg, I think this is a bug, as I said in previous post. Please check progres in original report:
http://bugs.mysql.com/bug.php?id=19953

My point was that it looks to me like there are two distinct bugs: 1. a failure to assign appropriate privileges, and 2. allowing selects on information_schema eventhough privileges weren't granted.  But if you regard this as one bug, then that's ok.  I'm just trying to be helpful.  

I have taken a different tact and its not an issue for me anymore.

And you are Greg, thanks.