This is something I can’t seem to find information on.
Let’s say I have a table users, and for security purposes, I want any SQL query to only executable if a reference to the id columns is made.
E.g. this should NOT work:
UPDATE users SET source="google" WHERE created_time < 20210303;
The above update statement is syntactically valid, but because it isn’t making a reference to the id column, it should not be executable.
Only the below would be executable:
UPDATE users SET source="google" WHERE id in (45,89,318);
Is there any way to enforce this from the MySQL server’s end?
Advertisement
Answer
I think the only way you can really do what you want is to use a stored procedure, where you pass in the ids and to the update there. You would set up the security as:
- Turn off updates to the underlying table for all-but-one user.
- Run the stored procedure as the user with permissions to modify the table (using
DEFINER).
This will be cumbersome because you will need to pass in all the values in the table.
You can come close with safe update mode. However, that also allows LIMIT as well as key comparisons, so that is not sufficient for your purposes.
Note: This sort of issue is usually handled in another way. Most users would not have permissions to modify such a table. Then “special” users who do would be assumed to be more knowledgable and careful about changes. If the data is sensitive, then the changes would be logged, so it would be (relatively) easy to undo changes that have been made.