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