Why is a SELECT grant needed for an UPDATE statement on a table? (Error 1143)

Question

myuser@'%' has UPDATE and INSERT grants on table1.

I tried to run this UPDATE:

UPDATE table1 SET column1 = 'blahblah' WHERE id = 123;

I got this error:

SELECT command denied to user 'myuser'@'localhost' for column 'id' in table 'table1'

... and error code 1143.

Of course, I understand that the particular UPDATE I was trying to run needs to pick just the particular row I want, probably using the equivalent of a SELECT statement "under the hood" so to speak, to find the row identified by id = 123, but it doesn't seem right that I should need the SELECT grant when I'm not doing an explicit SELECT.

Can someone help me understand the logic that calls for a SELECT grant for this very common sort of UPDATE?

Answer 1

Whilst I can't find anything official to back this up (I will keep trying) I guess a user with UPDATE but not SELECT permissions could brute force their way into knowing the values in the table without having SELECT permissions

lets say the user runs the following query

UPDATE MyTable SET MyCol = 'Something' WHERE Id = 1

and it returns that it successfully updated a row

and then the user runs

UPDATE MyTable SET MyCol = 'Something' WHERE Id = 2

and so on (they could write a loop using front end or database code to try a range of Ids quickly)

This tells the user which values for Id exist in the table without them having SELECT permissions (and this example could be applied to other columns in the table)

Answer 2

The important part is in the error message:

SELECT command denied to user 'myuser'@'localhost' for column 'id' in table 'table1'

If you try UPDATE table1 SET column1 = 'blahblah'; (without the selector WHERE id = 123); you will get no error message and the query will succeed.

This is due to the fact, that the SELECT privilege can not be treated 1:1 as a read privilege. It is actually the privilege to select rows from a table, either for the sake of updating them or for the sake of returning them.