CODEWITHSUNDEEP
phpmysqlpermissionshierarchy
Kris T.
Kris T.

Reputation: 11

Access control on different tables with parent/child relationships

I'm trying to setup access control in an application I'm writing. I've seen approaches like ACLs and Roles/Permission systems (like those described here and many others, as well as the Nested Set Model) but I'm not sure if I can accomplish my goal using these approaches.

What I'm trying to do is find a way to allow access to Children of an object. The catch is that the Children are stored in different tables and represent different entities. An example:

City > Streets (city_id) > Houses (street_id) > People (house_id)

In this scenario, each one of these objects is stored in a different table in the database, and points to its parent with the parenthesized fields. What I want to do is be able to give a user permissions on just the "City" level, (i.e. Grant permissions for City #10) which will grant access to all children on the various tables underneath, whether its a street, house, or person. (So, I can ask something like "Does User A have permission on House #16?" and the system can determine if House #16 is a child/grandchild, etc. of the City #10 object.)

Now, I'm aware this can be done by using multiple joins, etc. in the database, but that feels like a "brute force" approach and would not be very DRY or reusable. What I'm hoping to find is a more efficient/less naive way to accomplish this if possible. Thanks!

Upvotes: 0

Views: 324

Answers (1)

Kris T.
Kris T.

Reputation: 11

Found an answer: Models for Hierarchical Data in SQL and PHP.

That video talks about "closure tables" which is exactly what I was looking for. Using the "depth" and "ancestor" fields in the closure table, I can pinpoint from any level of the hierarchy (People, Houses, Streets) to which City the data belongs.

I created a "securables" table as well, that has one row for every row from the people, houses, and streets tables. Then my closure table (and some configuration to tell each child table what its depth should be) relates them together into a tree. Then, using that configured depth I can grab the root of the City's tree at any time from any node within- in a single query! Presto!

Upvotes: 1

Related Questions