Security Filter by email and location

[anonymous]

I’m trying to create a filter based on app users login email which will then permit then certain rights.

Each users email is linked to a user level and a respective site location (on a google sheet, attached).

The site locations are NJ, D1, ZN, OL, NU and ALL.

The user levels are Super-Admin, Admin, and User

I’m essentially trying to create a security filter that will permit the following:

User level “User” are only permitted to ADDS_ONLY. The user is also limited to only seeing data from their respective site.

User level “Admin” are permitted to ALL_CHANGES. The user is also limited to only seeing data from their respective site.

User level “Super-Admin” are permitted to ALL_CHANGES. The user is able to see all data from all sites (this user level is given the site location as ALL)

Is it possible to create a security filter? And what will the impact on performance be?

Thanks

[dbaum]

Have you tried something along the lines of the following? Did you encounter issues that I’m overlooking in your description?

Security filter property for Users table from your screenshot:

[Email] = USEREMAIL()

Are updates allowed? property for any table where you want to control edit permission by user role

SWITCH(INDEX(Users[User_Level], 1), "Super-Admin", "ALL_CHANGES", "Admin", "ALL_CHANGES", "ADDS_ONLY")

Security filter property for any table where you want to control data access by user location:

IF(INDEX(Users[User_Level], 1) = "Super-Admin", true, [Farm] = INDEX(Users[Farm], 1))

See additional helpful techniques in How to conform your app around WHO is using the ap… - Google Cloud Community .

[anonymous]

Thanks @dbaum for this. I’ve bene playing around with the Are updates allowed? property however I keep getting back the following message

I’ve also encountered this with the other filters I’ve put based on what you sent. Anyway around this (issue also arise when i specifically pick the email column for the respective table)?
Thanks.

[dbaum]

The following is the wrong syntax:

[Email][User_Level]

That syntax is for dereferencing a list, – not a single value. Your [Email] column presumably does not hold list values, and if it did that wouldn’t make sense in the context of your expression.

For the INDEX portion of your expression, you probably need something more along the lines of the following:

INDEX(
  SELECT(Users[User_Level], [Email] = USEREMAIL()), 
  1
)