I agree with @alieskovsky restricted views are your way to go. However, there are two options you can set this up:
- simple: use properties within your data like “Country” or “Customer” to be AND combined with a specific group that should have access to this row. This property based RLS can quickly be applied if you have only few groups. However, if you have many group (I would say 5+) or you expect that groups will change or groups have a very specific scope (like Country + Product Group + Customer) you will go crazy with this.
- complex but scalable: Create just one “RLS_ID” array column through your pipeline, that holds all ids (e.g. foundry:user_id) that are allowed to see this row. The restricted view is then just simply doing a “current user:id” is in RLS_ID column.
This RLS_ID array is constructed from two files you maintain either within your use case or centrally in your org. - → Roles: Defining which combinations of property values describe the scope of a role
- → User Role: which user has which role
Through second approach you can have central team (e.g. in your business/functions) managing the roles and users without having the need to always update restricted view policies. You provide them just the “engine”
