We've got a global system that we are attempting to solve a permissions issue around.  Currently, our system serves a number of different applications out to our clients and each client has their own list of user types and users for our system.  However, all of these things can be independent of each other.  Such that Client A can have access to Application A and Application B.  Client A might also have give different User Types, all with different Users.  Each 'level' of usage would add a new level of complexity to how we give permissions out to everything.For example: I might have three permissions, called Grape, Purple, and Car.  At the global level (meaning all applications, clients, user types, and users), I deny the permission on Grape, but allow permission for Purple and Car.  That means that no application, no client, no user type, and no user can access Grape, but they can access Purple and Car.  So now, we have two applications, named Fruit and Auto.  Currently, both Fruit and Auto would look down to the global permissions created for Grape, Purple, and Car and use those.  So, this is how things would look:FruitGrape: DeniedPurple: GrantedCar: GrantedAutoGrape: DeniedPurple: GrantedCar: GrantedBut, for the applications, this might not make sense.  So, I would create an application specific permission to grant permissions for Grape and deny permissions for Car for the application of Fruit.  So now, the permissions would look like this.FruitGrape: Granted (a)Purple: GrantedCar: Denied (a)AutoGrape: DeniedPurple: GrantedCar: GrantedWhile I still have the global permissions telling Auto what permissions to use, I have specific permissions telling Fruit that it needs to grant and deny different permissions there.  Now, we'd add another level of complexity and add in a client.  Let's say I have a client called Fox.  Fox uses both applications Fruit and Car.  Right now, without adding anything else, the permissions to the three objects (Grape, Purple, and Car) come from the application wide permission level.  But, maybe Fox wants things different.  So, instead of having Purple granted on application Fruit, it denies that permission specifically.  In terms of data, that would be a specific record you would add just for client Fox that overrides the permissions of Purple to be denied, regardless of what the application or global permissions said for Purple.  So, now you have something that looks like this.FruitFoxGrape: Granted (a)Purple: Denied (c)Car: Denied (a)AutoFoxGrape: DeniedPurple: GrantedCar: GrantedRemember, too, that a lot of this can be pretty abstracted.  You could have clients that only use one application, three applications, or every application you have in your system.  And for those clients, you might have very specific user lists that can only use what the client can use, so they'd be inheriting permissions down from the client unless specifically overwritten.Obviously, this can get increasingly complex when you add in a level for the user type and then for the user.  Keeping track of it all can be very cumbersome, but it's something we have to account for based on our system's model.  So, we came up with a scoring solution for permissions.We would query the database and tables and pass in the current application, client, user type, and user to the database for whatever we have current at the time.  The database would then use a scoring solution to determine if we can see or not see a particular permission.  Application would be a score of 1, client would be a score of 2, user type would be 4, user would be 8, ect.  The more defined objects you put in, the higher the maximum score can get.  This gives us a very clear definition of what permissions are active for a given set of objects.Our real problem now is performance.  We now have 5 levels of objects in our system and might be adding more, if the design of the system requires it.  However, querying all of these levels is a serious performance hit on SQL.  And each object will just make the problem exponentially worse.  My question is this: how can I achieve the same type of permission inheritance system here, and give the ability to add new levels when needed, without exponentially increasing the performance hit.  There has to be other companies and applications that have this same problem and have better solutions to it then we do.
Designing a better performing total permissions setup for multiple permission levels
design patterns;performance;permissions