Security Filters Expression Problem

Hello Everyone,

Working on security filters,
Having some slight troubles.

IFS(In(useremail(),Admin Emails[Email]), true, true, IN(useremail(), Select(Security Filters[email],[Vendors] = [_THISROW].[ID])))

See formula Above,

First Im checking if the email is one of the admin emails, These have access to everything, otherwise I want to check if the email has access to that specific vendor. Problem is all emails are getting access to vendors.

Not exaccly sure what Im doing wrong.

Hi @Jonathan_S

Could following expression be tried ?

OR(In(useremail(),Admin Emails[Email]), IN(useremail(), Select(Security Filters[email],[Vendors] = [_THISROW].[ID]))

1 Like

Or this one?

IN(
  USEREMAIL(),
  (
    Admin Emails[Email]
    + SELECT(
      Security Filters[email],
      ([_THISROW].[ID] = [Vendors])
    )
  )
)
1 Like

I’m afraid IN() may not work properly in Security filters - I would try CONTAINS()

1 Like

Since when? And why?

That’s actually less secure, since it could produce a false positive on a partial match:

CONTAINS({"bill.jayaram@somecompany.commerce.biz"}, "jayaram@somecompany.com")

matches.

1 Like

I have had some apps going back many years that would not work properly with IN() for me so I just gave up trying and use CONTAINS() accepting the shortcoming.

1 Like

Interesting… :frowning:

Good to know. Thanks for sharing!

It’s possible that IN() works in some cases and in some cases not; I have not had the time to investigate further as to why (or rather when) it doesn’t work. Would be nice to research further.

2 Likes

Does not work. Valid but I think its the In issue. How can someone build proper security filters with this problem.

I would rather not use contains.

If this has been an issue for a while, is the team investigating this? @Steve Is this something you could bring to there attention? Seems kind of strange that a security filter has holes in it. Security filters should be dead solid and should be the teams main focus to make sure it is up to par.

This will allow any email in,

OR(In(useremail(),Admin Emails[Email]), Contains(useremail(), Select(Security Filters[email],[Vendors] = [_THISROW].[ID])))

This works for admins not for emails in my security table
OR(In(useremail(),Admin Emails[Email]), In(useremail(), Select(Security Filters[email],[Vendors] = [_THISROW].[ID])))

Thank you.

Could you please mention the column types [email] and [Vendors] in Security Filters table. Also presume [ID] column is text or number type and in the table for which security filter expression is being used.

Email is a Text Column

Vendors is a reference to the Vendor Table.

Id is Text Type. this is my Key Column.

Thank you.

If the sub expression Select(Security Filters[email],[Vendors] = [_THISROW].[ID]) is returning a list of email IDs from the Security Filters table , then the overall expression, that is In(useremail(), Select(Security Filters[email],[Vendors] = [_THISROW].[ID])) also looks good as per my understanding.

Could you please try the expressions below once?

OR(In(useremail(),Admin Emails[Email]), IN([ID], SELECT(Security Filters[Vendors], [email]=useremail())))

1 Like

Works for Admins, Does not work for emails in security filter table

Please provide a screenshot of the test output for just SELECT(Security Filters[Vendors], [email]=useremail()).

1 Like

Needs to be of list.

Try it in a temporary virtual column.

All Results are blank.