6

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.

developer1234
  • 237
  • 2
  • 13
  • 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 '12 at 19:16

1 Answers1

6

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.

kevinskio
  • 387
  • 3
  • 11
  • 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. –  Aug 27 '12 at 20:46