Security Filter with USEREMAIL() that CONTAIN role names

[Rexatron]

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 · Accepted solution]

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

[Rexatron]

Thank you! 😁