Security Filters for Company, vendors and users of each with Roles

[ZOCO_Unlimited]

Hello, I’m looking for assistance w/ security f

Scenario: I have multiple companies that each have multiple vendors both with multiple users each having a Role within their respective organization.

Goal: I need the Company users with the roles of Admin or Dispatch to be able to see everything and the Vendors see and manage their users and jobs assigned to their companies.

Companies will assign Vendors Jobs. The Admin (Dispatch) of the Vendor account will assign a user (driver) a job. The Driver will be able to update the job status and notes only.

Tables:
Companies, Users, Jobs, Groups

Companies Columns:

CompanyID	Company	Company_Owner	Company_Main_Contact	Company_Owner_Email

Job Columns:

Job ID	Date	Name	Route	Production_Location	Lat Long	Company Assigned To	User Assigned To	Job Type	Status	Notes

User Columns:

User Email	Last Name	First Name	Phone	Company	Role	Group ID

I’ve found multiple ways of implementing different parts of this, but am circling as a whole..

Filter by User’s Company: IN([Company Assigned To], SELECT(Users[Company], [User Email] = USEREMAIL()))

Filter by Role: IN(lookup(useremail(), Users, User Email, Role), list(Admin, Dispatch)))

Security Filter in Jobs: if(in(lookup(useremail(), Users, User Email, Role), list(Admin, Dispatch)),
“ALL_CHANGES”,
“UPDATES_ONLY”
)

if(
AND([Company]=LOOKUP(USEREMAIL(),Users,user email, company),
in(lookup(useremail(), Users, User Email, Role), list(Admin, Dispatch))), “ALL_CHANGES”,
“UPDATES_ONLY”)

If I limit the user’s table for the companies to see their own, that works but in the jobs table the rows show the broken links to the jobs that they shouldn’t see that belong to other vendors.

I am especially struggling at the customer level for the customer to see everything if they have the role of admin or dispatch as well as manage vendor accounts. I feel like I need to break tables out to companies and their users and vendors and their users but I see so many formulas that make it seem possible with less tables.

I’ve also started these tables but have not used them yet as I don’t know how far I need to break down the tables to achieve my results:
Vendors
Managers
Routes
Locations

Any assistance is greatly appreciated!

if(
AND([Company]=LOOKUP(USEREMAIL(),Users,user email, company),
in(lookup(useremail(), Users, User Email, Role), list(Admin, Dispatch))), “ALL_CHANGES”,
“UPDATES_ONLY”)

(reposted, was in wrong category)

[WillowMobileSys]

The easiest way to handle Security Filters in a multi-tenant app, is to identify the tenant table…in your case that seems to be the Companies table…and filter off of that tenant table for all other associated tenant data. This means clearly assigning the tenant/Company on all associated data tables - which it seems like you have already done.

The idea then is this…

The Companies table would be filtered by the logged in user with a filter like:

[Company ID] = Current User[Company]   

Where CurrentUser is a Slice derived from the logged in user but it could just be a LOOKUP() or SELECT() instead of a Slice. However, I think you’ll find that you need access to the logged in user info all over the app so a Slice IS the way to go.

Then with all other data tables that need filtered including a Company column, their filter expressions would be something like:

[Company] = ANY(Companies[Company ID]) 

This assumes there will always only be a single Company the user will be viewing.

If a user can, for some reason, view multiple companies, then you would adjust these expression to use IN() functions instead.

This approach will greatly simplify the Security filtering and make it more efficient. Hopefully it will also eliminate the struggles you are having with the complex expressions.

I hope this helps!

[ZOCO_Unlimited]

Thank you for your response, I started with the current_user slice, this is the error that I received:

Tested on the Jobs table as well:

Current user slice for reference if needed:

Users: Company Column