Methodologies for Managing Users and Access?

Question

This is something I'm having a hard time getting my head around. I think I might be making it more complicated than it is.

What I'm trying to do is develop a method to store users in a database with varying levels of access, throughout different applications. I know this has been done before, but I don't know where to find how they did it.

Here is an example of what I need to accomplish:

UserA - Access to App1, App3, App4 & can add new users to App3, but not 4 or 1. 
UserB - Access to App2 only with ReadOnly access.
UserC - Access to App1 & App4 and is able to access Admin settings of both apps. 

In the past I've just used user groups. However, I'm reaching a phase where I need a bit more control over each individual user's access to certain parts of the different applications. I wish this were as cut and dry as being able to give a user a role and let each role inherit from the last.

Now, this is what I need to accomplish. But, I don't know any methods of doing this. I could easily just design something that works, but I know this has been done and I know this has been studied and I know this problem has been solved by much better minds than my own.

This is for a web application and using sql server 2008. I don't need to store passwords (LDAP) and the information I need to store is actually very limited. Basically just username and access.

Answer 1

This is the setup in the application I maintain:

• active directory has groups which can access the application with users in each group
• fine grained permissions are held in tables linked to groups so that all IT has to do is to add a user to an Active directory group
• a buggy service that synchronizes Active Directory users to a table of application users

you need

• Users table: with name, userId (numeric), enabled (numeric) and whatever users sign on with. (Usually a text string like your initials)
• Role Table: Active Directory groups with numeric ID and text identifier
• Action Table: group your permissions by type: access to Applications, access to Administrative, can grant permissions. create a table for each with numeric identifier and descriptive string. In the application I maintain top level permissions include ACCESS the master application, GRANT permissions, READ, UPDATE, ACCESS a linked sub application

• so a query to see what groups have top level permissions looks like this

  SELECT cr.NAME "AD_Group", CR.ID GROUP_ROLE_ID, DECODE (a.NAME, 'APPROVE', 'Approve Organizations', 'CONTACTS', 'Contact information', 'GRANT', 'Grant permissions', a.NAME) "Stage/Permission", gp.role_id, gp.action_id, FROM action a, general_permission gp, role cr WHERE a.ID = gp.action_id AND gp.role_id = cr.ID

When your user logs on you query LDAP for what groups that they are a member of. Then query the database for what permissions the groups have. Make it easy on yourself and make permissions additive.