I shared this idea this morning in a technical call on performance tickets, and I think while the suggestions above are valid (I have minor reservations about #2), we should also consider the following.
One way to flip this on its head is to attempt a concept that came out of a recent collaboration with ODK. In that collaboration it became apparent that we needed a way to translate our simple RBAC, which is mapped onto a complex Supervisory Node hierarchy, into a set of simple Strings which altogether described the User’s rights (aka permissions) in that hierarchy. Such a set of Permission Strings could, I believe, detail all of the different combinations of Rights that a User has on a Program and Facility.
Applied to the question posed here, database paging, there appears to be another use for this set of a User’s Permission Strings:
- In order to achieve database level paging, filtering should be done in the database. More specifically, this means that we need to be able to express our “may User X do Y to Z” criteria directly in the databases’ criteria. This properly places the filtering work where it belongs and where it’s most effecient. RDBMS’s are made to index and filter, and we avoid the high expense of turning database rows into Java objects which we then filter and page on.
- A row in the database, for example a Requisition, would need to express a set of Permission Strings that could be used.
The format of these permission strings should be consistent, and should be useful outside of Requisitions. A format which could work looks like the following:
Where program, facility and right would be UUIDs.
Lets create an example using this supervision structure:
Let’s assume the Program we’re interested in working with is for ARV, and the Facility W. Clinic is the one we want to see Requisitions at.
Requisition’s which were initiated at this facility would have a permission string such as:
The Storeroom Assistant user has permission strings:
The D. Supervisor user has permission strings:
In our database paging of Requisition’s search, we now pass this set of User permission strings, and use something like a SQL IN to match. A simplified query would look something like:
SELECT * FROM requisitions WHERE permissionString IN (“ARV-W.CLINIC-viewRequisition”, “ARV-W.CLINIC-approveRequisition”, etc…)
We could even change the Permission Strings which the Requisition list as valid, as needed for filtering. e.g. when the requisition is authorized, we’d remove the string ARV-W.CLINIC-authorizeRequisition from the Requisition, and replace it with ARV-W.CLINIC-approveRequisition. This specific SQL may not work as well within Requisition Service, as the Requisition might want to express multiple permission strings: e.g. view and approve. An approach to this should be quite easy to tackle, even if it meant using another table to store the list of permissions for the requisition.
Other Role Assignments
Moving away from this example and looking at some of our design docs around RBAC, this format would work for the Supervisory Role Assignments (program-facility-right) used above as well as other types of assignment’s by dropping pieces off of the front of the string:
- For a Fulfillment Role Assignment (aka Facility Role Assignment) we’d drop the program from the string to create: facility-right
- For an Administrative/Report Role Assignment (aka Direct Role Assignment) we’d further drop the facility in addition to the program, and simply create: right
Of course there might be further optimization here that could be made.
- These permission strings are actually UUIDs concatenated by hyphens - not truly strings we’d apply Regex to.
- It also might make more sense to reverse the format, so that it’s: right-facility-program, and we’d drop pieces off the back of the string rather than the front for different types of Role Assignments.
- Additionally we’d like to cache a User’s permission Strings for a period of time in consuming Services - any change in supervision structure, role definition, facility go-down date, etc would change this list, and we’d need to invalidate it in any caching strategy.
Before we jump into all of that, I’d like to discuss the overall concept applied to database paging as well as to take a temperature check of the usefulness of this in other Services.
- The rights a user has, mapped by any current type of Role Assignment, could be expressed by the Reference Data Service as a set of Permission Strings.
- An entity, such as a Requisition, could be searched for using the databases filtering capability more efficiently if we could compare the Permission Strings required for the entity to the Permission Strings a User has.
- Permission Strings could be further optimized, however we’d like to first validate the concept.