I have the 4 following tables in MySQL 8.0:

-- auto-generated definition
CREATE TABLE user
(
    id              VARCHAR(255) NOT NULL
        PRIMARY KEY,
    name            VARCHAR(255) NULL,
    email           VARCHAR(255) NOT NULL,
    CONSTRAINT user_email_unique
        UNIQUE (email)
)
    CHARSET = utf8mb4;

-- auto-generated definition
CREATE TABLE role_inheritance
(
    role_name  VARCHAR(255) NOT NULL,
    child_name VARCHAR(255) NOT NULL,
    PRIMARY KEY (role_name, child_name),
    CONSTRAINT role_inheritance_child_name_foreign
        FOREIGN KEY (child_name) REFERENCES role (name)
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT role_inheritance_role_name_foreign
        FOREIGN KEY (role_name) REFERENCES role (name)
            ON UPDATE CASCADE ON DELETE CASCADE
)
    CHARSET = utf8mb4;

CREATE INDEX role_inheritance_child_name_index
    ON role_inheritance (child_name);

CREATE INDEX role_inheritance_role_name_index
    ON role_inheritance (role_name);

-- auto-generated definition
CREATE TABLE role_assignment
(
    user_id   VARCHAR(255) NOT NULL,
    role_name VARCHAR(255) NOT NULL,
    scope     VARCHAR(255) NULL,
    PRIMARY KEY (user_id, role_name),
    CONSTRAINT role_assignment_role_name_foreign
        FOREIGN KEY (role_name) REFERENCES role (name)
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT role_assignment_user_id_foreign
        FOREIGN KEY (user_id) REFERENCES user (id)
            ON UPDATE CASCADE ON DELETE CASCADE
)
    CHARSET = utf8mb4;

CREATE INDEX role_assignment_role_name_index
    ON role_assignment (role_name);

CREATE INDEX role_assignment_user_id_index
    ON role_assignment (user_id);


-- auto-generated definition
CREATE TABLE role
(
    name VARCHAR(255) NOT NULL
        PRIMARY KEY
)
    CHARSET = utf8mb4;

What I am attempting to do is to allow me to find all of the inherited roles given an entry in role_assignment for a given user. The idea would be that if you assign a role a child, then any children of the assigned child would also become a child of the role. I currently have a way to find all children of a role via:

WITH RECURSIVE
    inheritance AS (SELECT child_name
                    FROM role_inheritance
                    WHERE role_name = 'admin'
                    UNION ALL
                    SELECT ri.child_name
                    FROM inheritance i,
                         role_inheritance ri
                    WHERE ri.role_name = i.child_name)
SELECT *
FROM role r
WHERE r.name IN (SELECT child_name
                 FROM inheritance)

However, I want to set it up that I can do a full join between the user, role_assignment, and role_inheritance tables to show that a user does indeed have a certain child role if they are assigned a role in role_assignment.

For example, take the following tables (subbing ids for names for easier clarification):

Roles

_{note: these were randomly generated for testing purposes}

Role Inheritance

Role Assignment

I want to achieve a result output that looks like this:

I attempted to use the following SQL, but it uses a method that will be deprecated in MySQL 9, and also does not remove "admin" and "functionalities" from Chris:

SELECT roles.name AS role_name, @id := u.id AS user_id
FROM user u
         CROSS JOIN (WITH RECURSIVE
                         user_roles  AS (SELECT role_name
                                         FROM role_assignment where user_id = @id),
                         inheritance AS (SELECT child_name
                                         FROM role_inheritance
                                         WHERE role_name IN
                                               (SELECT role_name
                                                FROM user_roles)
                                         UNION ALL
                                         SELECT ri.child_name
                                         FROM inheritance i,
                                              role_inheritance ri
                                         WHERE ri.role_name = i.child_name)
                     SELECT *
                     FROM role r
                     WHERE r.name IN (SELECT child_name
                                      FROM inheritance)
                            OR r.name IN (SELECT role_name
                                          FROM user_roles)) AS roles
ORDER BY user_id

How can I achieve this?

DBFiddle link

CodePudding user response：

If I follow you correctly, you can use the following logic:

with recursive cte as (
    select user_id, role_name from role_assignment
    union all
    select c.user_id, ri.child_name
    from cte c
    inner join role_inheritance ri on ri.role_name = c.role_name
)
select * from cte order by user_id, role_name

This scans all users declared in role_assignment, and then recursively follows the parent/child relationship of roles. As a result, you get a list of all users, with all the role they are assigned. I don't see the need to bring the user_roles table as in your query.

In this demo on DB Fiddle with your sample data, this yields: