What are the best ways to allocate permissions to users and groups for Project Management and Asset Management apps?

  • 0
  • 2
  • Question
  • Updated 3 years ago
  • Answered

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

  • "Projects"
    • "End Customer"
    • "Actions"
    • "Floors"
      • "Floor Plans"
    • "Project Rooms"
      • "Room Layouts"
      • "Survey Photos"
    • "Meetings"
    • "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.

  1. 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.
  2. 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.

In Summary

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.

Any advice?

Photo of David

David

  • 20 Points

Posted 3 years ago

  • 0
  • 2
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
Re: But I'm probably doing this entirely the wrong way.

I will post back soon.  There is a better way.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
OK, let's knock off the End Customers with one Role.

Make a table called End Customer Users where the userid is the Key field.  You Probably already have a table of Customers.  Make a relationship where 1 Customer has Many End Customer Users. Lookup the Customer Down to the End Customer Users Table.

Populate at least 1 End Customer User with their connection to the Customer so that you will be able to test.

On the Projects table Make a field called [Current User] with the formula of User() 


Make a Relationship where 1 End User has many Projects based on that User field.

Look up the value of the Customer from the End User Customer table.  Call it [Customer Name of Current User]

Lastly, make a Role called Customers

The Custom Rule will be where Customer name equals [Customer Name of Current User].



An alternative, which I typically do is to make a checkbox formula called say [Allowed to see this Customer] with a formula of

[Customer Name] = [Customer Name of Current User]

or if you want to be more wordy

IF([Customer Name] = [Customer Name of Current User],true, false)

Then  the Custom Rule Permission can be where [Allowed to see this Customer] is checked.

If you do that field at a high level like Customer or Project, then you can lookup that [Allowed to see this Customer] field down to child tables and then those child tables like Actions, Floors etc can also be driven off a Custom Rule of [Allowed to see this Customer] is checked, as well.

As for the staff who float between projects, one simple way is to have a list user field on the project called [User List of Staff working on this project]


Then again, have a checkbox field like  [Staff Allowed to see this Project]

Includes([User List of Staff working on this project], ToUserList([Current User]))

So, you just add or remove staff from particular Projects as they need access, and also flow down that checkbox field down to child tables for Custom Rule permissions there.


So now we only need two Roles for the Users who have limitaitons.
1. Customers2. Internal Staff Limited to Specific Projects
Photo of David

David

  • 20 Points
Thanks for the quick response. I'll give it a try. This may also mean that I can delegate the management of access to project owners rather than having to manage this with an admin function.
Photo of David

David

  • 20 Points
Thanks, Mark!

I've tested the Project-level methodology you suggested and this now works. I struggled initially to get access until I realised that I needed to add all the users of the app to the Project-level role. I achieved this by creating a Master group that contains all of the other groups associated with this app. Do you think I'm right doing this with groups? I was trying to avoid having too many user->role assignments per app as this was getting out of hand. Is there another way to assign "all users of this app" to the "Project-level" role (the Project level role has no access to any tables/fields, etc so I suppose I could use "Eveyone on the internet")?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
You should NOT use everyone on the Internet.

Normally, you would have a groups like All Employees,  and All Project Managers, and perhaps in your case, All Customers.

Typically if there is a Project Manager Role, then that Rolle would define the access to anything that a project manager should be allowed to see and do.  A Project Manager should not need to be in any other additional Roles.
Photo of David

David

  • 20 Points
Thanks Mark, this is working great and my power users of this app are happy to add users to each project as a part of the project creation. This is working great! I'm now adding the same functionality to a bunch of other apps which require restriction in this way. So much easier!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
Great, thx for letting me know.

When I see clients with a dozen or more rules, then in fact it's always a maintenance nightmare and they have low security and not high security, because you lose track of what each Role does.