User role stored in sql

Need advice on the best way to handle this, I'm not a programmer.

We have 11 Roles (ex. Salesperson, Manager, Production, etc), each role corresponds to a certain location (101, 102, 103, etc). A user can have multiple roles per location (location 101 Salesperson & Manger).

I need to limit access to the interface/displayed data based upon these roles.

What is the best way to do this? Ex. Salesperson in location 101 should only be able to search their customers, where a production manager in 101 should be able to see all customers, a salesperson in 101 and 102 should be able to see all their customer's in 101 102, HR should be able to see all customers in all locations but not edit any customer data.

Hmmm, we're only doing this with one "dimension" and not two (for us, this is scope, i.e. client or agency type customer data). There we're storing it in a table in our database with the user's email as the index, and then lookup the {{ }}'s permitted scoped, in each query.


SELECT * FROM customers WHERE scope = ANY(SELECT scopes FROM admin_users WHERE email = {{ }})

admin_users.scopes is of type array, customers.scope is of type string.

Hope this helps you towards the right track!