Security filter with multiple rules

[jjay]

Hi I want to create a security filter with the following

1. Restrict users based on their user email and team name

(IN(Worker[team_name], SELECT(Security[team_name], [email] = USER EMAIL())))

2. Give "unrestricted" (all rows) access to my team (owners of the app) and some managers

(IN(LOOKUP(USER EMAIL(), Security, email, team_name), LIST("unrestricted")))

I am able to achieve this separately and not together

Worker table : which has team_name ie. to which they belong to 

Security table : has permission details, ex; Username, team_name (to which team they should be given access to, "unrestricted" meaning they will get access to all of the data)

I tried the below and it did not work

(IN(LOOKUP(USER EMAIL(), Security, email, team_name), LIST("unrestricted"))) OR
(IN(Worker[team_name], SELECT(Security[team_name], [email] = USER EMAIL())))

Thanks in advance

[MultiTech]

If you implement the current user system inside your app, you could base your permissions off of the roles you assign to the various different users. This is a very common thing that I'll do, in just about every app that I build, including a way to differentiate one user-group from another; then I'll base my permissions off of who had what role. 

Admin vs. User

You can get all kinds of complicated with this sort of setup, giving an easy way to essentially turn off the security filter for your admins... And then implementing more complex security filters for your base users.

[jjay]

Thank you!

Can you please share an example for clarity ? In my usecase there might be over 200 people who should be given admin / unrestricted access,
How do I do that ?

[MultiTech]

---

@jjay wrote:

there might be over 200 people who should be given admin

---

The core of the Current User System, is a User table with 1 record corresponding to each person using the app.

• Each record holds the email a person is using the access the system
• Inside that record, there's a [User_Role] column
• Assign "Admin" to those who should have unrestricted access

Then it's just a matter of creating your security filter; I would try something like the following:

SWITCH(Index(Current_User[User_Role], 1), 
  "Admin", TRUE, 
  "User", in([Record_Team_Link], Split(Concatenate(Current_User[User_Assigned_Teams]), " , ")), 
false)

• Pull the [User_Role] value from the Current_User (slice)
• If the value is "Admin" - show all records
• If the value is "User" - Is the record's [Team_Link] inside the assigned teams for the current user record?
• Otherwise... there's either NO user record for the person, or they weren't assigned a role with permissions to view this table's data - so false to remove all records.

If your Team record instead holds a list of all the assigned users:

• Either with an EnumList (base type ref to the User table) where someone selects the assigned people
• Or if you have a join table making a user-team pairing; you can then create a derivative list, using a list dereference, to get a list (on the team) of all the assigned users

SWITCH(Index(Current_User[User_Role], 1), 
  "Admin", TRUE, 
  "User", in(Index(Current_User[UserID], 1), [Team_Assigned_Users_List]),
false)