Clarification with Security Filters

[anonymous]

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]

Dave_Willett:

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

Looks fine to me.

Dave_Willett:

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

Also looks good to me.

[Austin]

Dave_Willett:

USEREMAIL()=“ImTheAdminGuySoIWantToSeeEverything@gmail.com”

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

I concur with Steve they look correct to me.

[anonymous]

Thanks guys, much appreciated.

(Austin, its a dummy email lol )

[anonymous]

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”}
  )
)

[anonymous]

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!

[anonymous]

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

[anonymous]

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?

[anonymous]

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?

[anonymous]

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.

Dave_Willett:

[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.

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

1. Where AND( [LoginAddress]= USEREMAIL(), [Branch]= 'A Branch")

2. 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.

[anonymous]

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.

[anonymous]

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.