SECURITY FILTERS: Filter grandchild records based on parent ( [THISROW-2] ) without virtual column

Scenario

  • Multi-user app, with tables for Client, Invoice, Line Item
  • Client table contains client’s email
  • Invoice table contains Ref to Client row
  • Line Item table contains neither the email or Client Ref

That’s the full Line Item table. There are no virtual columns to reference the Client.


Security Filters (Goal)

  • Each Client can see their own Client record, plus any Invoices & related Line Items
  • App-owner can see all records in all tables

Client

This one is easy because the Client’s email is stored in the row we are filtering.

Screen Shot 2020-09-29 at 11.05.37 AM

Invoice

Here, we can use a similar expression, but we have to ‘dereference’ through the [Client] Ref to get the [Email]

Screen Shot 2020-09-29 at 11.05.50 AM

Line Item

Normally I would create a virtual column in the Line Item table to pull in the Client (Grandparent) Ref, and access the Email using the same method as in the Invoice table.

However, I’ve just found a new technique:

TIP

  • If the Invoice table (child) security filter is working correctly, then Invoice[Key] does not return a FULL LIST of Invoice keys-- it’s only the keys the Client is allowed to see.
  • This filtered list can be compared to the existing [Invoice] ref column, instead of having to add a new virtual column.

Screen Shot 2020-09-29 at 11.06.28 AM

So if the Line Item references an Invoice they are allowed to see, then they can see the Line Item too. And any Line Items that reference an Invoice they can’t see will be hidden as well.

By defining the grandchild security filter on the visibility of child keys, the grandchild table effectively inherits the security filter from the referenced child table.

This allows for defining the actual filter in only one location, and all child records will follow.

9 Likes

This is actually ultra great findings and solutions! Well done and I m impressed.

For now (until I get to know about this new tips and tricks), it was always happening to me to how to efficiently apply the security filters.

Simple case.
I have table of inspection report which stands as parent to child table, inspection details. They are connected through usual ref column.

Inspection table have “property name” fields.


For security filter applied to parent table, lets say we let the user to select the name of the property through Usersettings. User select arbitrary name of property and save. then sync starts to pull the records for that property name inspection record only, which is fine. However, we are not able to apply the same security filter onto child table, unless we have physical (not virtual) column for property name. Normally, we avoid to have the duplicated column across other table in view of efficiency. But to apply the same filter to child table, I go back to the table schema setting and add physical column to “store” property name just for the purpose of security filtering.

I even didnt know the tips and tricks you applied to your Invoice table, which make me free from adding unnecessary physical column to associated tables.

What a great help to make our apps more efficient!

thank you again for sharing

5 Likes

I very much echo what @tsuji_koichi has mentioned.

Super Useful.

3 Likes

The techique is also described in this article… https://help.appsheet.com/en/articles/3086424-advanced-techniques-horizontal-scaling

6 Likes

Obviously written by a time-traveler set on ruining this celebration of my discovery! :sweat_smile:

1 Like

Sorry about that :wink:

2 Likes

@GreenFlux Don’t worry. Even though I read this article months / years ago, I didn’t understand it very well. Now it was you that made the usecase clear to me :slight_smile:

3 Likes

Indeed. I echor what you say.

@GreenFlux

1 Like