A: 

If you put your roles in a SQL database, lookups will perform substantially as you describe. I can help you with the database structure, if you're interested.

Robert Harvey
The SQL database would not really remove the complexity of the Tree traversal, on the contrary in my opinion trees in SQL are quite a pain.The depth of the roles is not really limited.
Fionn
The 'nested set' model is a way to do very fast selection of subtrees in SQL.
ChrisW
There is a good change that this will run on a Database System like Bigtable - there are no joins just plain tables.And even if nested set is fast, i guess it will not scale as very good since it is far form a standard table lookup.
Fionn
No, the nested set method uses standard SQL. It uses a schema that makes insertions slow but selects very quick: it doesn't even require a join, let alone a recursive join.
ChrisW
A: 

You need to reverse your pointers.

"Harry" is a member of "Site2 Admins" which has "Administrators" access to "Site2", so he can thus "Delete," "Write" and "Read that content.

Why "Administration" should be a common thing between "Harry" and "Joe" I'm not clear. Harry is an administrator on one site, but just a user on another, and Joe vice versa.

Curt Sampson
There is no rule that says an administrator may do anything, it could just have been named SomeUsers role, if someone may read, write or whatever is entirely dependent whether a role containing that user is added to the property.
Fionn
+1  A: 

Have you measured this and determined that this traversal is a performance bottleneck?

I've never seen a system with so many roles / levels that the cost of traversing this kind of structure would become an issue. And if the tree really is that large, I'd be more concerned that administrators would have difficulty in understanding who is authorized to do what.

Regarding scalability, I would typically use the ASP.NET cache to cache the complete tree that maps between resources and roles, with a suitable cache timeout. And separately cache the mapping from Users to Roles (e.g. in Session or with a user-specific key in the ASP.NET cache).

Accessing the information from the cache will typically be blindingly fast compared with going to the database each time.

Joe