Managing user permissions with a hierarchy

Question

I am building a system where organizations will enter information pertaining to their business. Reporting needs to be available to users on multiple levels where some users will only have access to their organization's statistics and higher level users will have access to both individual organization statistics and well as aggregate statistics for entities at a higher level (see my diagram which illustraes the hierarchy).

• There will be one or more organizations within a municipality.
• There will be one or more municipalities in a county
• There will be one or more counties in a state
• There will be one or more states
• Organizations, municipalities, counties, and states can be added at any time
• When an organization, municipality, county, is added to the system a user who already has permission to view that state should automatically be able to view reports for the new organization/municipality/county without an administrator needing to explicitly grant them permission. The same should apply to users who have permission to view reports at a municipal and county level whenever a new entity below them in the hierarchy is added to the system.

Some examples:

User 1: Can only view reports for organization #1

User 2: Can view reports for all organizations under Municipality #2

User 3: Can view reports for all organizations under Municipalities #1 & #2

User 4: Can view reports for all organizations under County #3

User 5: Can view reports for all counties under State #3

My question is how do I organize this? I am unsure of the best way to assign permissions to reports without assigning permission to individual organizations. That clearly is not practical.

I've seen a few questions here that deal with ACL but they don't seem to apply to this. If it does, an explanation of how it would relate to ACL would be a satisfactory answer as well.

Answer 1

I would suggest creating a series of user groups in your database, each of which has one or more user account levels within it, then assigning an integer as a hierarchical value to the group, then doing the same for the individual account levels within the group, something like this (This is a relational structure, use InnoDB):

table: account_groups (Broader account groupings)
Fields:
-id_key - primary key, auto number
-group - unique index
-parent - index, foreign key=account_groups.group (this allows you to create group trees, so you can specify that a county group belongs to a state, and a municipality belongs to a county group, etc.)
-group_hierarchy - integer (0 is highest permission group, each subsequent one step lower)

table: account_levels (Account levels within a group)
Fields:
-id_key - primary key, auto number
-account_level - unique index
-group - index, foreign key=account_groups.group
-account_heirarchy - integer (same as other table but denotes heirarchy within the group

table: user_accounts (Individual user accounts)
Fields:
-id_key - primary key, auto number
-account_id - unique index, user account name
-account_level - index, foreign key=account_levels.account_level

table: user_groups (denotes which tree(s) the user has access to)
Fields:
-id_key - primary key, auto number
-account_id - index, foreign key=user_accounts.account_id
-group - index, foreign key=account_groups.group

And then for the permissions:

table: permissions (directory of permissions that could be applied)
Fields:
-id_key - primary key, auto number
-permission - unique index, permission identifier
-other stuff you need associated with the individual permissions, based on how you want them to hook into your program

table: permissions_group_permissions (permissions applied at group level)
Fields:
-id_key - primary key, auto number
-group - index, foreign key=account_groups.group
-permission - index, foreign key= permissions.permission

table: permissions_account_permissions (permissions applied at account level)
Fields:
-id_key - primary key, auto number
-account_type - index, foreign key=account_levels.account_level
-permission - index, foreign key=permissions.permission

table: permissions_individual_permissions (permissions applied to individual accounts, if neccessary)
Fields:
-id_key - primary key, auto number
-account_id - index, foreign key=user_accounts.account_id
-permission - index, foreign key=permissions.permission
-allow_or_deny - boolean (TRUE means permission is granted, FALSE means permission if revoked. This allows you to fine tune individual accounts, either granting custom elevated permissions, or revoking individual permissions for troublesome accounts without demoting them from the group. This can be useful in some special circumstances)
-expiration - timestamp (allows you to set expiration dates for permissions, like if you want to temporarily suspend a specific action. Programmatically set default value of 00/00/00 00:00:00 as indefinite. You can do this at the account and group levels too by adding this field to those tables.)

You can then use php to iterate through the permissions for the individual account by first getting the group associated with the account level, making an array of each subsequent group in the hierarchal order, and then iterating through the hierarchal order for the current group (add as multidimensional array to group array) from the current account level within the group to the last existing account level within the group. Next you would grab all of the account level for each subsequent group, and finally fetch all associated permissions for each account level that has been added to the array. If you implement the individual user permissions, you would then need to append your permission array with the individually applied permissions, and lastly remove any permissions that from your array that have their allow_or_deny field set to FALSE. If the user needs to have access to multiple trees, you add a record to the account_groups table matching their account id, denoting what the highest level of the tree they have access to is, and then iterate through all subsequent groups in the tree. To grant all applicable permissions to the account, grab all of the group associations for the account_id from user_groups, and then run the previously described process for each tree. If they only have access to one tree, you don't even need to use the user_groups table.

an example of how the structure fits your model:
group: USA, hierarchy = 0
group: California, parent-> USA, hierarchy = 1
group: Los Angeles, parent->California, hierarchy = 2
group: Texas, parent->USA, hierarchy = 1
group: Dallas, parent->Texas, hierarchy = 2

Members of group USA can access everything. Members of California can access all subsequent groups in the hierarchy for california, but not groups for Texas, even though they have the same hierarchical value (because they are different parental branches)

account levels:
admin, hierarchy=0
manager, hierarchy=1
analyst, hierarchy=2
staff member, hierarchy=3

Each account level has all of the permissions for each subsequent account level.

user accounts:
Bob, manager (likes to spam junk email to everyone)

You can still revoke the emailing permission for Bob by adding the email permission to permissions_individual_permissions and setting the allow_or_deny value to FALSE. This lets you stop Bob from spamming without demoting him from management.

example PHP array:
$account=array(
    groups=>array(), //Step 1: array_push each group the account is a member of here. Repeat for each tree from user_groups.
    account_levels=>array(), //Step 2: loop through $account[groups], array_push each level here
    permissions=>array(), //Step 3: loop through $account[account_levels], array_push each permission here. Then do the same for individual permissions applied to the account
    restrictions=>array() //Step 4: loop through individual permissions where allow_or_deny=FALSE, array_push here (do the same for group and account level if you implemented restrictions for those tables as well). Tell your program to ignore permissions from this array, even if the account would otherwise have them.
);

Answer 2

I'm thinking that one way is that you assing a unique permission id to each entity (oranisation, municipality, county, state)

So your tables should have a new column permission_id with the following form: Organisation 1 will have permission_id O1 Organisation 2 will have permission id O2

Municipality 1 will have permission id M1 Municipality 2 will have permission id M2

and so on.

Then, you can make a permissions table (id, id_user, permissions) where the permissions column will be something like O1 - permisssion only for Organisation1 M1 - permission for all organisations in Municipality 1 M1M2 - permission for all organisations in Municipalities 1 and 2

S1 - permission for state 1

This is just my opinion. As long as you know that a user has access to a municipality, he should have access to everything under that municipality. Some php function that can get the route from the current entity can match the user permission.

example.

You are on a municipality page. M2. With a user that has permission to S2 Your funcction will get as argument the municipality id and the function will create a route: M2 , C3, S1 . You compare then S2 with S1 and permission is denied. This way, the complexity is O(n) where n is the number of entities (orgs, municipalities, counties and states, that is 4).