mysql - 基于另一个列值 ("foreign key to a view' s 列限制 MySQL 外键”)？

Question

我的主要目标是拥有一个可以限制访问权限的数据库用户，这样他们就不会看到一些 secret 内容，同时仍然能够使用外键进行完整性检查。因此，必须允许下面的 foo 用户仅查看 PUBLIC 项，同时仍然能够在其 other_data 表上创建外键，以便该表不会包含他们看不到的任何 id_item 值。

这是设置:

-- These are items with a visibility
CREATE TABLE items (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    item_name VARCHAR(50) NOT NULL,
    visibility ENUM('PUBLIC','PRIVATE','DELETED') NOT NULL DEFAULT 'PRIVATE',
    INDEX (visibility),
    UNIQUE INDEX (item_name)
)
ENGINE=INNODB;

INSERT INTO items (id, item_name, visibility) 
    VALUES (1, 'x', 'PUBLIC'), (2, 'y', 'PRIVATE'), (3, 'z', 'DELETED'), (4, 'xprime', 'PUBLIC'), (5, 'yprime', 'PRIVATE'), (6, 'zprime', 'DELETED');

-- This view only shows the public items (not private ones, not deleted ones)
CREATE VIEW public_items AS 
    (SELECT id, item_name FROM items WHERE visibility = 'PUBLIC');

-- How can I make this table definition only allow id_item to be a value from public_items.id?
-- I cannot use the view in the constraint, tho it would be perfect solution
CREATE TABLE other_data (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    label VARCHAR(50) NOT NULL,
    id_item INT UNSIGNED NOT NULL,
    UNIQUE KEY (label),
    CONSTRAINT FOREIGN KEY (id_item) REFERENCES /*public_*/items (id)
        ON UPDATE CASCADE ON DELETE RESTRICT
)ENGINE=INNODB;


-- Let's create the foo user that I want to restrict access rights
CREATE USER 'foo'@'localhost' IDENTIFIED BY 'bar';
GRANT USAGE ON *.* TO 'foo'@'localhost';
GRANT SELECT ON test_sql.public_items TO 'foo'@'localhost';
GRANT SELECT,INSERT ON test_sql.other_data TO 'foo'@'localhost';
FLUSH PRIVILEGES;

-- Now, use foo@localhost user

-- This should return (1;4) because foo must be allowed to see the public items => OK
SELECT * FROM public_items;

-- This should be DENIED because foo is not allowed to see all other items => OK
SELECT * FROM items;

-- This should be ALLOWED because foo is allowed to insert rows in other_data => OK
-- and this row refers to a public item
INSERT INTO other_data (id, label, id_item) VALUES (11, 'allowed-public', 1);

-- This should be ALLOWED because foo is allowed to see all the other_data => OK
SELECT * FROM other_data;

-- This should be FORBIDDEN because the item is not public => FAIL, it can be inserted...
INSERT INTO other_data (id, label, id_item) VALUES (12, 'forbidden-private', 2);

-- This should be FORBIDDEN because the item is not public => FAIL, it can be inserted
INSERT INTO other_data (id, label, id_item) VALUES (13, 'forbidden-deleted', 3);

-- This should be FORBIDDEN because the item does not exist => OK, it cannot be inserted
INSERT INTO other_data (id, label, id_item) VALUES (19, 'forbidden-notexist', 9);

我在这里使用 mysql 5.7，所以我可以在 items 表中使用生成的列，类似于 id_if_public INT UNSIGNED GENERATED ALWAYS AS IF(visibility = 'PUBLIC' , id, NULL) STORED 并将我的 FK 放在该列上，但生成的列禁止引用自动增量 (id) 列...

我可以迁移到 MySQL 8，以便在 other_data 上使用 CHECK 语法，例如 CHECK EXISTS(SELECT 1 FROM public_items WHERE public_items.id = other_data. id_item) 但似乎 CHECK 不允许子查询...

我可以将 public_items 转换为一个表，并使用 TRIGGER 等“维护”它，但感觉非常沉重和复杂，并且需要额外的存储空间 item_name 因此它不是完全可扩展的(即:items 中的列越多，就越重!)

Answer 1

拥有这样一个新表public_items并不是一个坏主意，因为您需要有外键的东西。您可以将其与 View 结合起来，从 items 表中获取元信息，这样您就不需要将它们添加到新的 public_items 表中两次。这些表格应如下所示:

CREATE TABLE items (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    item_name VARCHAR(50) NOT NULL,
    visibility ENUM('PUBLIC','PRIVATE','DELETED') NOT NULL DEFAULT 'PRIVATE',
    INDEX (visibility),
    UNIQUE INDEX (item_name),
    UNIQUE INDEX (id, visibility) /* new index over 'id' and 'visibility' */
);

现在，您创建一个新表 public_items，其外键位于 (id, Visibility) 上，但强制仅包含 “PUBLIC” 条目可以使用。

CREATE TABLE public_items (
    id INT UNSIGNED NOT NULL PRIMARY KEY,
    visibility ENUM('PUBLIC') NOT NULL, /* only allow public items */

    CONSTRAINT FK_public_items_id_visibility FOREIGN KEY (id, visibility) 
                                             REFERENCES items(id, visibility)
);

现在，您只能添加来自 public_items 中的 items 的行，这些行是公共(public)的，并且您可以通过外键引用这些行。您的 other_data 表必须将外键更改为这个新的 public_items 表。

显然这种方法有一些缺点:

• 您必须使用“源”表 items 中的条目维护 public_items 中的公共(public)项目列表。您可以使用触发器或后台进程或任何其他方法来保持这些条目同步。
• public_items 表没有 item_name 列。如果您不想将这些元信息复制到 public_items 表(使用上面提到的同步/更新过程)。