Restrict access to subset of rows in MySQL

Question

I am quite the database noob :-( If I have a simple table designed like so:

CREATE TABLE IF NOT EXISTS picture
(
ID INT NOT NULL AUTO_INCREMENT,
userID INT NOT NULL,
name VARCHAR(150),

PRIMARY KEY(ID),
FOREIGN KEY (userID) REFERENCES user(ID)
)ENGINE=InnoDB

and described, for example, like so:

+----+--------+------+
| ID | userID | name |
+----+--------+------+
|  1 |      1 | john |
|  2 |      1 | jack |
|  3 |      2 | amir |
|  4 |      2 | chan |
|  5 |      2 | jose |
|  6 |      3 | jane |
|  7 |      3 | buba |
+----+--------+------+

How would I design a database which meets the following two constraints:

1. Users 1, 2 and 3 can add new entries to the pictures table.
2. Users 1, 2 and 3 can modify only those rows which have userIDs 1, 2 and 3, respecitvely

I know this might be non trivial, so feel free to ask any additional questions and I will edit this question accordingly.

Answer 1

Drawing off of this answer I put this together:

delimiter //
CREATE TRIGGER trig_picture BEFORE UPDATE ON picture
FOR EACH ROW
BEGIN
    DECLARE dummy INT;
    IF NOT EXISTS (select ID from users where ID = OLD.userID and name = (SELECT SUBSTRING_INDEX(USER(), '@', 1))) THEN
        SET NEW=(SELECT CONCAT("Access Denied for user ", SUBSTRING_INDEX(USER(), '@', 1), "; uid ", OLD.userID));
    END IF;
END;//
delimiter ;

This assumes you have a users table with some entries:

CREATE TABLE IF NOT EXISTS users ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) );
insert into users values (1, 'adam');
insert into users values (2, 'bob');
insert into users values (3, 'clark');

This assumes the names in the users table matches the database user name as returned by SELECT SUBSTRING_INDEX(USER(), '@', 1).

Also, note that this only prevents updates from happening. You'd have to create additional insert/delete triggers as well to prevent creation/deletion of entries.

Finally, this doesn't prevent someone from changing the userID of their entry in the pictures table, essentially locking them out from further edits. If this matters to you, you can add an additional check in the trigger for that.

Answer 2

For first item, you could just grant INSERT permissions to the pictures table for users 1, 2 and 3.

For second item, you could do the following:

A. Create an additional table (db_logins) that contains mappings between userID and database login.

+----+--------+--------------+
| ID | userID | db_loginname |
+----+--------+--------------+
|  1 |      1 | dbLogin1     |
|  2 |      2 | dbLogin2     |
|  3 |      3 | dbLogin3     |
+----+--------+--------------+

B. Whenever you do an update, add check for db_loginname to WHERE clause. The UPDATE statement will look something like this (I don't have access to MySQL instance at the moment, so I can't verify the syntax):

UPDATE pictures
SET name = 'new name'
FROM pictures
INNER JOIN db_logins 
    ON pictures.userID = db_logins.userID
WHERE name = 'buba' 
   AND db_loginname = user()