Designing a multiple level user permission system

Question

I am working on a site that has a database schema like a tree. From top to bottom I have:

• One Company can have many
• Locations that can have many
• Fleets that can have many
• Vehicles

The user permission system has to be flexible in that someone can gain or have restricted access at any level of the tree. For example, a person that manages location x will by default have full access to all fleets below location x and all vehicles below location x. But I should also be able to restrict the user's access to any node below location x.

What would be the proper way to develop a schema for a permission system such as this? Would it be realistic to store each node a user has access to even though it would be kind of redundant? The goal is for me to be able to easily run queries for example to get all locations a user has access to.

Solution

You might find a number of threads on SO interesting. Among them:

Implementing permissions based on reputation

Database schema for ACL

Comments

[Layke]

I don't have the energy to write a full answer, so I will just comment instead and let someone else fill in the blanks. Everything you have just described can be accomplished by using inheritance through an ACL. Access Control List. You could set up Locations as Resources in your ACL and so could your Fleets be Resources, you allow access to users that way. Alternatively, you could use a Nested Set Model hierarchy and ignore the ACL altogether and just give users access to nodes that fall between the lFT and RGT values in the tree. ie, SELECT * from locations where lft BETWEEN 4 and 54.