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.
Best Answer
Using this model, I have come up with a way to query the Pages table in the following manner:
The GetPermissionStatus inline table-valued function's result can be either an empty set or one single-column row. When the result set is empty, that means that there are no non-NULL entries for the specified page/user/permission combination. The corresponding Pages row is automatically filtered out.
If the function does return a row, then its only column (IsAllowed) will contain either 1 (meaning true) or 0 (meaning false). The WHERE filter additionally checks that the value must be 1 for the row to be included in the output.
What the function does:
walks the Pages table up the hierarchy to collect the specified page and all its parents into one row set;
builds another row set containing all the roles the specified user is included in, along with one of the permission columns (but only non-NULL values) – specifically the one corresponding to the permission specified as the third argument;
finally, joins the first and second set via the RolePages table to find the complete set of explicit permissions matching either the specified page or any of its parents.
The resulting row set is sorted in the ascending order of permission values and the topmost value is returned as the result of the function. Since nulls are filtered out at an earlier stage, the list can contain just 0s and 1s. Thus, if there is at least one "deny" (0) in the list of permissions, that will be the result of the function. Otherwise the topmost result will be 1, unless the roles corresponding to the selected pages happen to have no explicit "allows" either or there are just no matching entries for the specified page and user at all, in which case the result will be an empty row set.
This is the function:
Test case
DDL:
Data inserts:
So, just one user is used but it is assigned to two roles, with various combinations of permission values between the two roles to test the blending logic on child objects.
The page hierarchy is very simple: one parent, two children. The parent is associated with one role, one of the children with the other role.
Test script:
Cleanup:
Results
for Create:
There was an explicit true for
Page 1.1
only. The page was returned according to the "true + not defined" logic. The others were "not defined" and "not defined + not defined" – hence excluded.for Read:
An explicit true was found in the settings for
Page 1
and forPage 1.1
. Thus, for the former it was just a single "true" while for the latter "true + true". There were no explicit read permissions forPage 1.2
, so it was another "true + not defined" case. So, all three pages were returned.for Update:
From the settings, an explicit true was returned for
Page 1
and a false forPage 1.1
. For the pages that made it into the output the logic was the same as in case of Read. For the excluded row both false and true were found and so the "false + anything" logic worked.for Delete there were no rows returned. The parent and one of the children had explicit nulls in the settings and the other child did not have anything.
Get all permissions
Now if you want just to return all effective permissions, you can adapt the GetPermissionStatus function:
The function returns four columns – the effective permissions for the specified page and user. Usage example:
Output: