The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Generic Permission and Role tables...

I'm considering creating generic permissions and role tables in my database for use with all of my in-house applications.  These tables would relate to a users table already in existence.  I would have a "Application" column in both the permission and role tables that would define which application uses that particular permission or role.  This would also allow me to add additional permissions and roles at runtime.

Does anyone see any major problems with this setup?  I'm in my research phase right now and I am trying to see all the possible risks.

Thanks.
zigzag Send private email
Monday, April 25, 2005
 
 
We've done it before, but it ain't fun. I'd avoid it unless absolutely necessary.

We have tables for:

- users

- roles (groups)

- entity (screen, table, query, report, lookuplist, etc)

- action (insert, update, delete, select (view), run (report), administer (ACLs/permissions), etc.)

NOTE: Actions may (or may not) "cascade" -- think "inherited permissions" on the Windows file system -- e.g. viewing a customer "with cascade" allows you to view a customer's child records (contacts, contracts, locations, etc.)

- ACL -- access control list based on entity ID -- e.g., Fred has X permissions on Customer 101, but Y permissions on Customer 202. For example: Fred has "full control" on customer 101, but can't change prices on customer 202.

It works largely like, and was modeled after, the Windows file system permissions, except with database and application objects rather than files/folders.

This was (a) not fun to setup, it was added in after the fact. I imagine, if you can start from a clean slate, it would be a hell-of-a-lot easier to build in from the ground up and (b)in my experience, clients typically ask for such control as a "must have" requirement, but when the rubber hits the road, they grant "full control" to the "Everyone" role. Administering such a fine grained access security model is simply something they don't want to/don't have time to do. Funny how a "must have" turns into a "we don't use that, it's too *hard*"
Sgt.Sausage
Monday, April 25, 2005
 
 
Echo prior poster.  A pain to implement, and real day-to-day use is nonexistent.  I'd like to whack some functionality in a future release and leave this as "checkbox" functionality, but that's still under evaluation.
D. Lambert Send private email
Monday, April 25, 2005
 
 
there is an MS Application block for this....
Sassy Send private email
Monday, April 25, 2005
 
 
I don't require an implementation that fine grained.  I'm content to define basic permissions as I create the application and then group those into roles for assignment.

I'm mainly just looking for a better way to centralize and store permissions for all my apps.  After doing some reading I might just use our Active Directory structure, but if I go that route I will need to create some objects to interface with it.
zigzag Send private email
Monday, April 25, 2005
 
 
Well, Zigzag, I'm happy you don't WANT anything "that fine grained".  But once it exists it can be difficult to keep your customer from playing with it, and making it as fine grained as they want.

And then, once it's deployed, it's either not used, or becomes a maintenance nightmare.

As long as you stick with the Unix model, and work like mad to keep it simple, you may be okay.

The worst use of this is where a customer tries to replace good operational procedures ("this is what you should do") with draconian permission roles ("You're a lowly employee, the system shall not ALLOW you to do that.  He's a Supervisor, he gets to see THIS.").  This approach quickly degenerates into role-based wars -- every iteration of which you as the developer get to implement the newest plan.
AllanL5
Monday, April 25, 2005
 
 
Oh, and I just saw that "all of my in-house applications".  Do you REALLY want to couple ALL of your in-house applications together with ONE set of user names, and ONE set of roles?
AllanL5
Monday, April 25, 2005
 
 
==>Do you REALLY want to couple ALL of your in-house applications together with ONE set of user names, and ONE set of roles?

A resounding yes on that. They're all "in-house", so it's the same bunch-o-folks. To do otherwise would be administrative lunacy. You (usually) don't setup multiple active directories for one in-house network do you? Why? Centralized control. "The One True Source" for users, roles, permissions, etc. It would be a nightmare if you didn't have ONE set. Think about it. Hire a new employee, and have to update (say) 15 different security/permissions repositories?

I think ONE *is* the way to go.
Sgt.Sausage
Monday, April 25, 2005
 
 
It's not impossible. Every open source CRM package implements it. As pointed out before, it's time consuming and worst--it may not scale as the usage model expands to web sites with tons of hits. ACL libraries are often forced to do double duty (forcing programmers to make them even more generic): Ex. You might want to use the same library/tables to track segmentation in marketing research. So group == segments. It's well worth developing one though, but creating one that survives each framework jump (from ASP to ASP.NET, or C, to C++ to Java) is tough.
Li-fan Chen Send private email
Monday, April 25, 2005
 
 
The most important issues with ACL are 1) migration or 2) merging/segmenting ACL groups from distinct application systems. It gets much more complicated than that but this is what I would use all the time.

I can't imagine why this is something every web shop should write over and over, starting ASP.NET 2.0/Windows Server 2003 people will look to Microsoft to provide such ACL framework: one for AD, and one non-AD (backs to RDBMS/XML?).
Li-fan Chen Send private email
Monday, April 25, 2005
 
 
MicroISV Guy Send private email
Tuesday, April 26, 2005
 
 
You are so right about users requesting an access control system that is so fine grained that they end up not using it. I first designed my own solution 20 years ago in COBOL, and I have since rewritten it in two other languages. It basically involves four database tables:

(1) Users
(2) Roles (or User Groups). A User must be a member of a Role.
(3) Tasks (or Transactions). These are activities which can be selected on a menu screen for which a program/procedure has been written. Note that in my design "Create Customer", "View Customer", "Update Customer" and "Delete Customer" are separate tasks. I do NOT have a single "Maintain Customer" task which has separate modes for create, view, update and delete.
(4) Permissions. This is a link between Role and Task. If a record exists here then Users of that Role can access that Task. If a record does not exist then that combination of Role and Task is not valid.

Effective without being too complicated. Read about it at http://www.tonymarston.net/php-mysql/role-based-access-control.html
Tony Marston Send private email
Tuesday, April 26, 2005
 
 
Tony,

That seems like a good model.  I have to develop a similar permission system for a project but it has one additional crinkle: if the user creates a record (say customer) than he/she may have permission to update/delete it but may have different permissions on records they haven't created themselves.  Do handle that sort of thing?

I once developed a big Windows-style ACL system for a project and it ended up being very complicated -- very few people used it.  On a another project, I used a 4-level model but for that project everyone always just used "administrator".  I can't even be sure that the other permission levels work anymore!
Almost Anonymous Send private email
Tuesday, April 26, 2005
 
 
That design does not go down to the record level. It gives permission to access tasks, not data.

I once worked on a project that required that level of permission control, and this involved adding an extra "owner" field to each record. The system would not allow a user to "see" any record that did not have a compatible value in the "owner" field.

This was the only time that sort of requirement materialised in 20 years of software development, so it's not all that common. However, a well structured design should allow such custom features to be added in relatively easily.
Tony Marston Send private email
Wednesday, April 27, 2005
 
 
My objection to the "Single set of users and roles" used by ALL in-house programs was the coupling involved.

Conceivably, each program could have its own set of users and its own set of roles appropriate for that program.

Once you mandate that 'Single Source' approach, now every application you write in-house MUST have the same set of users, and the same set of roles.  This can be pretty limiting.  And if anyone ever wants to change one of the roles, now ALL your in-house applications may need modification to support the new schema.  Coupling.  Leads to maintenance nightmares.

For Windows log-ins, and Windows roles, this makes sense, so a single-source for user logins makes sense.  Also, Windows is not going to be changing what the roles are.

For an arbitrary set of in-house applications, who knows what set of roles you might want to define?  Or how changeable they are?
AllanL5
Wednesday, April 27, 2005
 
 
==>For an arbitrary set of in-house applications, who knows what set of roles you might want to define?  Or how changeable they are?

At my client locations, roles are generally based on job positions. Those functions those roles perform are largely the same across applications, and rarely change for a given job position.

Whan Andy Accountant gets promoted from clerk to VP of Purchasing, would you rather update Andy's roles once, in a centralized repository, or 37 times across the entire organization's application suite, likely with 37 different ways to do it?

I guess we'll just disagree on this one.
Sgt.Sausage
Wednesday, April 27, 2005
 
 
Tony the past ACL libraries I have the (dubious?) pleasure of implementing all are along the line of what you detailed above, the only difference is I would add a domain table. A domain for each application. They work with the Users table so that there can be two Tonys or Li-fans in the users table. Not ideal, but in throw away applications it helps.
Li-fan Chen Send private email
Wednesday, April 27, 2005
 
 
(1) Have multiple Tonys or Li-fans in the User table is not an issue as each user has a unique user_id which is separate from the user_name. I do not need a separate domain to handle different applications (what I would call a subsystem) as a particular user may have access to any number of components in any number of subsystems within the application. Just as each user has a unique id each task also has a unique id, so there is no possibility of different tasks in different subsystems having the same id.

(2) Each program does NOT have its own set of users and its own set of roles. There is one set of users, one set of roles, and one set of tasks (programs). A user belongs to one role. A role can have any number of users. The permissions table identifies valid combinations of role and task. There are no limitations as to what roles can be linked with what tasks - that is entirely down to the system administrator who can create whatever roles he wishes with permission to access whatever combination of tasks he wishes.

Individual tasks (programs) do not need to verify anything on any user, role or permissions table. This is done by the control program BEFORE the individual task is activated. If the correct permissions are not in place, then the control program will not allow the task to be activated. Because the control program is also responsible for constructing the menu buttons it can use the permissions system to exclude those buttons for which the current user does not have access. Simple, yet effective.
Tony Marston Send private email
Thursday, April 28, 2005
 
 
Tony,

Your system seems to fit my needs the best.  Using predefined tasks within the application and assigning users to roles that can do those tasks. 

I am wondering now if I should just use active directory groups to accomplish all of this?  Our entire company is under AD, so the infrastructure is in place.  Only members of our company logged into the domain can use our programs.  This would save me from having to write this permission storage structure. 

Does anyone have any input as to why using AD groups would be a bad idea?
zigzag Send private email
Thursday, April 28, 2005
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz