Using a subset of a users table in a second App

Hello Friends:

I have two Apps that share a users table, but the second App only needs a strict subset of the users in that users table. There’s a column in the users table that indicates the role of the user.

Currently, the second App performs a check at login time to see if the user attempting to log in is part of that subset (by checking that column), allowing them in if so, and rejecting them otherwise.

But this is done in code, which can fail, or develop a bug, or etc. A more secure solution is not sharing the users table, and just replicate only the needed subset into a dedicated users table in the second App.

This requires a script. That isn’t a big deal, but is there any Anvil feature that might help here (besides that)?

Thank you!

Yes. Database table Views can filter down to a desired subset of records.

This could noticeably simplify the code required to keep the 2nd table in sync, as the filtering (of the source or “master” table) would be done by the View.

3 Likes

Thank you sir. You’re absolutely right. I thought about Views briefly some time ago (same strategy that I use in PostgreSQL… well, we all have, haven’t we), but I quickly forgot about that with Anvil Data Tables, mostly because I think the feature isn’t available in my Personal plan and can’t really afford the next tier (I’m in the red already with this personal project), but I’ll ask @anvil. But you’re correct about Views and code simplification, which I want to do and really important here. Thank you again.

Edit: Looking back at it more deeply now, perhaps Views are available in the Personal plan, and I’m confusing it with things like Accelerated Tables or something else (it’s been a while since I originally thought about those things - I’m still new). I’ll still reach out to Anvil. I would like to upgrade a little. :relaxed: Thank you again @p.colbert .

Fairly sure Views are available, as are Queries.

2 Likes

To keep this conversation on views going, in another part of my application, I run add_row() on Data Tables (including data to Linked Columns), but do so by passing the data structure to the backend Server Module, and let it run add_row(). If I collect, say, 5 entries from the User, that would be 5 calls.

This is one alternative approach to providing a Client writable view to the same Data Table, correct? I’m just try to validate or invalidate my understanding and options. (Btw, This is separate from the user login scenario posed earlier, but in a way it’s all related in terms of access, code development and security). :slight_smile:

And getting back to user logins, I think I still have to use a separate users table because I imagined views as something akin to the relational world, where they’d already exist before login such that get_login_with_whatever_method() could perform a lookup against that subset view. But in re-reading the docs, views are just-in-time objects. Unless you could pass a view object to the various login methods. I have to play in the IDE a bit tomorrow, but I don’t think it’s possible. In other words, I’m trying to let the native Anvil login method itself, without wrapping logic around it, reject or allow the login.

That sounds like a great idea for a Feature Request!

1 Like

Unless you batch them. Which you might want to do for performance purposes: one round-trip to the server instead of 5.

While this is related to your same program, in a broader sense this is moving off the stated topic. It might be wise to start a new thread.

1 Like

I agree. I’ll put up a feature request over the weekend.

EDIT: Request filed here.

1 Like