MySQL Bugs: #18681: View privileges are broken

Bug #18681	View privileges are broken
Submitted:	31 Mar 2006 10:38	Modified:	26 May 2006 16:39
Reporter:	Per Holm	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.21-BK, 5.0.18	OS:	Linux (Linux, Mac OS X 10.4.5)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
I am confused about views and view privileges. I posted a question about it in the Views forum, but I haven't got any replies.

The manual says this in section 19.2:

"At view execution time, privileges for objects accessed by the view are checked against the privileges held by the view creator or invoker, depending on whether the SQL SECURITY characteristic is DEFINER or INVOKER, respectively."

This I take to mean that if a view is defined as "create security INVOKER view v_t as select * from t", and a user only has the SELECT privilege on t, he shouldn't be able to update the base table t in any way, neither directly nor via the view. This should be regardless of what privileges the user has on the view v_t.

But this he can do -- if he has delete and update privileges on v_t he can "delete from v_t;" and "update v_t set ...", and the base table t is changed. It seems that deletions and updates don't check "privileges for objects accessed by the view" at all, only privileges on the view.

This seems unreasonable to me, and I thought is was an obvious bug. But then I read in the article http://dev.mysql.com/tech-resources/articles/mysql-views.pdf, page 15:

"Special rule: If you have a privilege to perform an operation on a view, you don't need the same privilege on the underlying tables. That is, if you have the UPDATE privilege on view v, which is based on table t, you can update v -- assuming that v is updatable. There is no requirement that you have the UPDATE privilege on t as well."

Is this really the intended behaviour? If it is, why doesn't the manual say so?

Finally: in the example below, why is there a difference between "delete from v_t where x = 1;" and "delete from v_t;"?

How to repeat:
-- do this as root:

use test;
create table t (x int);
insert into t values (1), (2);
create SQL SECURITY INVOKER view v_t as select * from t;
create user readonly;
grant select on test.t to readonly;
grant select, insert, update, delete on test.v_t to readonly;

-- now log in as readonly

per% mysql -u readonly -A test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 152900 to server version: 5.0.18-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from v_t;
+------+
| x    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> -- THE FOLLOWING MESSAGES ARE AS I EXPECTED:
mysql> insert into v_t values (4);
ERROR 1356 (HY000): View 'test.v_t' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql> delete from v_t where x = 1;
ERROR 1356 (HY000): View 'test.v_t' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql> 
mysql> -- THE FOLLOWING WORK, BUT SHOULD NOT (?)
mysql> update v_t set x = 3 where x = 2;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t;
+------+
| x    |
+------+
|    1 |
|    3 |
+------+
2 rows in set (0.01 sec)

mysql> delete from v_t;
Query OK, 2 rows affected (0.01 sec)

mysql> select * from t;
Empty set (0.00 sec)

Thank you for a bug report. Verified just as described with 5.0.21-BK (ChangeSet@1.2124, 2006-03-30 11:34:14-08:00) on Linux. With a separate newly created testv database:

mysql> create user readonly@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> create database testv;
Query OK, 1 row affected (0.00 sec)

mysql> use testv;
Database changed
mysql> create table t (x int);
inQuery OK, 0 rows affected (0.01 sec)

mysql> insert into t values (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create SQL SECURITY INVOKER view v_t as select * from t;
Query OK, 0 rows affected (0.01 sec)

mysql> grant select on testv.t to readonly;
Query OK, 0 rows affected (0.01 sec)

mysql> grant select, insert, update, delete on testv.v_t to readonly;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysql -ureadonly testv
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 12 to server version: 5.0.21

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from v_t;
+------+
| x    |
+------+
| 1    |
| 2    |
+------+
2 rows in set (0.00 sec)

mysql> insert into v_t values(4);
ERROR 1356 (HY000): View 'testv.v_t' references invalid table(s) or column(s) or
 function(s) or definer/invoker of view lack rights to use them
mysql> delete from v_t where x = 1;
ERROR 1356 (HY000): View 'testv.v_t' references invalid table(s) or column(s) or
 function(s) or definer/invoker of view lack rights to use them
mysql> update v_t set x = 3 where x = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t;
+------+
| x    |
+------+
| 1    |
| 3    |
+------+
2 rows in set (0.00 sec)

mysql> delete from v_t;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t;
Empty set (0.00 sec)

So, if manual (http://dev.mysql.com/doc/refman/5.0/en/create-view.html) is right:

"At view execution time, privileges for objects accessed by the view are checked against the privileges held by the view creator or invoker, depending on whether the SQL SECURITY characteristic is DEFINER or INVOKER, respectively."

then there is a bug here. With UPDATE allowed. The other bug is with DELETE without WHERE is allowed (while DELETE with WHERE is NOT)!

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6623

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6900

pushed in 5.0.23-BK

Noted in 5.0.23 changelog.

Several aspects of view privileges were being checked incorrectly.