mysql - 限制对 MySQL 中行子集的访问

Question

我是个数据库菜鸟 :-( 如果我有一个像这样设计的简单表:

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

并描述，例如，像这样:

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

我将如何设计满足以下两个约束的数据库:

1. 用户 1 , 2和 3可以向 pictures 添加新条目表。
2. 用户 1 , 2和 3可以修改只有那些行有userID s 1 , 2和 3 , 分别

我知道这可能很重要，所以请随时提出任何其他问题，我会相应地编辑这个问题。

Answer 1

绘制 this answer我把它放在一起:

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 ;

假设您有一个包含一些条目的 users 表:

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');

假设 users 表中的名称与 SELECT SUBSTRING_INDEX(USER(), '@', 1) 返回的数据库用户名相匹配。

另请注意，这只会阻止更新的发生。您还必须创建额外的插入/删除触发器，以防止创建/删除条目。

最后，这不会阻止某人更改他们在图片表中的条目的用户 ID，实际上将他们锁定在进一步编辑之外。如果这对您很重要，您可以为此在触发器中添加额外的检查。