MySQL Bugs: #7091: Views: CREATE VIEW privilege doesn't work

Bug #7091	Views: CREATE VIEW privilege doesn't work
Submitted:	8 Dec 2004 0:54	Modified:	13 Jun 2006 8:36
Reporter:	Trudy Pelzer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	WIN -5.0.21 LIN 5.0.22	OS:	Windows (win32 - XP SP2, Fedora core 5)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
Paraphrasing WL#941: 
To create a view:  
-- User must have CREATE VIEW privilege 
-- User must have some privilege on each column of underlying table 
 
But if I grant SELECT and CREATE VIEW to a user, 
that user is still not able to create a view. 

How to repeat:
-- I sign on as root: 
mysql> create table t1 (col1 int not null primary key, col2 varchar(10)); 
Query OK, 0 rows affected (0.02 sec) 
mysql> insert into t1 values(10,'trudy'); 
Query OK, 1 row affected (0.00 sec) 
mysql>create user trudy; 
Query OK, 1 row affected (0.00 sec) 
mysql> grant select,create view on t1 to trudy; 
Query OK, 0 rows affected (0.00 sec) 
 
-- I sign on as trudy: 
mysql> select current_user; 
+--------------+ 
| current_user | 
+--------------+ 
| trudy@%      | 
+--------------+ 
1 row in set (0.00 sec) 
 
mysql> create view vt1 as select * from t1 where col1<50; 
ERROR 1142 (42000): create view command denied to user 'trudy'@'localhost' for table 
'v941_3' 
-- This is the incorrect response; the CREATE VIEW  
should work since user trudy has CREATE VIEW and SELECT 
privileges on underlying table.

Just modified how the privileges were done:

mysql> use test;
Database changed
mysql> create table t1 (col1 int not null primary key, col2 varchar(10)); 
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(10,'trudy'); 
Query OK, 1 row affected (0.03 sec)

mysql> create user trudy;
Query OK, 0 rows affected (0.01 sec)

mysql> grant select,create view on test.t1 to "trudy"@"localhost" identified by "trudy";
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
miguel@hegel:~/dbs/5.0$ bin/mysql -utrudy -ptrudy test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.3-alpha-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create view vt1 as select * from t1 where col1<50;
Query OK, 0 rows affected (0.02 sec)

Note to Miguel: 
The problem seems to be related to the database in use. 
When I used the test database, as you did, it worked. But 
when I did the same sequence on my own database -- called 
tp -- the problem with the CREATE VIEW privilege not working 
still exists: 
 
trudy@linux:~> /usr/local/mysql/bin/mysql --user=root 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 8 to server version: 5.0.3-alpha-debug 
 
mysql> use tp; 
Database changed 
 
mysql> create table t1 (col1 int not null primary key, col2 varchar(10)); 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert into t1 values (10,'trudy'); 
Query OK, 1 row affected (0.01 sec) 
 
mysql> create user trudy; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> grant select,create view on tp.t1 to "trudy"@"localhost" identified by 
"trudy"; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> exit 
Bye 
trudy@linux:~> /usr/local/mysql/bin/mysql -utrudy -ptrudy tp 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 9 to server version: 5.0.3-alpha-debug 
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
 
mysql> create view vt1 as select * from t1 where col1<50; 
ERROR 1142 (42000): create view command denied to user 'trudy'@'localhost' for 
table 'vt1'

Thank you for the bug report and clarification.

This is not a bug after all; the problem is caused by the
fact that the CREATE VIEW privilege names the view
that can be created, rather than the table that the 
view may be based upon.

I am having the same problem granting the create view privilege to a user.  The grant seems to work, but when I log on as the user and try to "create view...", 
I get ERROR 1142(42000): create view...

I don't understand Trudy's last comment that "this is not a bug after all; the
problem is caused by the fact that the CREATE VIEW privilege names the view that can be created..."  What is the syntax I should be using?  Currenly, I've been using:

grant select, create view on test.* to "user1"@"localhost";

which to me means that user1 should be able to create a view on any table(s) in the test database??