Business Problem
There is a business requirement that the users can view, submit, and update data for a CHEFS form only for the organization and/or organization level they belong to. Currently, this requirement is addressed by creating a separate form for each organization, but with the implementation of new streams of work this approach is not feasible anymore because of the large number of forms that need to be created and maintained.
User Stories
As a Team Manager I want to grant user access to a form for one or more organizations so that the users can perform their work without viewing other organizations' data.
As a Submitter I want to be able to submit data only for the organization and organization level I am assigned to so that the reports reflect the most current data.
As a Reviewer I want to be able to modify data only for the organization and organization level I am assigned to so that information for the reporting is up to date.
As a Viewer I want to be able to view data only the organization and organization level I am assigned to, so I can see what information is available for my organization.
...
Viewer role is not currently available in CHEFS.
Organization - an administrative and functional structure (such as a business department, community, etc.)
The existing access at the form level should also be available for the groups that need full access.
Assumptions
The organization information, including the levels, will be available as fields in the form or part of the user credentials.
The Viewer role will be implemented in CHEFS before the RLS implementation.
Use Cases
Team Manager (Admin)
The user will log into CHEFS and assign permissions (add new user, modify existing user permission, remove user) per:
Form
by Record/Submission (row level) using the organization(s) and organization level they belong to (fields within the form)
Submitter
The user will log into CHEFS, click on the form or form submissions and they will be able to submit data only for the organization (s) they have been granted access to. For example, the user will click on the form, select the organization name that they belong to if they have access to more than an organization, and then enter and submit data.
Reviewer
The user will log into CHEFS, click on the form or form submissions and they will be able to modify data only for the organization(s) they have been granted access to. For example, the user will click on ‘Submissions' and only the submissions they have access to will be available. To view and modify a submission the user will follow the existing workflow in CHEFS.
Viewer
The user will log into CHEFS, click on the form or form submissions and they will be able to view data only for the organization(s) they have been granted access to. For example, the user will click on ‘Submissions' and only the submissions they have access to will be available. To view a submission the user will follow the existing workflow in CHEFS.
If the organization has multiple levels, then the user will be able to view and submit the data at each of the lower levels of the organization.
Form Designer and Owner (Form Developer)
The form developer will enable form permissions by record/submission using fields available in the form.
Solution Approach
Here bellow the high-level system design wireframe of the proposed solution to handle RLS
To accommodate this on DB level we need to add new table form_rls as well as add new column to form table - rls as bool (false as default) to make it easier to determine if we call list of submissions would we need additional filter by user’s field assignment.
Also, as a part of current solution will be implemented Viewer role which is not exist yet in CHEFS currently. This way user with this role would only see the submission list without ability to update or delete them.