Restrict a MySQL foreign key based on another column value ("foreign key to a view's column")?

Question

My main goal is to have a database user that I can restrict access rights so they won't see some confidential stuff, while still being able to use foreign keys for integrity checks. So the foo user below must be allowed to only see the PUBLIC items, while still being able to make a foreign key on their other_data table so that table won't contain any id_item value theyr wouldn't be able to see.

Here is the setup:

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

I'm using mysql 5.7 here, so I could have use a generated column in the items table that would be like id_if_public INT UNSIGNED GENERATED ALWAYS AS IF(visibility = 'PUBLIC', id, NULL) STORED and put my FK on this column but generated columns forbid referencing an auto-increment (id) column...

I could move to MySQL 8 for the CHECK syntax to be used on other_data like CHECK EXISTS(SELECT 1 FROM public_items WHERE public_items.id = other_data.id_item) but is seems CHECK does not allow subqueries...

I could turn the public_items into a table and "maintain" it using TRIGGER and such, but it feels very heavy and complex, and requires extra storage for the item_name so it's not perfectly extensible (ie: the more columns in items, the heavier!)

score 0 · Answer 1 · answered Dec 20 '19 at 12:34

Having such a new table public_items isn't a bad idea since you need something to have a foreign key on. You can combine it with a view to get the meta information from the items table so you don't need to add them in the new public_items table twice. The tables should look like this:

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

Now you create a new table public_items with the foreign key over (id, visibility), but enforce that only "PUBLIC" entries can be used.

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

Now you can only add rows from items in public_items which are public and you can reference these rows by a foreign key. Your other_data table has to change the foreign key to this new public_items table.

Obviously that approach has some drawbacks:

You have to maintain the list of public items in public_items with entries from the "source" table items. You can use TRIGGERs or a background process to keep these entries in sync or any other approach.
The public_items table does not have the item_name column. You might still need a view to get the public items with their meta information from the items table if you don't want to copy these meta information to the public_items table (with the sync/update process mentioned above).

Restrict a MySQL foreign key based on another column value ("foreign key to a view's column")?

1 Answers1