MySQL: How do I lock individual rows in a table?

Question

To increase the security of the app I'm working on, I want to be able to protect against any sort of db modification. I know I can lock entire tables from mysql users, but what about individual rows?

Would I still be able to add new rows to the table? maybe for just one particular table, only the SELECT and INSERT commands are allowed?

but then if the records that the views get the data from changes, the data within the views changes as well. — NullVoxPopuli, Jan 30 '12 at 14:38
The title of the post relates to "locking" rows yet the details ask about access control. Understand that the term "lock" in database parlance relates to concurrent access to the data, e.g. a "shared lock" permits other connections to read but not modify the data, versus an "exclusive lock" which blocks other connections from doing anything (isolation level aside). This isn't the same concept as the types of actions one can perform on the data, e.g. select, update, insert, delete. — aingram, Jan 30 '12 at 15:02

score 2 · Answer 1 · answered Jan 30 '12 at 14:14

2

This would be "Row level security". MySQL doesn't have it, so you'd need to implement yourself.

For example, an "AddedBy" column can be used to restrict data changes to other members in the same group. Of course, if the Addedby user changes group you have to track this

To restrict allow INSERT and SELECT only, just GRANT these permissions.

Otherwise, please add more use cases

answered Jan 30 '12 at 14:14

gbn

422,506
82
585
676

1

That's wrong: ["MySQL uses row-level locking for InnoDB tables, and table-level locking for MyISAM, MEMORY, and MERGE tables."](http://dev.mysql.com/doc/refman/5.5/en/internal-locking.html) – feeela Jan 30 '12 at 14:17
2

@feeela: I said "Row level security". I did not say "Row level locking". Read my answer: don't comment on what I didn't write. To help you read these: http://stackoverflow.com/q/7942301/27535 and http://www.dba-oracle.com/concepts/restricting_access.htm – gbn Jan 30 '12 at 14:28
1

I gave this response a +1 because the title of the post asks about individual rows and the description mentions security. Perfectly reasonable response, IMHO. It doesn't deserve the -1 that someone gave it. – aingram Jan 30 '12 at 15:05
Gotta love the downvotes from people who don't read the answer properly. +1 from me for proper answer. – N.B. Jan 30 '12 at 15:34

aingram · Accepted Answer · 2012-01-30T15:12:34.773

It sounds like you want to permit inserting new rows and querying existing rows, but you do not want to permit updating or deleting rows. If that is correct, then you'll want to create a MySQL user that has only INSERT and SELECT privileges on the table(s) in question. Do not grant UPDATE and DELETE privileges.

To grant INSERT and SELECT privileges to user foo on my_table:

GRANT SELECT, INSERT ON my_table TO 'foo'@'localhost';

To revoke UPDATE and DELETE privileges from user foo on my_table:

REVOKE UPDATE, DELETE ON my_table FROM 'foo'@'localhost';

score 0 · Answer 3 · answered Jan 30 '12 at 14:18

0

You could use a specific database users for your application with limited rights (No INSERT, DELETE) for the desired tables.

answered Jan 30 '12 at 14:18

dgw

13,418
11
56
54

MySQL: How do I lock individual rows in a table?

3 Answers3