MySQL Bugs: #9035: Views: View creation should include automatic view use privileges

Bug #9035	Views: View creation should include automatic view use privileges
Submitted:	8 Mar 2005 0:57	Modified:	30 Sep 2008 16:54
Reporter:	Trudy Pelzer	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.0.3-alpha-debug	OS:	Linux (SuSE 9.1)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
A user who is allowed to create a view should also
be allowed, automatically, to use that view. This
is not happening: right now, if I create a view, I cannot
even SELECT from it. The SQL Standard says that the
creator of a view automatically gets the same privileges
on the view that he/she holds on the underlying tables,
except in cases where this doesn't make sense because
the view is not updatable. (That is, if I create a view that
is not updatable, then I do not get INSERT, UPDATE, or
DELETE privileges on the view even though I hold those
privileges on the underlying table).

MySQL should follow the Standard as regards automatic
view privilege grants.

How to repeat:
-- sign on as root
mysql> select current_user;
+----------------+
| current_user   |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)

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

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

mysql> grant select on t1 to me;
Query OK, 0 rows affected (0.00 sec)

mysql> create view v1 as select * from t1;
Query OK, 0 rows affected (0.00 sec)
mysql> grant create view on v1 to me;
Query OK, 0 rows affected (0.00 sec)
mysql> drop view v1;
Query OK, 0 rows affected (0.00 sec)
-- This workaround is needed to avoid bug#9034,
which doesn't allow one to GRANT CREATE VIEW
unless the view already exists.

-- sign on as me
mysql> select current_user;
+--------------+
| current_user |
+--------------+
| me@%         |
+--------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+-------+
| col1 | col2  |
+------+-------+
|   10 | trudy |
+------+-------+
1 row in set (0.00 sec)

mysql> create view v1 as select * from t1;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v1;
ERROR 1142 (42000): SELECT command denied to user 'me'@'localhost' for table 'v1'
-- This is the incorrect response. If the user can
create the view, he/she should also get appropriate
privileges on the view. The SQL Standard says that
the user should get all privileges that make sense;
that is, whatever the user can do to the underlying
table should also be possible on the view. Since the
user has SELECT on all columns of table t1, she should
have SELECT on all columns of view v1 too. No other
privileges on the view should be automatically granted 
though, since no other privileges have been granted on 
the table.

Has this been fixed?

I couldn't repeat:

mysql 5.0 > select * from t1;
+------+-------+
| col1 | col2  |
+------+-------+
|   10 | trudy |
+------+-------+
1 row in set (0.02 sec)

mysql 5.0 > create view v1 as select * from t1;
Query OK, 0 rows affected (0.02 sec)

mysql 5.0 > select * from v1;
+------+-------+
| col1 | col2  |
+------+-------+
|   10 | trudy |
+------+-------+
1 row in set (0.03 sec)

mysql 5.0 > show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.0.72-nt-debug-log |
| version_comment         | Source distribution |
| version_compile_machine | ia32                |
| version_compile_os      | Win32               |
+-------------------------+---------------------+
5 rows in set (0.05 sec)

mysql 5.0 >

The problem appears when the user crating does not have the global select privilige for the database. If the user has the select privilege on table level he can create a view on it but he wont have privileges on the view.