CODEWITHSUNDEEP
mysql
Edward Falk
Edward Falk

Reputation: 10083

How can I update a specific record without SELECT permission?

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.

Upvotes: 4

Views: 3980

Answers (4)

Edward Falk
Edward Falk

Reputation: 10083

Oooh! I've got it. I'll give my user SELECT privilege for just the recno column.

Leaving the question open though, just in case someone comes up with a better answer.

Upvotes: 3

Bohemian
Bohemian

Reputation: 425198

This is easily achieved by granting the correct level of access:

REVOKE ALL ON TABLE GRADES FROM USER1; -- to clear out all privileges
GRANT INSERT, UPDATE, DELETE ON TABLE GRADES TO USER1;

This will allow USER1 to insert, update and delete, but not select, from the grades table.

Change USER1 to PUBLIC or some group or whatever as you need.
Change the permissions list as you need.


Your situation is a version of a classic example in an IBM database course I used to teach: Financial staff are allowed to give a % pay rise, but not to see what the pay level is, for employees, so

GRANT UPDATE ON TABLE EMPLOYEE TO ACCT_STAFF; -- an no other privileges

Which allows them to give a 15% pay rise by executing:

UPDATE EMPLOYEE SET PAY = PAY * 1.15 WHERE EMPLOYEE_ID = 666;

Upvotes: 1

kba
kba

Reputation: 19466

Create a VIEW and grant the user full access to it. This view should only contain the rows which that you want the user to be able to edit.

A different, and possibly more suitable approach, would be to completely abstract this mechanism away from the DBMS and instead create an API that you allow your users to query.

Upvotes: 2

wallyk
wallyk

Reputation: 57784

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.

Upvotes: 8

Related Questions