Query for hierarchical RBAC scenario

access-controlctehierarchyrolesqlite

I have a SQLite database with a hierarchical role-based access control (RBAC) data model.

In this model, users are granted permissions to perform certain actions against various resources either directly or by being a member of one or more roles to which the permissions are assigned. Additionally roles can be assigned to other roles such that a hierarchy of roles can be defined.

The schema is roughly like the following:

enter image description here

(from How to design a hierarchical role based access control system – StackOverflow)

With the exception that there is a Role_Role join table instead of a "parent" column on the Roles table, similar to the below:

enter image description here

(from same post as above image)

Also there is no "grant" column on the join tables; existence of a record in a join table means it is granted.

What would a query to get all of the permissions for a given user look like? I'm assuming using a recursive CTE would be involved, but I'm not sure.

Edit: I've got the following recursive CTE working for getting the roles inherited by other roles:

WITH RECURSIVE
  deep_roles(role_id, parent_id, depth) AS (
    SELECT role_id, parent_id, 1 FROM role_roles WHERE role_id = ?
    UNION ALL
    SELECT rr.role_id, rr.parent_id, dr.depth+1
      FROM deep_roles dr, role_roles AS rr
     WHERE rr.role_id=dr.parent_id
       AND dr.depth < 10
  )
SELECT DISTINCT role_id, parent_id FROM deep_roles;

Not sure if this is the only CTE I'll need but it's a start.

Best Answer

Here's what I came up with -- thanks to @CL. for the tips. I made it into a view so I can just query against that for a particular user.

CREATE VIEW user_permissions_deep AS
SELECT DISTINCT user_id, permission_id
FROM (
    SELECT urd.user_id user_id, pr.permission_id permission_id, urd.role_id role_id, urd.parent_id parent_role
    FROM (
        WITH RECURSIVE
          deep_roles(user_id, role_id, parent_id, depth) AS (
            SELECT ru.user_id, rr.role_id, rr.parent_id, 0 depth
            FROM role_roles rr
            INNER JOIN role_users ru ON rr.role_id = ru.role_id
            UNION
            SELECT dr.user_id, rr.role_id, rr.parent_id, dr.depth+1
            FROM deep_roles dr, role_roles rr
            WHERE rr.role_id=dr.parent_id
            AND dr.depth < 10
          )
        SELECT user_id, role_id, parent_id FROM deep_roles
        WHERE depth > 0
        UNION
        SELECT user_id, role_id, null parent_id
        FROM role_users
    ) AS urd
    INNER JOIN permission_roles pr
        ON pr.role_id = urd.role_id
        OR pr.role_id = urd.parent_id
    UNION
    SELECT user_id, permission_id, null role_id, null parent_id
    FROM permission_users
) AS upd
INNER JOIN permission p ON upd.permission_id = p.id;

I found I had to keep the depth check otherwise the loop would never exit if there was a cycle in the role inheritance chain.

If anyone has any suggestions on how this could be improved, please let me know!

Related Question