Security Filters Expression Problem

[Jonathan_S]

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.

[Suvrutt_Gurjar]

Hi @Jonathan_S

Could following expression be tried ?

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

[Steve]

Or this one?

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

[Jonathan_S]

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

[Bellave_Jayaram]

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

[Steve]

Bellave_Jayaram:

I’m afraid IN() may not work properly in Security filters

Since when? And why?

Bellave_Jayaram:

I would try CONTAINS()

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.

[Bellave_Jayaram]

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.

[Steve]

Interesting…

Good to know. Thanks for sharing!

[Bellave_Jayaram]

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.

[Jonathan_S]

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.

[Jonathan_S]

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])))

[Suvrutt_Gurjar]

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.

[Jonathan_S]

Email is a Text Column

Vendors is a reference to the Vendor Table.

Id is Text Type. this is my Key Column.

[Suvrutt_Gurjar]

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.

[Suvrutt_Gurjar]

Could you please try the expressions below once?

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

[Jonathan_S]

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

[Steve]

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

[Jonathan_S]

Needs to be of list.

[Steve]

Try it in a temporary virtual column.

[Jonathan_S]

Steve:

SELECT(Security Filters[Vendors], [email]=useremail()) .

All Results are blank.

[Steve]

Then that’s your problem. I don’t see any email addresses in the table contents. Is the email column populated?

[Jonathan_S]

Yes, There is one email that is related to boon. test@gmail.com

Its not in the test output though

Security Filter Table

Vendor Table

[Steve]

Let’s make sure we’re on the same page here… This expression:

SELECT(Security Filters[Vendors], [email]=useremail())`  

will produce a list of values from the Vendor column of the Security Filters table from rows where the row’s email column value matches the current app user’s email. Is that you understanding?

That means it will only produce a result if the current logged-in app user (you) has an email address in the email column of some row of the Security Filter table.

[Jonathan_S]

So to my understanding…

I have a vendor Table.

Related to this vendor table I have an security Filter Table.

Each Vendor Can have multiple email address, Each customer will be able to access there own online items.

Im testing with this.

[Steve · Accepted solution]

I suspect we may be running into a limitation of the Preview as feature. I know it has limits, but they aren’t well articulated anywhere, and I myself don’t use it very often. I suggest you use your own email address. If yours is currently in the Admin Emails table, remove it from there while you test with the Security Filters table.

[Jonathan_S]

Let me try this. I’m finding the security features to be great but they Def need work.

Edit: Ridicoulous. This is now working.

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

There should be a statement when you change your email in what I was using that it does not work for security filters. I though the entire point of it was for security filters.

[Austin]

Do these have to be in security filters instead of slices? I have IN’s in my slice filters and they work just fine.

[Jonathan_S]

Yes, I dont want to have the remote possibility for 1 customer to Even access another customers data.
I also believe security filters will make the app run faster.

[Austin]

Very valid reason to require this be in the security filters.
@Steve I think some testing with the IN formula in security filters might be necessary because it’s a pivotal feature and confirming between this being a bug or just some misunderstanding/misuse would be really nice.
@Jonathan_S try changing the preview to something else and re-applying without making any save changes. I have noticed before that when you save, the preview might not change but you’re actually loaded in as your email.

[Steve]

Austin_Lambeth:

I think some testing with the IN formula in security filters might be necessary

Not until someone can demonstrate it doesn’t work right…

[Austin]

When I get some free time I’ll try although the message before mine now leads me down the just some misunderstanding/misuse. The preview feature is a finicky thing as well.