2

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.

  • 1
    Yes, a recursive CTE would be involved. First, write a query to get the permissions for a given user and his roles, assuming that there is no role recusion. Use a subquery to get the user's roles. Then write a query to get the roles contained in a role. Then combine those. – CL. May 21 '15 at 8:53
  • Thanks for the response -- could you provide an example? – blah238 May 21 '15 at 16:15
  • Asking me to do all your work for you does not motivate me. At least write those queries that you know how to write. – CL. May 21 '15 at 16:18
  • Sorry my SQL-fu is pretty weak. I've added a recursive CTE to the question that gets all the roles inherited by a given role. Do you think this is the only recursive CTE I'll need? Would the rest of the queries just be joins? – blah238 May 21 '15 at 19:32
  • 1
    When you replace UNION ALL with UNION, you don't need the depth check. Yes. Yes. – CL. May 21 '15 at 20:06
1

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.