How can I update a specific record without SELECT permission?

Question

I want to execute something like this:

  UPDATE grades SET status="pass" WHERE recno=123;

However, I want the user account doing the update to have write-only access to the database which means it does not have SELECT access. This causes the WHERE clause to fail.

If necessary, I can actually re-write the entire record, but recno is the primary key, which would cause the write to fail. Is there a way to do

INSERT INTO grades (recno,name,status,...) VALUES (123, 'chemistry', 'pass',...)
  ON DUPLICATE KEY UPDATE <everything>;

or is this the wrong approach? Besides, it's not the general solution to the "update a specific field in a specific record" problem.

Answer 1

That is a peculiar way to protect a table. It might make sense in some cases, though usually you want to provide some window of visibility so users are not blind to their effects on data.

To completely implement a write-only database, use stored procedures. The procedures can have full access to the database, and users can be granted access only to stored procedures.