Security FIlter Issue

[David2]

Hi,

I've got three tables; User, Roles and Notes

The Users and Notes table has an ENUMLIST that references the Roles table.

A User, or a Note can be assigned one or more Roles.

I have this security filter on the Notes table

OR(
useremail()=[Created By Email],
IN([Available To],lookup(useremail(),"Users","User Email","User Role"))
)

Which allows a user to view their own notes, and also allows a user with a Role of Admin to view all Notes that have ONLY Admin in the Available To field (ENUMLIST REF)

But, If the Note had an Available To Role of Admin ,  Sales, for example, a User that had a Role of Admin and different email address, can't view the note.

My issue is this

IN([Available To],lookup(useremail(),"Users","User Email","User Role"))

I need to, somehow, require it to check the list of Roles on the notes Available To field, to see if the Role exists in the list.

I've tried a few options, but this one line of code is the very closest I can get this to the end result.

Can anyone assist me any further here?

[AleksiAlkio · Accepted solution]

Well, the difference is very small. I normally prefer COUNT as then you don't need to play with blank values.

[MultiTech · Accepted solution]

The current user system is a fundamental skill to learn in AppSheet, allowing for all sorts of advanced functionality in your system.

Using the Current_User slice in your security filters, however, introduces dependencies for computation; the Transactions table can't process until the User table is finished, which then populates the current user slice, which then can be used for the Transaction table's security filter.  It has to wait for the other things to finish before it can even start it's run.

So depending on the scenario, this might not be advisable.  But if your data system isn't intended to be very big (under 10k records in your biggest table) then this would be okay; once you start getting into larger data sets the dependencies for computation start to really add up, and your app starts to take longer and longer to load.

The current user system is an essential tool in your AppSheet toolbelt; I find it preferable to use this instead of brute force (ie. LOOKUP(), SELECT(), FILTER(), etc.) if I can.

[AleksiAlkio]

You have the IN() in a wrong order. You need to use IN(LOOKUP(..),[Available To])

[David2]

Thanks for your reply, I have corrected this, and added a split function in as well, so I now have;

OR(
useremail()=[Created By Email],
IN(split(lookup(useremail(),"Users","User Email","User Role"),","),[Available To])
)

My problem now seems to be a little reversed, a Note can Now have an Available To (ENUMLIST REF) of Admin , Sales for example - if the User has a User Role (ENUMLIST REF) of Admin, they can view this note, if I change the User Role to Admin , Sales, or Admin , HR, this Note disappears.

I'm rather confused, I feel this could be something simple, but I can't work it out.

The expression output even looks good.

ANY of these statements is true: 
....1: (USEREMAIL( 
....)) is equal to (The value of column 'Created By Email') 
....2: (SPLIT( 
........One randomly chosen value from this list ( 
................The list of values of column 'User Role' 
................from rows of table 'Users' 
................where this condition is true: ((The value of column 'User Email') is equal to 
(USEREMAIL( 
................)))) 
........",")) is one of the values in the list (The value of column 'Available To')

Any further help would be greatly appreciated.

Thanks.

[AleksiAlkio]

What is the column type with your "User role"?

[David2]

It is an EnumList, allowing a user to be part of one or more Roles

[AleksiAlkio]

So.. now it gets more complicated as your are trying to evaluate list against list. The IN() doesn't do that. It checks if a value is included in the list.

[AleksiAlkio]

Something like COUNT(INTERSECT(EnumList1,EnumList2))>0

[David2]

Thank you so much @AleksiAlkio !

That worked, I also found another working code, both below

OR(
useremail()=[Created By Email],
COUNT(INTERSECT([Available To], split(lookup(useremail(),"Users","User Email","User Role"), " , ")))>0
)

OR(
useremail()=[Created By Email],
IsNotBlank(Intersect([Available To], split(lookup(useremail(),"Users","User Email","User Role"), " , ")))
)

Would there be a preference for either code?

Credit for the second code goes to @MultiTech 

[AleksiAlkio · Accepted solution]

Well, the difference is very small. I normally prefer COUNT as then you don't need to play with blank values.

[David2]

Thank you so much, really appreciate your help on this one.

[MultiTech]

Perhaps a bit more efficient would be to create a current user slice to hold the details of the current user's record, instead of the LOOKUP().

• https://www.googlecloudcommunity.com/gc/Tips-Tricks/Current-User-Slice-How-to-conform-your-app-aroun...

OR(
  useremail()=[Created By Email],
  IsNotBlank(
    Intersect(
      [Available To], 
      split(index(Current_User[User Role], 1), " , ")
    )
  )
)

Cheers to @AleksiAlkio  for the knowledge gem about blanks inside a list not being counted. 🧠👍

[David2]

Thanks for this option @MultiTech. After seeing your post, and just as I thought I had this all sorted, I'm now questioning my knowledge a little further 😅

Is using a user slice, the preferred method for using security filters in an app, instead of using a generic user list and comparing the current user to an email in a list of users?

Do you find any pro's or con's to either?

On the other hand, I made a copy of my app and also had a play around with creating a Current User Slice and tried your code, it works fine, I've also been successful in modifying it and adapting it in other parts of the app.

Out of this, I've created two security filter sample apps, one with a user list, one with a current user slice. I've learnt a lot in the process, so thank you both for your help and support.

[MultiTech · Accepted solution]

The current user system is a fundamental skill to learn in AppSheet, allowing for all sorts of advanced functionality in your system.

Using the Current_User slice in your security filters, however, introduces dependencies for computation; the Transactions table can't process until the User table is finished, which then populates the current user slice, which then can be used for the Transaction table's security filter.  It has to wait for the other things to finish before it can even start it's run.

So depending on the scenario, this might not be advisable.  But if your data system isn't intended to be very big (under 10k records in your biggest table) then this would be okay; once you start getting into larger data sets the dependencies for computation start to really add up, and your app starts to take longer and longer to load.

The current user system is an essential tool in your AppSheet toolbelt; I find it preferable to use this instead of brute force (ie. LOOKUP(), SELECT(), FILTER(), etc.) if I can.

[AleksiAlkio · Accepted solution]

The simpler the better 🙂

[David2]

Thank you, @AleksiAlkio and @MultiTech for your input on this, security filters have been a long time struggle for me, I've finally got a number of security filters implemented into my apps, couldn't have done it without you both.