CODEWITHSUNDEEP
mysqlmariadbcommon-table-expression
CDuv
CDuv

Reputation: 2260

Find SQL rows associated to other using 1:1 linking tables and CTE (eg. permissions on users and groups)

The problem

I need to determine if an user U can access an entity E according to permissions stored in MySQL tables (on MariaDB 10.2) with the following rules:

The tables are already in place and the way I understand my problem it falls down to: "Is there a link between the row of user U and the row of entity E (via any possible path)?"

Because the tables are no graphs (see details below) and because of groups inheritance I think I can use Common Table Expressions here (which I am not familiar with), with recursion (even worse for me).

My attempt

So I built the following SQL query to support direct entity-user allowance (first CTE: cte_entities_allowed_to_users)

SET @TEST_EID = 302;
SET @TEST_UID = 103;

WITH
cte_entities_allowed_to_users AS (
    SELECT
        DISTINCT
        entities.eid,
        entity_user_access.uid
    FROM
        entity_user_access
        JOIN entities
            ON entity_user_access.eid = entities.eid
)
SELECT
    COUNT(*)
FROM
    cte_entities_allowed_to_users
WHERE
    cte_entities_allowed_to_users.eid = @TEST_EID
    AND cte_entities_allowed_to_users.uid = @TEST_UID

And also built the following other CTE:

cte_groups_of_users AS (
    SELECT
        DISTINCT
        users.uid,
        groups.gid
    FROM
        groups
        JOIN group_members
            ON group_members.gid = groups.gid
        JOIN users
            ON users.uid = group_members.uid
)
,
cte_entities_allowed_to_groups AS (
    SELECT
        DISTINCT
        entities.eid,
        entity_group_access.gid
    FROM
        entity_group_access
        JOIN entities
            ON entity_group_access.eid = entities.eid
)

Where I struggle

But I am not sure:

What I expect ("truth table")

For the following @TEST_EID and @TEST_UID pairs here are the expected returned value for the SELECT (the COUNT(*) field)):

@TEST_EID @TEST_UID Return Why
301 101 ≥1 entity_user_access has a (301,101) row
301 102 ≥1 entity_user_access has a (301,102) row
301 103 =0
302 101 =0
302 102 ≥1 entity_group_access has a (302,201) row and group_members has a (201,102) row
302 103 ≥1 entity_user_access has a (302,103) row
303 101 =0
303 102 ≥1 (303,201) row in entity_group_access and group_members has a (201,102) row
303 103 ≥1 entity_user_access has a (303,103) row
304 101 ≥1 (304,205) row in entity_group_access and group_of_groups has a(204,205) row and group_members has a (204,101) row
304 102 ≥1 (304,201) row in entity_group_access and group_members has a (201,102) row
304 103 ≥1 (304,205) row in entity_group_access and group_of_groups has a(202,205) row and group_members has a (202,103) row

Annex 1/1: Tables details

Relations:


      [users]───────────────[group_members]─────[groups]──┐
         │                       │                 │      │
         │                       │                 └──[group_of_groups]
[entity_user_access]   [entity_group_access]
         │                       │
         └────────┐    ┌─────────┘
                  │    │
                [entities]

Some example dataset:

users table:

uid uname
101 Alice
102 Bob
103 Charlie

groups table:

gid gname
201 Administrators
202 Users
203 Operators
204 Guests
205 X-Mas event

entities table:

eid ename
301 Foo
302 Bar
303 Qux
304 Snow

entity_user_access table:

eid uid
301 101
301 102
302 103
303 103

entity_group_access table:

eid gid
301 201
302 201
303 201
304 201
302 203
304 205

group_members table:

gid uid Comment (not part of data)
201 102 Bob is an admin
203 102 Bob is also an operator
202 103 Charlie is an user
204 101 Alice is a guest

group_of_groups table:

gid parent_gid Comment (not part of data)
201 203 Admins (201) are Operators (203)
202 205 Users (202) are in the X-Mas event group (205)
204 205 Guests (204) are in the X-Mas event group (205)

Full SQL:

-- Structure
CREATE TABLE `users` (
    `uid` INT(11) NOT NULL,
    `uname` TINYTEXT NOT NULL,
    PRIMARY KEY (`uid`)
);
CREATE TABLE `groups` (
    `gid` INT(11) NOT NULL,
    `gname` TINYTEXT NOT NULL,
    PRIMARY KEY (`gid`)
);
CREATE TABLE `entities` (
    `eid` INT(11) NOT NULL,
    `ename` TINYTEXT NOT NULL,
    PRIMARY KEY (`eid`)
);
CREATE TABLE `entity_user_access` (
    `eid` INT(11) NOT NULL,
    `uid` INT(11) NOT NULL,
    PRIMARY KEY (`eid`, `uid`),
    CONSTRAINT `FK_eua_entity` FOREIGN KEY (`eid`) REFERENCES `entities` (`eid`),
    CONSTRAINT `FK_eua_user` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`)
);
CREATE TABLE `entity_group_access` (
    `eid` INT(11) NOT NULL,
    `gid` INT(11) NOT NULL,
    PRIMARY KEY (`eid`, `gid`),
    CONSTRAINT `FK_ega_entity` FOREIGN KEY (`eid`) REFERENCES `entities` (`eid`),
    CONSTRAINT `FK_ega_group` FOREIGN KEY (`gid`) REFERENCES `groups` (`gid`)
);
CREATE TABLE `group_members` (
    `gid` INT(11) NOT NULL,
    `uid` INT(11) NOT NULL,
    PRIMARY KEY (`gid`, `uid`),
    CONSTRAINT `FK_gm_group` FOREIGN KEY (`gid`) REFERENCES `groups` (`gid`),
    CONSTRAINT `FK_gm_user` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`)
);
CREATE TABLE `group_of_groups` (
    `gid` INT(11) NOT NULL,
    `parent_gid` INT(11) NOT NULL,
    PRIMARY KEY (`gid`, `parent_gid`),
    CONSTRAINT `FK_gog_group` FOREIGN KEY (`gid`) REFERENCES `groups` (`gid`),
    CONSTRAINT `FK_gog_parent_group` FOREIGN KEY (`parent_gid`) REFERENCES `groups` (`gid`)
);

-- Data
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
INSERT INTO `users` VALUES
    (101, 'Alice'),
    (102, 'Bob'),
    (103, 'Charlie');
INSERT INTO `groups` VALUES
    (201, 'Administrators'),
    (202, 'Users'),
    (203, 'Operators'),
    (204, 'Guests'),
    (205, 'X-Mas event');
INSERT INTO `entities` VALUES
    (301, 'Foo'),
    (302, 'Bar'),
    (303, 'Qux'),
    (304, 'Snow');
INSERT INTO `entity_user_access` VALUES
    (301, 101),
    (301, 102),
    (302, 103),
    (303, 103);
INSERT INTO `entity_group_access` VALUES
    (301, 201),
    (302, 201),
    (302, 203),
    (303, 201),
    (304, 201),
    (304, 205);
INSERT INTO `group_members` VALUES
    (201, 102),
    (202, 103),
    (203, 102),
    (204, 101);
INSERT INTO `group_of_groups` VALUES
    (201, 203),
    (202, 205),
    (204, 205);
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;

Upvotes: 1

Views: 42

Answers (1)

nbk
nbk

Reputation: 49375

CTE are only a replcament for subqueries.

But your idea how to handle it is a bit off

Your groups table needs to have the same strucure like your first, so you must join the other tables as well to get eid and uid

The next step is to UNION the results so that they are joined verticcally

on that union you run your count query

See example

SET @TEST_EID = 302;
SET @TEST_UID = 103;

SELECT COUNT(*)
FROM
(    SELECT
        DISTINCT
        entities.eid,
        entity_user_access.uid
    FROM
        entity_user_access
        JOIN entities
            ON entity_user_access.eid = entities.eid
UNION
    SELECT
        DISTINCT
        entities.eid,
        users.uid
    FROM
        `groups`
        JOIN group_members
            ON group_members.gid = groups.gid
        JOIN users
            ON users.uid = group_members.uid
         JOIN 
            entity_group_access
            ON groups.gid = entity_group_access.gid
         JOIN entities
            ON entity_group_access.eid = entities.eid) t1
WHERE eid = @TEST_EID and uid = @TEST_UID

| COUNT(*) |
| -------: |
|        1 |

db<>fiddle here

Upvotes: 0

Related Questions