Make this data type private by default - team access to a thing (Role-Based Security at the row level)

[james.evason]

Hi - I’m reviewing Bubble with a view to using it to make a SaaS version of a legacy app. I have a question as follows:

TLDR: How to make Role-Based Security at the row level in Bubble.io

Use case:

• Users work in teams.
• A user might be assigned to a team and later de-assigned. This would cover a period of volume stress.
• Users create transactions - think of an insurance claim.
• When the transaction is created it is associated to one of the teams that the creating user is attached to. Think of teams like Northern, Southern, Western, etc.
• The transactions must be accessible by any member of the team associated. So a transaction associated to the Northern team can only be accessed by a user who is a current member of that team. Think of a team of clerks at a call centre - any clerk (user) can update a transaction linked to their team.

This is the Role-Based Security pattern. I know Bubble has its own layer of data access security, however the difference here is that access must be via the user being a member of a list that changes frequently AND that list must be managed via the app UI, not by app design admin.

If I were doing this in a SQL RDBMS I would have tables

transaction(transid, title, teamid,…)
teamMember(teamid,userid,…)

then my query would be

select * from transaction where transaction .teamid in
(select teamid from teamMember where userid = currentUserId)

How would I do this with Bubble?

Thanks in advance

J

[mikeloc]

Hi there, @james.evason… if I understand your post correctly, it sounds like you need a custom data type for teams, a field on the User data type that stores a list of teams to which a user belongs, and a field on the Transaction data type that stores the team with which a transaction is associated. With those data types/fields in place, you would be able to create a privacy rule on the Transaction data type that looks like the following, and you should be good to go.

Hope this helps.

Best…
Mike

[kevin35]

@mikeloc Thank you for that … One further question on this…

How would you do this if you need the user to have a different role on the transaction? For instance, if you have a project, and different users have different roles on the project.

project = {id, name, commission_amount}
user = {id, first_name}
user_to_project = {id, user_id, project_id, role}

1. users should only be able to view the projects they are assigned to through user_to_project
2. Only users with role “Project Lead” should be able to view project.commission

[mikeloc]

Hi there, @kevin35… I might have to think this one through a bit more, but if you want to use privacy rules (which have a well-documented limitation), I believe you would need to have multiple list fields on either the User data type or the Project data type, and each list field would be associated with a specific role. So, you would either have multiple list fields on the User data type that store lists of projects for each role or you would have multiple list fields on the Project data type that store lists of users for each role. With those list fields in place, you should be able to build the necessary privacy rules by checking to see if a project is in a user’s specific role field or if a user is in a project’s specific role field.

Another way to go would be to use an option set to define the roles and then have a custom data type that stores the association between a user, a project, and the user’s role on the project. With this structure, I don’t believe you would be able to use privacy rules, though, because of the aforementioned limitation.

Anyway, just some food for thought there, I guess, and I hope it helps.

Best…
Mike