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?

  • What grants do you currently have? In particular, are they at the table level, or at the column level? – Rick James Jan 17 '20 at 7:13
  • @RickJames The DB user has UPDATE and INSERT grants on the table level of table1. – dbdemon Jan 17 '20 at 11:19

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)

  • I like this explanation. I was looking for an explanation in manuals for DBMSes such as MariaDB, Oracle, PostgreSQL - they all mention that you need the SELECT grant on the table or the specific columns referenced in the WHERE clause, but they don't seem to explain the reason behind this design. – dbdemon Jan 14 '20 at 14:07

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.