CREATE DATABASE test;

use test;

-- Create a couple tables and fill with data

CREATE TABLE items (id INT PRIMARY KEY, name VARCHAR(255) );

CREATE TABLE properties (item INT, value VARCHAR(255) );

INSERT INTO items (id, name) VALUES (1, 'ball'), (2, 'pencil'), (3, 'bag');

INSERT INTO properties (item, value) VALUES (1, 'red'), (2, 'black'), (3, 'blue');

-- Create a user which can only update the value column, on the real table

CREATE USER 'testuser1'@'%' IDENTIFIED BY 'demo';

GRANT SELECT ON items TO 'testuser1'@'%';

GRANT SELECT ON properties TO 'testuser1'@'%';

GRANT UPDATE (value) ON properties TO 'testuser1'@'%';

-- Create views mapping the tables

CREATE VIEW itemview AS SELECT * FROM items;

CREATE VIEW propertyview AS SELECT * FROM properties;

-- Create a user which can only update the value column, using the views

CREATE USER 'testuser2'@'%' IDENTIFIED BY 'demo';

GRANT SELECT ON itemview TO 'testuser2'@'%';

GRANT SELECT ON propertyview TO 'testuser2'@'%';

GRANT UPDATE (value) ON propertyview TO 'testuser2'@'%';

# testuser1 can update the real table doing a JOIN

mysql -u testuser1 -pdemo test -e "UPDATE items JOIN properties ON (id=item) SET value='yellow' WHERE name='ball';"

# However, testuser2 needs UPDATE right on columns used for joining 

mysql -u testuser2 -pdemo test -e "UPDATE itemview JOIN propertyview ON (id=item) SET value='blue' WHERE name='ball';"

ERROR 1143 (42000) at line 1: UPDATE command denied to user 'testuser2'@'localhost' for column 'item' in table 'propertyview'

# Although it works correctly if moving them to the WHERE clause

mysql -u testuser2 -pdemo test -e "UPDATE itemview JOIN propertyview SET value='blue' WHERE name='ball' AND id=item;"