Restrict app usage. Security and access expressions

[StephenMattison]

Howdy! I think I asked for too much in my original Question for this, so I eliminated all but the most crucial part here. Thanks for your patience & help!

I need help with a security or access filter expression for my Club Meetings app.

My Tables are Clubs, Members, Meetings. I have an "Active Members" Slice with only members where "Active" is in their [Status] column in the Members table. Members table also has an [Access] column that will be either "User or "Admin." Members table is Ref-ed to Clubs, Meetings table is Ref-ed to Members table & Clubs table.  There will be many clubs, many members & many meetings. [_ComputedKey] is the Key column in all 3 Tables.

I need to:

1. Limit app users to only be able to see data pertaining to their Club, based on [Club] column in their row of Members table.

Thank you very much!

[ali-hussain]

Clubs need to know who their members are. If a person can only be a member of one club then a Ref to the club is the easy way. If not then maintain a list of users and add or remove from the club, say "Club Members". Also you'll need users to have something like a Role column with an Admin role. Your security filter becomes: or(filter("Clubs",in(useremail(),[Club Members])),lookup(useremail(),"Users","Email","Role")="Admin") this will cover both the options of a regular user being a member of the group and the admin being able to make administrative actions.

[StephenMattison]

Thanks Mr Ali! I just added this sentence to my above Question, sorry I forgot it originally. "Members table also has an [Access] column that will currently be either "User or "Admin."

I think I can tweak your excellent expression into what I need. Thank you also for including an "Admin" part to the expression, I need it. I have a bad habit of asking too many things at once & I thought I'd get the basic User access expression working then add my Admin access, which will actually/eventually have to be more complicated than most apps would require.  So big thanks for thinking about Admin up front.

I'll get this first part working, but will eventually need at least two more layers of Admin-type access. The Club will have "District" and "Regional" managers that will need to have access to data from all Clubs & Members in their area. I was hoping to simply expand my [Access] choices to "User", "Admin", "District", "Region" and then expand all of my security/access expressions accordingly.

Thanks again!!

[ali-hussain]

You're welcome. If there is a many to many relationship, i.e., a user can be in multiple clubs then you may need to add an additional layer of complexity. Because each user would have a different type of relationship with each club. Like I can be the district manager for a club and a user in another. If you have to do this, then you may need to create another table with the columns User, Club, Club Role. I would also create a virtual key column concatenate([User],"-",[Club]). This way, any time you create a club, you can also specify the role the user has in that club. 

Hmm, was looking at the expression I gave you. I might have made a mistake:  or(in([_THISROW],filter("Clubs",in(useremail(),[Club Members]))),lookup(useremail(),"Users","Email","Role")="Admin")

[StephenMattison]

OK, thanks again Mr. Ali.  And yes, what you've described will happen, the District & Regional managers will be an Active Member in one Club, but will require access to all other Club data in their District. I'll get the expression that you've kindly shared working & then we'll adjust from there.

I am hoping to not have to add any more Tables, but if that's the only, or best way then I will do it.  Instead of another Table, could I use a Virtual Column to pull in a List of Admins for the Club and use that instead? I am currently doing this to pull in the Users [Access] with this in a VC... SELECT(Members[Access],AND([Email] = [_THISROW].[User email],[Club] = [_THISROW].[Club])).

TYVM!

[ali-hussain]

If an Admin is an admin, regardless of which club you don't need a new table for that. 

But if someone is a District or Region manager in some clubs but not in others where they have access then you'll need a new table. 

[StephenMattison]

Thx!