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: | |
| 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
[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.