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

[8 Dec 2004 0:54] Trudy Pelzer
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.
[8 Dec 2004 3:02] MySQL Verification Team
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)
[8 Dec 2004 21:39] Trudy Pelzer
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'
[9 Dec 2004 18:20] MySQL Verification Team
Thank you for the bug report and clarification.
[8 Mar 2005 0:32] Trudy Pelzer
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.
[7 Feb 2008 21:42] Sharon Banting
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??

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.058 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.