Role Based Access Control in a Tree System

Question

I'm trying to figure out what the best way of creating a database for a user permission system that is structured as a tree system.

(for illustrative purposes) I have three levels of control: 1Property Group, 2Property, 3Building. A property group has several properties, a property has several buildings.

I want to set up administration on each level.

a PropGroupAdmin can view and edit all the properties inside the set property group, and the buildings inside.

a PropAdmin can only view the set property and the buildings within

and a BldngAdmin can only view the set building

How I envision it working is that user Bobby is a PropGroupAdmin with a propertygroup id of 102. That would translate into a series of specific privileges that I could read from a view.

The question I have is how can I set up the GroupPrivileges table to be able to inherit (if that's the right word) the hierarchy? Essentially allowing me to create a view that I could just read to see if a user has the right permissions to perform a task.

I created a little chart of how I imagine it will work.

I know This won't work this way, I'm just trying to figure out hot to make changes to get a similar result.

Answer 1

why reinventing the wheel? Why don't you implement the Role Based Access Control standard? http://csrc.nist.gov/groups/SNS/rbac/

There are implementations for all databases.

http://www.tonymarston.net/php-mysql/role-based-access-control.html

Answer 2

If this is not too late, have a look at this:

http://vita.codeplex.com/wikipage?title=guide_authorization