Table Security Filter expression reference to other rows in table

[dbaum]

tl;dr:

How can I load only user's own records if user doesn't have advanced role but load all user records within a tenant if user has advanced role.?

What I tried first

I had a security filter that referenced other rows in the table it was filtering, and AppSheet won't process it and flags the self-reference as an error, which I now see makes perfect sense. I'm glad for the tight security, but I'm not sure how to accomplish what I need, so I'm reaching out here.

My table

I have a Users table in my multi-tenant app. Every row has (in addition to other columns):

• a UNIQUEID() as the [User ID]
• the user's [Email] address
• a [Tenant ID] from my Tenants table
• an [Active] boolean
• a [Role] (i.e., Admin, Leader, or Member)

A single email address can exist in multiple Users table rows because the person who owns that email address could have an account with multiple tenants, and potentially have a different role in each tenant (e.g., a Leader user in one tenant could be a Member user in another tenant).

My goal

I need the security filter on the Users table to load rows as follows:

• For users with any role: All the Active rows where the email address equals the USEREMAIL() of the app user
• For users with an Active row that has an Admin or Leader role for a tenant: All the tenant's users' rows

My ideas to try next

Is there any way to do this in a single table? Maybe by somehow maintaining (via an action?) a column that for Admin/Leader rows includes all the tenant's User IDs?

If that doesn't make sense, I'll likely try maintaining a shadow table with only rows for users with advanced roles. Although I haven't yet explored automation and action functionality, it sounds plausible to me that I could add/delete a row in an Advanced Users table everytime a user receives/loses an advanced role in the main Users table. Then, I think I should be able to reference that Advanced Users table in the main Users table's security filter. Does this approach make sense?

Any different guidance?

More background

In case it helps illustrate, here's the self-referential security filter expression for my Users table:

OR(USEREMAIL() = CONTEXT(OwnerEmail), AND([Email] = USEREMAIL(), [Active]), IN([Tenant ID], SELECT(Users[Tenant ID], AND([Email] = USEREMAIL(), [Active], IN([Role], LIST("Admin", "Leader"))))))

 In case it's relevant, here's more context that I think precludes certain alternative approaches:

• I'm not limiting users to a single row in the Users table (e.g., with a column that lists multiple tenants) because I don't want changes made by one tenant who can manage user details to affect the user's record for another tenant.
• I assume I'll need to make the app public for anyone to sign into rather than explicitly share the app to authorized users because I need tenants to be able to add/remove users, including assigning advanced roles to some users, rather than depend on me (the app owner) to do that. (This raises other challenges--e.g., related to billing if a non-user or inactive user signs in--that I'm working through and will likely ask about later in this forum.)

Thanks for reading through this and offering any suggestions. I've found lots of great guidance in this forum and hope someone has already figured out how to address this use case in their own apps.

[Steve · Accepted solution]

I think I'd go with two tables: one for the users with "advanced" roles (email, tenant ID), and one for all users (email, tenant ID, role, etc.).

[SkrOYC]

I think I've understand this clear, let me give a suggestion and also correct me if I'm wrong.

What I understanded

You need to have a filter to make sure users:

1. If they are Members by the Role field, just see their own rows (the ones where the [Email] column = USEREMAIL()
2. If they are Admin or Leader, see all of the rows where [Tenant] equals their [Tenant].

Now, the problem with this from my pov is that a strong usertable should just have one row per user, and you told us that they can have more than one depending on their Tenant.

My suggestions/Questions

What are the "Members" on the App?

As my limited english knowledge goes, this is related to rent? I'm a little bit lost there. My questions is, could you have members on their own table separated from Admins and Leaders?

I'm gonna add more input afterwars.

Also, take a look at User Settings, maybe you can find a good solution by using it.

Maybe you could leave the table as is but allow a user setting where they can change which role they want as the "active" one. This way, eventhough they can be Admin, Leader or Member, they have to choose wich ones should apply while they use the app.

USERSETTINGS() | AppSheet Help Center

[dbaum]

Thanks for considering my challenge and discussing with me. I think you've understood my goal accurately.

My app doesn't happen to be related to rent. Maybe the term "tenant" is confusing. While that word can mean someone who rents an apartment or an office from a landlord, I'm using it in an IT sense (see Multitenancy) to mean an organization using my app whose data is logically segregated from the data of other tenants.

Member is just the least priveleged role. Here's the hierarchy from highest to lowest:

1. Admin
2. Leader
3. Member

Both Admin and Leader can manage other users (e.g., add, update, delete), which is why they need access to all the user rows for their tenant. A Member cannot access information about any other users in their tenant.

I'm indeed already using User Settings. Here's an outline of my current draft design. The step to filter the Users table is what I'm trying to figure out.

1. User opens app and authenticates
2. User table is filtered as I've described
3. App starts on the User Settings screen
4. There's a single User Setting. It's an enum column where the list items are all the tenants for which the user has an active account. The user selects one tenant to apply to the current session.
5. A "Current User" slice of the User table is filtered to the user's single row for the tenant from User Settings.
6. All other permissions throughout the app are governed by the tenant and role from the Current User slice.

As you suggest, maybe I need to manage two user tables (e.g., Advanced Users; Basic User) and reference them jointly or separately as needed.

(Did I see in one of your posts somewhere a reference to Spain? If so, también podemos conversar en español, aunque en ese caso habrá detalles que pierdo yo pues no es mi primer idioma.)

[SkrOYC]

1. You already have a tenant usersetting, checked
2. You have a Current User slice, checked.

You can have a security filter where:

SWITCH(
  INDEX(CurrentUser[ROLE], 1),
  "Admin", [Tenant ID]=INDEX(CurrentUser[Tenant ID], 1),
  "Leader", [Tenant ID]=INDEX(CurrentUser[Tenant ID], 1),
  "Member", [Email]=USEREMAIL()
)

[dbaum]

Thanks a lot for this suggestion. However, I think it won't work because it ends up being circular. The only way to populate the User Settings [Tenant ID] column with the list of tenants that the user is allowed to select from is to first load the Users table, which is what I need to filter. For that matter, the Current User slice itself is a row from the Users table and, therefore, depends on first loading the Users table.

As @Steve suggested and I commented in my originl post, I'm thinking that I need to go with a second table one way or another--either maintain Member users in one table and advanced users in another table or maintain all users in a single table but automatically add/remove clones of advanced users' rows in a separate table.

[SkrOYC]

Try it

[Steve · Accepted solution]

I think I'd go with two tables: one for the users with "advanced" roles (email, tenant ID), and one for all users (email, tenant ID, role, etc.).

[dbaum · Accepted solution]

Thanks. I finally got back to this, and your recommendation is more or less what I ended up doing. Here's a simplified outline of the implementation for anyone's reference.

Table:	Users (i.e., all users)	Access Managers (i.e., only users with a role that allows managing other users)
Columns:	Email, Tenant ID, Role, etc.	Email, Tenant ID, Role
Data entry:	Via UI	Cloned from Users table only for rows with pertinent role. Cloning managed via a bot that runs relevant actions anytime a Users table row has its role changed.
Security filter:	Any of the following: Email = USEREMAIL() Tenant ID in Access Managers table	Email = USEREMAIL()

[SkrOYC]

Can you confirm if you tried to do this?

I think I applied it on one of my apps (the usage of data from the same table for a security filter)

[dbaum]

No, I didn't try that. When you suggested "Try it", I thought you were referring to my immediate prior comment regarding the multiple tables approach. I didn't try your seemingly elegant SWITCH approach because of my observation that the app just doesn't load when a table's security filter references a slice and a user setting value that both derive from the table being filtered. If I get a chance, I'll also test out your SWITCH technique.