I have been struggling with Quickbase's permissions structure for a while now and would appreciate a little guidance on best practice for the use of groups and roles.
We have a number of apps but I'll focus in on two of the apps we have which are essentially a Project Management app and an Asset Management app.
Project Management app
This app is essentially focused around a "Projects" table with a unique Project ID. This table acts as Master to a number of different tables, for example
- "End Customer"
- "Floor Plans"
- "Project Rooms"
- "Room Layouts"
- "Survey Photos"
- "Project Contacts"
- "Project Notes"
I need to be able to allow different types users to see just enough information but not have them see too much. I also need users to see only the Projects they need to see or, in the case of End Customer viewers, see all projects associated with that End Customer.
I have currently achieved this by creating three distinct types of roles which could be described as follows:
- Functional Roles (roles which define how much of the app the user can see) allow access to tables and fields but allow no ability to View or Modify records.
- examples of Functional Roles might include: Customer Viewer, Internal Project Manager, Third-party Viewer, etc
- each Functional Role has a corresponding Group to which users are added.
- Project-level Roles (roles which define which projects a user can see) allow a user to see particular records using a Custom Rule which uses the Project ID (which is pulled through to each Details table from the Projects table) as the filter.
- A Custom Rule might say "May view Projects where Project ID is equal to 123456" or "May view Actions where Project-Project ID is equal to 123546"
- A user might need to view many projects or just one
- Each Project-level Role has a corresponding Group to which users are added. This group is then added to the App and assigned the Project-level Role.
- One Project-level Role is required for each Project
- example names might be Project 123456, Project 654321
- Customer-level Roles (roles which define which customers a user can see) allow a user to see all Projects associated with that customer. This is important for End Customer stakeholders who may want to see the status of all their projects. This is achieved by using a Custom Rule which uses the Project End Customer (which is pulled through to each Details table from the Projects table) as the filter.
- A Custom Rule might say "May view Projects where End Customer is equal to ACME Inc" or "May view Actions where Project-End Customer is equal to ACME Inc"
- Each Customer-level Role has a corresponding Group to which users are added. This group is then added to the App and assigned the specific Customer-level Role
- One Customer-level Role is required for each customer
- An "All customers" role allows access to all Projects (for internal people who need to see everything).
Access to the app is achieved in one of two ways.
- A user is added to a "Functional Role" and a "Project-level Role" (or number of "Project-level Roles"). The combination of these two gives the user the correct level of access and also access to the right projects.
- A user is added to a "Functional Role" and a "Customer-level Role" which gives the correct level of access and also access to all Projects for that customer.
This works OK BUT it means there might be:
- Five Functional Roles
- One Project Role for each Project (over 50 Roles)
- And each one has to be edited with the right Project ID within each Custom Rule (which takes about 5 minutes per role)
- One Customer Role for each End Customer (over 20 Roles)
- And each one has to be edited with the right End Customer within each Custom Rule (which takes about 5 minutes)
- A group for every role (over 75 groups!).
Asset Management app
Essentially, permissions for the Asset Management app are the same except "Project-level Roles" are not required.
As you can imagine, this is a bit of an Admin nightmare. It's just about do-able, but I keep thinking that I am perhaps not approaching this in the right way.
I have seen people say that you shouldn't really have more than a few roles but I can't think of how I could do the Project-level and Customer-level permissions without these additional roles.
To support the way I am doing this I would really like to have something like "Role Variables" where a Role has a variable into which I can input the Project ID (or customer) and then simply allocate that variable to the Custom Rules. This would speed up the creation of the individual roles, allowing copying of an existing role and quick replacement of the Project ID or Customer.
But I'm probably doing this entirely the wrong way.