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