I am working on an application with some basic role based access control. I made a lucid chart of the current design here: https://www.lucidchart.com/invitations/accept/49ba4012-c2cc-48de-b11b-5d9c357a66d7
The main objects so far are Accounts, Companies, and Users where a user can be a member of many companies, and there are many companies under 1 account. For example, I can sign up and create a "Bob's Tire Shop" account and I can have several companies under it such as "Bob's Tire Shop in New York" and "Bob's Tire Shop in Chicago", etc.
I want users to be able to be a part of multiple companies, so if Joe is a mechanic at two shops, he should be able to access the info for the the respective shops he works at. I can do this with my current database design, but I am stuck on how to grant account level permissions to a user. For example, when Bob creates the account, he is the account "owner" so he should automatically be able to perform admin actions across all companies. I should also be able to have other "account admins" who can access all companies as well. Or, if I want to give a user "billing" access to an account, he should only be able to see the billing info and not necessarily be a member of the individual companies.
Is it as simple as create an account_users join table between accounts and users with an account_roles table? Or am I missing something else. Any help would be much appreciated.
I think you can create another version of the memberships table that's related to accounts.
This membership_accounts table would store the relationships between accounts and users, and capture the level (account ID, user ID, level). It's essentially the same as the memberships table, but relates to accounts.
The level column in this table would be a foreign key to the memberships_levels table.
Then, to understand a user's full set of permissions, you can get a UNION ALL of both the memberships and membership_accounts tables, and perform your checks on that.