Clarification with Security Filters

Dave_Willett · 08-19-2020 11:32 AM

Hello All.

I’m very close to completing my App and need to apply the security filters. I think I have the filters correct but if I could pass them into the forum to sense check then I could save some embarrassment going forward. I watched and studied the Office Hours ‘Slices, Security Filters and User Roles’ last night and gained quite a bit of knowledge.

I have a users table ‘Operators’ with columns:
[ID]
[Branch]
[Operator] (Name of User)
[Role] (Manager, User, Administrator etc )
[EmailAddress] (Preferred Contact)
[LoginAddress] (USEREMAIL())
[Active] (Yes/No)
This is the master table I want to pull permissions from.

In another two tables, that we enter data ‘CollectionDetails’ and ‘Customers’ I have applied a security filter:
OR(
LOOKUP(USEREMAIL(),“Operators”,“LoginAddress”,“Branch”)=[Branch],
USEREMAIL()=“ImTheAdminGuySoIWantToSeeEverything@gmail.com”)

Every table in the App (more than two) has a [Branch] column which is filled from the Operators table and every other table has a [SalesPerson] column set to USEREMAIL().
The filter only lets users from their respective [Branch] to see their data. Only two tables are seen by users of the app ‘Customers’ and ‘CollectionDetails’, and I have that security filter in both.
All other tables are either RefRows or lookup tables so would I be right in thinking those other tables will not require the filter?

Next in the process is the Editing rights to the other tables, using behaviour - system actions I want to restrict editing to either the User via USEREMAIL() or a role from table ‘Operators’.

OR([Salesperson]=USEREMAIL(),LOOKUP(USEREMAIL(),“Operators”,“LoginAddress”,“Role”)=“Manager”)

So drilling down I am feeling I could have a secure solution but I’m not sure of my syntax, this is where I could do with some assistance.

If one of you educated guys want to hop into the App and have a look around I am quite happy for that to happen.

Or… do my theories and filters look correct? I have put alot of effort into my App and learned quite alot it would be a shame for it to fail.
Here’s my nearly finished App

Much Appreciated.

Steve

Looks fine to me.

Also looks good to me.

Dave_Willett

Hi Steve
Can I add another role to this? I get the paranthesis errors I seem to be struggling with:

OR(
[Salesperson]=USEREMAIL(),LOOKUP(USEREMAIL(),
“Operators”,“LoginAddress”,“Role”)=“Manager”)
“Operators”,“LoginAddress”,“Role”)=“Administrator”)

Steve

Should be:

OR(
[Salesperson]=USEREMAIL(),
LOOKUP(USEREMAIL(),“Operators”,“LoginAddress”,“Role”)=“Manager”,
LOOKUP(USEREMAIL(),“Operators”,“LoginAddress”,“Role”)=“Administrator”))

Or, better:

OR(
  [Salesperson]=USEREMAIL(),
  IN(
    LOOKUP(USEREMAIL(),“Operators”,“LoginAddress”,“Role”),
    {“Manager”, “Administrator”}
  )
)

Dave_Willett

Fantastic Steve, I hade the basics, expressions do confuse me but the way you lay them out does make them easier to understand.

Kind Regards

Steve

We’re here to help!

Dave_Willett

One last filter but on the current table ‘Operators’.

OR(
LOOKUP(USEREMAIL(),“Operators”,“LoginAddress”,“Branch”)=[Branch],
USEREMAIL()=“theadminguy@gmail.com”)

Is sucessful in looking up the Branch from the Operators table from any other table, the filter doesn’t like being called from the same table ‘Operator’ and bombs out.

I could quite easily set the filter as:
[Branch]=“MMVR”
which is hard coded and defeats the object of the App.

So, the what would be the syntax for the ‘Operators’ table to achieve:
Only show the [Branch] for the current logged in useremail without actually hard coding the branches individually?

Or have I found the solution using the following filter??

OR([Branch]=[_THISROW].[Branch],USEREMAIL()=“theadminguy@gmail.com”)
(No, it returns all rows not just the branch associated with the logged in user)

Suvrutt_Gurjar

Could you please update if it is an expression for the security filter? And are columns 'Branch" in LOOKUP() and [Branch] in comprison are in the same 'Operators" table?

OR(
LOOKUP(USEREMAIL(),“Operators”,“LoginAddress”,“Branch”)=[Branch],
USEREMAIL()=“theadminguy@gmail.com”)

Dave_Willett

Hi Suvrutt

It is an expression in the Security Filter for the table ‘Operators’ and that table contains the columns of:
[LoginAddress] (USEREMAIL()
[Branch]

I only want the logged in users ( there could be many LoginAddresses within the same branch ) to see there own branch plus me (TheAdminGuy) to see all branches.

I hope this makes sense.

Suvrutt_Gurjar

Than you Dave.

Could you please update what is the [LoginAddress] column type? And will there be one row per [Branch] or one row per [LoginAddress] (if it is Email type column) in the table?

Dave_Willett

Thanks Suvrutt
LoginAddress is email with USEREMAIL() As Initial Value. There will be many branches (duplicates) and one LoginAddress per Operator

Suvrutt_Gurjar

Thank you. Could you please update how the fields [LoginAddress] and [EmailAddress] are used?

Since [LoginAddress] has USEREMAIL()as nitial value, is it changed while saving record or later?

Also in general, any specific reason a simple filter like [LoginAddress]=USEREMAIL() will not work?

Dave_Willett

LoginAddress is recorded at Login as the USEREMAIL() whilst [EmailAddress] can be the users preferred email address to later use in a workflow, so that can be different than the actual App login address. This is one entry per Operator as it is a table of App Users.

[LoginAddress]=USEREMAIL() would work fine if I only wanted the user to see his profile, I wanted users to see ALL users within his branch.

Cheers

Suvrutt_Gurjar

Hi Dave,

Thanks for that additional data point. The requirement is becoming interesting.

So is it correct to assume that there will be some records

Where AND( [LoginAddress]= USEREMAIL(), [Branch]= 'A Branch")
Where AND([LoginAddress] <> USEREMAIL() , [Branch]= 'A Branch")

You would like combination of records in both 1) and 2) above based on USEREMAIL() through security filter.

Dave_Willett

Hi Suvrutt
Yes, each user or LoginAddress is based at a branch, there are several branches:

MMVR
Ostwestry
MMLEEK

they are all branches.
There could be many staff (LoginAddress) at a particular branch, I only want the staff to see all the records for his branch. This could be hard coded , ie, [Branch]=“MMVR” but I am trying to avoid doing that. So the expression needs to say…
I am logged in as abc@gmail.com and my branch is MMVR.
Return all records from the table ‘operators’ where the branch = my branch.
Or
I am logged in as xyz@gmail.com and my branch is MMLEEK.
Return all records from the table ‘operators’ where the branch = my branch.

Suvrutt_Gurjar

Thank you Dave. I believe easier thing will be to create a BranchUsers table with columns such as each user’s email and branch . This table records each user’s email and branch - one record per user.

Then in the “Operators” table, you could have a security filter expression for “Operators” table such as

OR( [Branch]= ANY(SELECT(BranchUsers[Branch], [Email]=USEREMAIL() )), USEREMAIL()=admin@email.com)

or as follows

OR( [Branch]= LOOKUP(USEREMAIL(), “BranchUsers”, “Email”, “Branch”)=[Branch],
USEREMAIL()=admin@email.com)

Edit:: As per my understanding, since the multirow expression on the same table or a use of VC is not possibe in security filters, the required condition of selecting all records from the same branch just based on one user’s email may not be possible. And hence the need for an additional table of User Branch combination as suggsted above.

Dave_Willett

Thanks Suvrutt
It seems more difficult than I thought as based on the host table.

The expression:
OR(
LOOKUP(USEREMAIL(),“Operators”,“LoginAddress”,“Branch”)=[Branch],
USEREMAIL()=“theadminguy@gmail.com”)

Works from every other table/security filter and returns correct results, how strange we can’t filter from the same table and lookup from itself…

But I thank you for your assistance, I’ll work out a way to incorporate a separate table or even a slice may do the trick.

Suvrutt_Gurjar

Hi @Dave_Willett,

Yes, as we have seen the security filters are not allowed with multirow expressions in the same table nor VCs, I believe we need to have additional table. I believe your requirement is similar to the use case described with the below expression in the article below. Even in that use case, there is a separate table “CustomersToReps”

IN([CustomerId], SELECT(CustomersToReps[CustomerId], [SalesRepEmail] = USEREMAIL()))

Hi @Steve :May I request your guidance, in today’s discussion thread at your convenience.

Dave_Willett

Thanks again, and it really is thelast piece in the jigsaw…

Dave_Willett

Ok, I think I’ve done it.
Slice created from the ‘Operators’ table with a row filter condition of:

IN([Branch], SELECT(Operators[Branch], [LoginAddress] = USEREMAIL()))
And base the view on the slice. That shows everyone in the LoginAddress [Branch] for the logged in user.

Then duplicate the view, call it “Operators Admin” and base that on the raw table, menu, and a ShowIf of
USEREMAIL()=“ImTheAdminGuy@gmail.com” which only I can see.

Suvrutt_Gurjar

Hi @Steve,

Thank you for your guidance. I understand now.

Hi @Dave_Willett,

Thanks for your update. You seem to have solved the requirement by creating a slice. I believe slice filters and security filters are fundamentally different from security point of view and the way data reaches a user’s device. Our initial discussion was about using security filters. I believe the constraint describded by @Steve above for security filters is not applicable for slice filters and so slice filter conidtion on same table works.

Anyway, I believe that slice filter may be OK for you because as per requirement, each person in that branch needs to anyway see all the records for that branch so I believe essentially security filter is unnecessary. However I believe with slice filter all the records of all the branches will reach user’s device in the absence of a security filter. You may evaluate that if it is OK for you.

I believe in this process , we got some more insights on security filters and slice filters, especially after @steve’s guidance.

Dave_Willett

Thank you guys. I’m learning every day and improving as I go.
I understand I have to trade off using the slice but for this purpose it might not be that detrimental.

Suvrutt_Gurjar

Thank you Dave for the update. Nice to know you have evaluated the impact of using a slice.

Steve

It appears the issue is attempting to apply a security filter that references the very table being filtered. That’s not possible, as the table itself is only complete and usable after the security filter has been applied; therefore, the table as a whole is unavailable within the security filter expression.

I only figured this out myself in the last few weeks.

Dave_Willett

Yes that makes sense when you know the sequence of the table loading and as Suvrutt suggested a different table would only question the Operators table once it was available.

But for my purpose another table would cause me issues, my work around above does just what it needs, give users views of members in their Branch but allow me to see everything.

Result

Austin

I didn’t know google allowed emails of such length.

I concur with Steve they look correct to me.

Dave_Willett

Thanks guys, much appreciated.

(Austin, its a dummy email lol )