Assuming the following database structure (modifiable if need be) ...
I am looking for a nice way to determine the "effective permissions" for a given user on a given page in a way that allows me to return a row containing the Page and the effective permissions.
I am thinking that the ideal solution may include a function that uses a CTE to perform the recursion needed to evaluate the "effective permissions" for a given page row for the current user.
Background and Implementation details
The above schema represents a start point for a content management system in which users can be granted permissions by being added and removed from roles.
Resources in the system (e.g. pages) are associated with roles to grant the group of users linked to that role the permissions it grants.
The idea is to be able to easily lock down a user by simply having a deny all role and adding the root level page in the tree to that role and then adding the user to that role.
This would allow the permission structure to remain in place when (for example) a contractor working for the company is not available for long periods, this will then also allow for the same granting of their original permissions by simply removing the user from that one role.
Permissions are based on typical ACL type rules that might apply to file system by following these rules.
The CRUD permissions are to be nullable bits so the available values are true, false, not defined where the following is true:
- false + anything = false
- true + not defined = true
- true + true = true
- not defined + not defined = not defined
If any of the permissions is false -> false Else if any is true -> true Else (all not defined) -> false
In other words you get no permissions on anything unless you are granted them through role membership and a deny rule overrides an allow rule.
The "set" of permissions this applies to is all permissions applied to the tree up to and including the current page, in other words: If a false is in any role applied to any page in the tree to this page then the result is false, but if the whole tree up to here is not defined then the current page contains a true rule the result is true here but would be false for the parent.
I'd like to loosely keep the db structure if possible, also keep in mind that my goal here is to be able to do something like:
select * from pages where effective permissions (read = true) and user = ? so any solution should be able to allow me have a queryable set with the effective permissions in them in some way (returning them is optional as long as the criteria can be specified).
Assuming 2 pages exist where 1 is a child of the other and 2 roles exist, one for admin users and 1 for read only users, both are linked to only the root level page I would expect to see something like this as expected output:
Admin user: Id, Parent, Name, Create, Read, Update, Delete 1, null, Root, True , True, True , True 2, 1, Child,True , True, True , True Read only user: Id, Parent, Name, Create, Read, Update, Delete 1, null, Root, False , True, False , False 2, 1, Child,False , True, False , False
Further discussion around this question can be found in the main site chat room starting here.