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.

  • 1
    I don't know enough about your business model, but I wouldn't get away from groups even if it meant several groups currently only had one member. People come and go. It's easier to add a new user and assign to the same groups as the person they replace than trying to find all of a specific user's settings.
    – JeffO
    Aug 27, 2012 at 19:16

1 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.

  • 2
    Good answer. I don't believe the OP needs the Active Directory aspects, but including AD in your answer doesn't change the fundamentals of what needs to be done.
    – user53019
    Aug 27, 2012 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.