Bug #5152 Views: Too many privilege requirements

• Submitted: 23 Aug 2004 1:13
• Modified: 17 Sep 2004 18:24
• Reporter: Peter Gulutzan
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.2-alpha-debug
• OS: Linux (SuSE 8.2)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[23 Aug 2004 1:13] Peter Gulutzan

Description:
Why do I need database-wide privileges to create a view? 
Suppose I'm saying: 
CREATE VIEW v AS SELECT * FROM t; 
I need CREATE VIEW privilege, okay. 
Plus I need SELECT privileges on table t, okay. 
But if I only have table privileges, it doesn't work. 
I need SELECT privileges on everything in database, e.g. 
GRANT SELECT ON db5.* TO user5; 
If I have database-wide privileges, it works. 
But I don't see why I need them. 
 

How to repeat:
As user root, say: 
create database d1; 
use d1; 
create table t (col1 int); 
grant create view on d1.* to user5; 
grant select, insert on t to user5; 
 
As user user5, say: 
use d1; 
create view v as select * from t; -- fails 
 
As user root, say: 
grant insert, select on d1.* to user5; 
 
As user5, say: 
create view v as select * from t; -- succeeds

[16 Sep 2004 5:36] Oleksandr Byelkin

Any privileges are required on view fields which should be created, but not more then user 
has on correspondent field of underlaying table (or SELECT privilege in case of expression). 
 
If above requirement is too strict, we can easy chenge it to allow create unusable VIEWs. 
 
And privileges can be for table or field (not always database global): 
mysql> create database d1; 
Query OK, 1 row affected (0.00 sec) 
 
mysql> use d1; 
Database changed 
mysql> create table t (col1 int); 
Query OK, 0 rows affected (0.02 sec) 
 
mysql> create table v (col1 int); 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> grant create view on d1.* to user5@localhost; 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> grant select, insert on t to user5@localhost; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> grant select on v to user5@localhost; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> drop table v; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> \q 
Bye 
[bell@sanja mysql-test]$ ../client/mysql -u user5 d1 
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 
 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 4 to server version: 5.0.2-alpha-valgrind-max-debug-log 
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
 
mysql> create view v as select * from t; 
Query OK, 0 rows affected (0.00 sec)

[16 Sep 2004 21:18] Oleksandr Byelkin

ChangeSet 
  1.1764 04/09/17 00:16:57 [e-mail] +3 -0 
  now we allow to careate VIEW without any privileges on view columns (except create 
view privilege) (BUG#5152)

[17 Sep 2004 18:24] Oleksandr Byelkin

Thank you for bugreport! Bug is fixed and patch is pushed into our source repository.