Security Filter for multiple companies + two user types

[Jake_Naude]

I have multiple companies doing surveys that populate a surveys table.

I have a usertable that has three columns, email, company, role.

The role column has two role types, admin and surveyor

I'd like admin to see all records, and surveyor to only see records from their own company.

My security filter kind-of works but is badly written and is causing issues.

Any suggestions would be welcome. Many thanks... 😉

This is the security filter I have in the surveys table:

IF(

AND(

[surveyor_company] = ANY(SELECT(usertable[company], [Email] = USEREMAIL())),

"ADMIN" = ANY(SELECT(usertable[role], [Email] = USEREMAIL()))

),

TRUE,

AND(

[surveyor_company] = ANY(SELECT(usertable[company], [Email] = USEREMAIL())),

"Surveyor" = ANY(SELECT(usertable[role], [Email] = USEREMAIL()))

)

)

UPDATE:

Here's the problem I'm solving for:

With my security filer in place, I add a row in the app, it appears in my spreadsheet, and after a moment it stays in the spreadsheet but disappears in the app's table view. After a few minutes it shows up again. Also, things happen a little slower.

Without a security filter, I add a row in the app, it appears in my spreadsheet and in the app's table view and the data stays where it should be and all is good, and fast.

I'm a bit confused... 🤔

[AleksiAlkio · Accepted solution]

This should be enough..

OR(

"ADMIN" = LOOKUP(USEREMAIL(), "usertable", "Email", "role"),[surveyor_company] = LOOKUP(USEREMAIL(), "usertable", "Email", "company")

)

[AleksiAlkio]

Do you have a security filter in Usertable as well filtering other rows except app user's own row,

[Jake_Naude]

No sir...

[Jake_Naude]

I've added a email_domain column to the usertable and tried this security filter but the result is the same...

IF(

"ADMIN" = ANY(SELECT(usertable[role], [Email] = USEREMAIL())),

TRUE,

CONTAINS(USEREMAIL(), [surveyor].[email_domain])

)

[romulo_torres]

Hello!

How about try alternatives similar to these:

OR(
"ADMIN" = LOOKUP(USEREMAIL(), "usertable", "Email", "role"),
AND(
"Surveyor" = LOOKUP(USEREMAIL(), "usertable", "Email", "role"),
[surveyor_company] = LOOKUP(USEREMAIL(), "usertable", "Email", "company")
)
)

---------------------------------
IF(
"ADMIN" = ANY(SELECT(usertable[role], [Email] = USEREMAIL())),
TRUE,
CONTAINS(
SELECT(usertable[email_domain], [Email] = USEREMAIL()),
RIGHT(USEREMAIL(), LEN(USEREMAIL()) - FIND("@", USEREMAIL()))
)
)

[Jake_Naude]

Hi @romulo_torres ... Thanks for those...

The first one filtered the data correctly but still gave me the disappearing row issue.

The second one did not... Is it too complicated for the security filter... I read that somewhere... I think...?

Many thanks... 😉

[romulo_torres]

How about:

OR(
"ADMIN" = LOOKUP(USEREMAIL(), "usertable", "Email", "role"),
AND(
"Surveyor" = LOOKUP(USEREMAIL(), "usertable", "Email", "role"),
ENDSWITH(USEREMAIL(), LOOKUP(USEREMAIL(), "usertable", "Email", "email_domain"))
)
)

[AleksiAlkio · Accepted solution]

This should be enough..

OR(

"ADMIN" = LOOKUP(USEREMAIL(), "usertable", "Email", "role"),[surveyor_company] = LOOKUP(USEREMAIL(), "usertable", "Email", "company")

)

[Jake_Naude]

Many thanks @AleksiAlkio... That filtered the data correctly but still gave me the disappearing row issue... 🤔

[AleksiAlkio]

If the new row disappears  after the sync is completed, it means the security filter filters that row.

[Jake_Naude]

And the confusing thing is that, after a while, it reappears... It's almost as if the security filter does what it does, then changes it's mind...?

Give me a sec and I'll record it and post it back here...

[AleksiAlkio]

It sounds the reason is something else than the security filter itself. Do you have something else happening on the background that could change the data?

[Jake_Naude]

I have a bot that fires on Data Change ADD and sets the values of the number column, and the survey_number column.

number column expression:

IFS(

ISNOTBLANK([survey_number]),

[number],

ISBLANK([survey_number]),

MAX(SELECT(surveys[number], [surveyor_company] = [_THISROW].[surveyor_company])) + 1,)

survey_number expression 

IF(

ISNOTBLANK([survey_number]),

[survey_number],

CONCATENATE([surveyor_company], "-", [number], "-", [location_code])

)

I currently have the bot disabled, but it's the same... Here's the bot setup:

[AleksiAlkio]

Add a new row and at the same time watch the data source what's happening. Does something change that record after the save is done?

[Jake_Naude]

I copied the row as it popped into the spreadsheet and compared it to itself after the table view row disappeared and again after the table view row appeared again. No change. Identical.

[AleksiAlkio]

Duplicate key values?

[Jake_Naude]

No duplicate Key values... I just checked all my tables...

[AleksiAlkio]

How many seconds between when the row disappears and than back again?

[Jake_Naude]

Between 2 and 3 minutes...

[Jake_Naude]

---

@AleksiAlkio wrote:

It sounds the reason is something else than the security filter itself. Do you have something else happening on the background that could change the data?

---

The odd thing is though... That when I turn off the security filter, it all just works and nothing disappears... 🤔

[Jake_Naude]

I've just stripped my app back down to the basics...

I deleted all the actions... Only left one view... Killed all the slices... Deleted every bot...

Still the same thing... 🤔

UPDATE:

I have reinstated the full app after finding no change to the behavior in the pared back version.

[Jake_Naude · Accepted solution]

UPDATE:

I can't explain it but It works now... 🚀  😉  😎

These are the steps I stumbled through:

The issue persisted this morning, connected to OneDrive and SharePoint (at different times)

I suspected the issue was somehow connected to OneDrive or SharePoint, so I moved my sheet to a new Google Drive data source and it tested perfectly there.

I moved it back to OneDrive, and it's working perfectly there too.

Thanks for your patience and persistence @AleksiAlkio and @romulo_torres. Your guidance and security filters have no doubt played a significant part in getting this up and running. Cheers.

Much appreciated... 😉 👏👌

[AleksiAlkio]

That's weird indeed.