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:
(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:
(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.
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!