Security Filter with USEREMAIL() that CONTAIN role names

[anonymous8]

Hi everyone,

I am in need of advice here. My current objective is to restrict access to certain table data based on current users’ role. For now, let’s say I have an ADMIN TABLE which is only accessible by users with an ADMIN ROLE in their row. Then, I have another table called USER TABLE which can be accessed by both USER and ADMIN.

In this case, I created another table called Role List for the purpose of filtering multiple users of varying roles.

Scenario 1: Example. user with a role name of USER will have access to this view/table, but cannot access ADMIN table/view.

Scenario 2: ADMIN has full access regardless.

However, the issue now is that USER role can still access the admin view after I have applied the expression.

Here is the table and my security filter

Based on my understanding, I tried using an alternative expression

AND(

LOOKUP(USEREMAIL(), “Role List”, “Email”, “Email”),

LOOKUP(“User”, “Role List”, “Roles”, “Role”)

)

However, for the first LOOKUP() it displays an error asking for a yes/no expression.

Your help in reviewing my expression and table is greatly appreciated!

[Aurelien]

Hi @Rexatron

Assuming that in your table you have a column [Email], what about this:

OR(
    LOOKUP(USEREMAIL(), "Role List", "Email", "Roles")="Admin",
    AND(
      LOOKUP(USEREMAIL(), "Role List", "Email", "Roles")="User"
      USEREMAIL()=[Email]
    )
  )

Alternatively, you can try:

OR(
    LOOKUP(USEREMAIL(), "Role List", "Email", "Roles")="Admin",
    AND(
      LOOKUP(USEREMAIL(), "Role List", "Email", "Roles")="User"
      anyOtherConditionYouWish
    )
  )

Also, you may want to prefer using Sharing status:

OR(
    USERROLE()="Admin",
    AND(
      USERROLE()="User"
      anyOtherConditionYouWish
    )
  )

For reference:

USERROLE() - AppSheet Help

Current User (Slice) | How to conform your app a… - Google Cloud Community

[anonymous8]

Thank you!