Security filter for using 4 different roles, each one with a higher security than the other

[AaronTerry]

Hey there, I’m attempting to build a rental management app that’s not only for Admin and Managers but for Clients and Tenants as well.

• Admins should see everything.
• Managers should see rentals, clients and tenants.
• Clients should see their rentals and tenants.

I grabbed this formula from a timecard demo and it worked but I don’t think it’s the final solution.

IFS(
      ANY(CURRENT_USER[ROLE]) = 'ADMIN',
      TRUE,
      ANY(CURRENT_USER[ROLE]) = 'MANAGER',
      OR(
           [USER] = USEREMAIL(),
           AND(
                   [USER].[REGION] = ANY(CURRENT_USER[REGION]),
                   [USER].[ROLE] = 'CLIENT'
                   AND(
                           [USER].[REGION] = ANY(CURRENT_USER[REGION]),
                           [USER].[ROLE] = 'TENANT'
                            )
                     )
              ),
TRUE,
[USER] = USEREMAIL()
)

In a perfect scenario, I wouldn’t need the User, Role or Region in EVERY data table because the system would verify the UserEmail matches the address attached to the UserEmail of the Users Data table. I can’t for the life of me figure out how to write the formula to reference the ‘Users’ Data Table even after creating a Current_User slice sourced from that data. Any help and direction is appreciated!

[Steve]

Please post a screenshot of the columns (from Data >> Columns in the app editor) for the table you’re building the security filter for,

Please also post a screenshot of the columns for the Users table,

[AaronTerry]

Is this what you’re looking for? I’d like to apply a filter to my Users data table so only i and maybe managers can update it while in the field. I’d like to show rental information to only those whose Current_User matches the User’s address in the Users data table.

Right now I have User, Role and Region in the Rentals table and I can already see how that forces me to create multiple inputs of the same property address each with the appropriate labels but I’d rather not create multiples if I don’t have to. Thanks, Steve for your help!

[Steve · Accepted solution]

From what I’ve been able to glean from your screenshots (thank you!), you might try this as the security filter for the Rentals table:

OR(
  IN("Admin", Current_User[Role]),
  AND(
    IN("Manager", Current_User[Role]),
    IN([Region], Current_User[Region])
  ),
  AND(
    IN("Client", Current_User[Role]),
    IN([Client Email], Current_User[User])
  ),
  AND(
    IN("Tenant", Current_User[Role]),
    IN([Tenant Email], Current_User[User])
  )
)

An app user with the Admin role sees all Rentals.

A user with the Manager role sees Rentals that have the user’s same Region.

A user with the Client role (I’m guessing these are property owners?) sees Rentals that have the user’s email address as the rental’s Client Email.

A user with the Tenant role sees Rentals that have the user’s email address as the rental’s Tenant Email.

[AaronTerry]

Leave it to Steve! haha

Thank you so much, I can rest easy tonight.

[AaronTerry]

Here’s the Users data table I’m trying to reference. I have a Slice created called Current_User with the formual USEREMAIL() = [User] as per your demo videos and its referencing the Users data table to pull info.