I am having trouble on how best to setup my database for privacy roles and also querying data. The app I am trying to build has 3 users levels; Company Admin; Site Admin and a worker. I want the privacy roles to be worker to only see the workers data. The site admin only to see data for the site and the company admin to be able to see all the data for the company.
The simplest way I can think of is to include which company and which site the data belongs to in every table although this doesn’t seem the best approach as I am repeating data but; I have read in other posts that bubble is different from other databases. I am wondering would this have any major impact on performance or is likely to cause any problems a long the way?
With database privacy roles, the first point to keep in mind is that you define them for each type of data in the app, not just on the user table.
Within this structure, a simple approach may be to 1) have flags on the user profile (ie. for company admin and site admin). And 2) on the respective tables (Company and Site, have a field where you save the reference of who the Company and Site Admins are. And 3) for each individual user to have relevant associations with the Company and Sites. (Ex. Company_Affiliated and Active_Sites).
For the three user types, thinking from the perspective of the user table:
Company Admin: this user’s company admin is yes
Site Admin: this user’s site admin is yes and this user’s site’s site admins contains current user
Worker: This user is Current User
Then for a given Site record:
Company Admin: current user’s company admin is yes
Site Admin: current user’s site admin is yes and this site’s admins contains current user
Worker: this site’s users contains current user.
Just some general thoughts. I may not have captured the nuances of your app, but hopefully that points you in the right direction.
The video below will give you a bit more background on database privacy roles.
I have done as you have said and all seems to be okay? Is there any easy way of testing these rules to see exactly what they can see? Or is it best just to send the user to a page with a repeating group listing all the contents of the tables and see what is shown?
You’re thinking on the right path. To test out my rules, I’ll typically do a battery of tests using a sandbox page and log in from the perspective of a series of different users.
On the sandbox page, I’ll set up a handful of tests based on the database role (repeating groups, do a search for, etc.) and see if the roles work as intended.
It’s a good idea to familiarize yourself with the Inspect tool. It’ll provide some context as to what data was actually sent to the page and what was restricted.
