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.