MySQL Bugs: #59957: View using merge permissions in multi-table update

Bug #59957	View using merge permissions in multi-table update
Submitted:	4 Feb 2011 23:32	Modified:	30 Sep 2011 18:30
Reporter:	Gillian Gunson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.0, 5.1, 5.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A multi-table update where a view is used read-only to select which rows in the update to change, fails with a permission error on the read-only view if the merge algorithm is used and there is only select privilege on the view. The update works fine if the view is replaced by the underlying table, or if the view uses the temptable algorithm.

The error on the multi-table update with the merge view is:

ERROR 1142 (42000): UPDATE command denied to user 'user2'@'localhost' for table 'v1_MERGE'

This has been tested in the latest versions of 5.0, 5.1 and 5.5.

How to repeat:
-- Step 1 - run as root user

CREATE DATABASE db1;
CREATE DATABASE db2;

DROP USER user2; -- add @host info as needed
CREATE USER user2;
GRANT ALL ON db2.* TO user2;
GRANT SELECT ON db1.* TO user2;
FLUSH PRIVILEGES;

USE db1;
CREATE TABLE t1 (
id INT(10) UNSIGNED NOT NULL,
val VARCHAR(20) NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO t1 VALUES(1, 'test1');

CREATE ALGORITHM=TEMPTABLE VIEW v1_TEMP
AS SELECT * FROM t1;

CREATE ALGORITHM=MERGE VIEW v1_MERGE
AS SELECT * FROM t1;

USE db2;

CREATE TABLE t2 (
id INT(10) UNSIGNED NOT NULL,
val VARCHAR(20) NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO t2 VALUES(1, 'test2');

-- Step 2 - run as user2

USE db2;

-- works with table
UPDATE t2, db1.t1 t1
SET t2.val = t1.val
WHERE t2.id = t1.id;

-- works with temptable view
UPDATE t2, db1.v1_TEMP v1
SET t2.val = v1.val
WHERE t2.id = v1.id;

-- fails with merge view
UPDATE t2, db1.v1_MERGE v1
SET t2.val = v1.val
WHERE t2.id = v1.id;

Suggested fix:
Two possibilities:

1. Modify the privileges check with merge view to allow multi-table updates where the user has select privileges on the view and the view data isn't modified by the update, or

2. Document how view privileges are determined differently from tables in this situation.

Possible workarounds:

1. Add UPDATE privilege to user for the view:

GRANT UPDATE on db1.v1_MERGE to user2;

2. Recreate view in DB where the user has UPDATE privileges:

USE db2;
CREATE ALGORITHM=MERGE VIEW v1_MERGE AS SELECT * FROM db1.t1;

3. Rewrite view as TEMPTABLE:

USE db1;
CREATE ALGORITHM=TEMPTABLE VIEW v1_TEMP
AS SELECT * FROM t1;

Noted in 5.6.4 changelog.

A multiple-table UPDATE statement required the UPDATE privilege on a 
view which was only read if the view was processed using the merge 
algorithm.