I’m creating an app where business customers can sign up to manage data around their processes and improvement opportunities. I’m trying to figure out a way to make sure that businesses that sign up can only see their data. However there are some nuances that make it a little more complex, so I’ll break down the data structure here to hopefully make it a little clearer.
User has one company.
A company can have many users.
A company can have none or one consultant.
A consultant can have many companies.
Permissions:
Users within a company should only be able to see their own data and no other companies, unless they are an admin or consultant.
Consultants should be able to see all of their company, as well as any other company that has them listed as a consultant.
Admins can see globally.
“Users within a company should only be able to see their own data and no other companies, unless they are an admin or consultant.”
Set the users “role” to their company in the user table and have a row-owner column with the Company name in it. Now they will only see rows that match the Company.
“Consultants should be able to see all of their company, as well as any other company that has them listed as a consultant.”
This runs up against the 9 roles rules. Your consultants can only have 9 roles/Company so if one is their own company - they can see 8 other companies as consultants. Otherwise you will have to implement your own hierarchy and write that information to every record/row/table in your App.
In both cases, every time you create a row that is to be restricted you need to ‘write’ the row owner into that column. You can also have multiple row owner columns in a row which operate as an ‘OR’ (if any column designated a row-owner matches ANY of the roles listed in the google USER sheet then the user has access to the data).
“Admins can see globally.”
You probably need multiple row-owner columns since one column will control ADMIN access and another will control COMPANY access. For administrators, one “Role” slot will be ADMIN and when you create a ROW you will need both an Access and a Company ‘row-owner’/column. Every time you create an object you have to write ADMIN into the ‘access’ column. This way admins will have access to that data no matter what/how the Company column is set. Non-admin users will not have access via the ADMIN role (unless a company is also named ADMIN and then all bets are off) and will be restricted to access via the Company column matching their role/Company in the google sheet.
Row owners / roles is a surprisingly nuanced feature and requires a google sheet based on your user case. Search @Jeff_Hager / @Darren_Murphy with the tag ‘row-owners’ to get LOTS of examples/conversations on usage.
To simplify things, is it possible to just administrate from the backend for my team? Are these constraints only here if I wanted to build an administration frontend?
And if that’s the case, consultants won’t be restricted to 9 companies?
roles/row owners physically control access to the data (the underlying rows).
Does a consultant have 8 active clients/Companies? You can add/subtract active Clients/Companies from the google sheets ‘role’ columns. The restriction is really 9 concurrent accesses - you can have 9 concurrent roles (ADMIN + 8 companies).
Consultants can work at/on any company - just only 8 at a time in this example.
So yes, you can administrate access on the backend.The good news if when a gig is over you subtract the Company name from the role column and you have now restricted access which might be what the client expects.
The trouble is a consultant can handle more than 8 clients - so I’m going to have to find a different way to do the hierarchy. Is there way to do a multiple relation and have the app look at one of the records to determine access?
I’m also not sure why there is a Google Sheet requirement here. How does this differ from using Glide Tables?
I have a clarifying question: are you wanting to use Roles as Row Owners to prevent data from being downloaded to users devices, or are you just wanting “pseudo-roles” to use as editing and visibility controls?
The Google Sheets requirement comes into play any time that you need to assign more than one role to any single user. It’s not possible to do this with native Glide Tables.
I think it’s worth noting that if a consultant is a single person, they don’t necessarily need to be assigned a role or multiple roles for that matter. Instead you would just apply their email address in a row owner column for the relevant company data. There is no limit on the number of row owner columns you can have in a table.
Roles mostly make sense in cases where you would have several users with the same role, and instead of creating a row owner column for each user, you instead create a single row owner column with the role that applies to multiple users.
In your case, you mentioned that a company will have no more than one consultant. If that consultant is just one single user, then I think Roles may be overkill.
That’s not to say that you wouldn’t still use Role functionality for things such as a group of Admin users.
Roles and Row Owners DEMYSTIFIED - Community Resources - Glide Community (glideapps.com) thanks @Robert_Petitto ))