Bug #9034 Views: Can't GRANT CREATE VIEW unless view already exists
Submitted: 8 Mar 2005 0:25 Modified: 24 Apr 2006 7:02
Reporter: Trudy Pelzer Email Updates:
Status: Verified 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: Assigned Account CPU Architecture:Any

[8 Mar 2005 0:25] Trudy Pelzer
Description:
The CREATE VIEW privilege cannot be granted unless
the view for which the privilege is granted already
exists. But since there can only be one table/view 
with the same name, this means that a user who is
granted the CREATE VIEW privilege can never create
the view regardless. This is different behaviour from 
the CREATE privilege, which can be granted for a table
that doesn't already exist, thus allowing the grantee
to make use of the privilege.

Note1:
Ideally, the CREATE and CREATE VIEW privileges should
not be object privileges at all -- they should be system
privileges and the ON clause should not accept any
table name.

Note2:
This is related to Bug#7091, which was written up based
on a false understanding of how the CREATE VIEW 
privilege should work.

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

mysql> select * from t;
ERROR 1146 (42S02): Table 'tp.t' doesn't exist
mysql> grant create on t to me;
Query OK, 0 rows affected (0.03 sec)
-- GRANT CREATE works even though table t does not exist

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

mysql> use tp;
Database changed
mysql> create table t (col1 int);
Query OK, 0 rows affected (0.01 sec)
-- This is the correct response; me can create the table t.

mysql> create table t1 (col1 int);
ERROR 1142 (42000): CREATE command denied to user 'me'@'localhost' for table 't1'
-- This is the correct response; me hasn't been granted the privilege
to create a table called t1.

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

mysql> select * from v;
ERROR 1146 (42S02): Table 'tp.v' doesn't exist
mysql> grant create view on v to me;
ERROR 1146 (42S02): Table 'tp.v' doesn't exist
-- This differs from the CREATE [TABLE] implementation.
Although I can GRANT CREATE for a table that doesn't already
exist, I cannot GRANT CREATE VIEW for a view that doesn't
already exist. But if I create the view so that I can grant
the privilege, what's the point? There can only be one view
with the same name.

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

mysql> create view v as select * from t;
ERROR 1142 (42000): CREATE VIEW command denied to user 'me'@'localhost' for table 'v'
-- This is as expected, since root was not able to GRANT
CREATE VIEW ON v to me. But the GRANT should be allowed.

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

mysql> create view v as select * from t;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on t to me;
Query OK, 0 rows affected (0.00 sec)
mysql> grant create view on v to me;
Query OK, 0 rows affected (0.00 sec)
-- Now root has created view v, so the GRANT is accepted.

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

mysql> create view v as select * from t;
ERROR 1050 (42S01): Table 'v' already exists
-- Now me has the privilege to create the view, but still 
can't -- because the view already exists. Deadlock.
[25 Jan 2006 19:12] Wolfgang Fahl
Good one trudy - I just don't understand why such a severe bug is considered "Non critical". The system is useless if views can not be created in such a situation.