multi level hierarchy access control table

Question

i am having some trouble implementing acl. i have the following already

table: users
id

table: roles
id
role

table: user_roles
id
user_id
role_id

table: permissions
id
permissions

table: role_permissions
id
role_id
permission_id

table: user_permissions
id
user_id
permission_id

when a user logs, roles are fetched and based on those roles, permissions are fetched then stored on a permissions array for later use.

while the above works fine, the addition of locations and stores got me confused.

table: locations
id
location

table: stores
id
store

so, basically a user can have a general role assigned:
global admin, global personnel
can also become location admin or personnel
then can also become store admin or personnel

store are located in a location
locations are managed by global office

now, how do i integrate locations and stores with the existing access control?

Answer 1

You can connect Store and User. User and all the permissions related to a particular store can be stored here.

There is a Ternary relationship between, Store, User and Permission Now, Store and User have a many to many relation. In the association table, Store_User, you can link the Permission table. This will make sure that each store has a specific set of permission for each user.

This should explain more: