Having trouble with Security Filters to ensure data to client is filtered

[dbweb]

Tables: 

Customer: one row per customer

Users: one row per user, with a Ref to Customer

Manager: one row per manager, with Ref to Customer and User

I'm trying to limit 2 specific situations: 

1. Users should never have emails of users that are not in their same Customer downloaded to the client

2. Users should never have other Customers downloaded to the client

If I can somehow do a lookup on Managers table without it containing email address, that would prevent that email from being downloaded to the client.

I've been working on this for hours, and thought I was on the right track until I realized my Security Filters created a circular dependency. 

Please let me know if there's any additional info I could give to help. 

[dbweb]

So I think I've made some progress on a possible solution. 

I created a copy of the User's table "UserClone"

Since the Customer is on the original User table, I have a Security Filter based on Email to Filter that table down to one row, the User.

For both the Customer and Manager tables, both of them filter by the one row in Users: 

example of Customer:

IN([ID], SELECT(Users[Customer], [Email] = USEREMAIL()))

Finally, we can now filter the UserClone table: 

IN([Customer], SELECT(Customer[ID], (TRUE = TRUE)))

So I think this will work. The User's tables I'm currently planning to manually manage, since I need to manually add users to the App anyways. If someone more experienced than I could comment and/or improve my code I'd be happy to hear it. 

[Marc_Dillon]

I don't recommend a "manager" table. I recommend splitting up the "user" table into 2 separate tables.

First user table:

id	email
1	a@a.com
2	b@a.com

With security filter : [email] = USEREMAIL(). Can include any other info that a single user may need for the app functionality.

Then a "user_link" table that links users together. If we assume user 1 is a manager of users 2,3,4, then:

manager	user
1	2
1	3
1	4

With security filter : IN( [manager] , user[id] )

Then a second "user_detail" table, with a 1-to-1 relation to "user", that includes all other user details that may need to be shared to other authorized users ("managers"). This could potentially be the exact same info from the first table. This is easier done with a sql database as one can simply be a "view" of the other, instead of separate tables.

Security Filter : IN( [id] , user_link[user] )

https://help.appsheet.com/en/articles/4575739-list-from-table-column-reference

[dbweb]

Thanks Marc. 
My usage of "manager" is probably a bit confusing. "Managers" in this app can perform administrative actions over all users/data, there's no relation from managers to users. 

The part that complicated things for me what that I essentially needed a complete copy of the Users' table, as I'm applying two different Security Filters to it. 

This is to avoid/prevent any leakage of Customer relations on the User tables. 

I definitely empathize with you about things being easier in SQL. Note that all users have a corresponding Customer association, so I need to have that Security Filtered everywhere.

[Marc_Dillon]

I assumed the difficulty came from the fact that some users needed access to other user's data. If that's not the case, then I don't see where your difficulty is coming from.

How and why are you applying 2 different security filters on the same table?

[dbweb]

"Managers" need to view all User's data.

But both Managers and Users are associated with a Customer. And they should only be able to view records of the associated customer. 

I needed to create a Security Filter at the customer level. But I first needed the Customer ID of the User running the app. 

So I filtered the Users table based on User email, which gets me the Customer the User is assigned to. 

But then that means my Users table only has one User in it. 

So I cloned that table to then be filtered by Customer only. 

All of this is to prevent anyone downloading Customer data and Users of other Customers data. 

[Marc_Dillon]

Ok. So then to expand on my example above to accommodate for that, include a [role] and [customer_id] in the first user table. Probably don't even need the user_link table. Change the user_detail table's security filter to IN( "Manager" , user[role] ). Customer table security filter would be IN( [id] , user[customer_id] )