I spent my afternoon yesterday justifying a role based security system for the application I'm heading up. Why would I have to do such a thing? Well, I had an encounter with one of the most fearsome beasts of the business world. That beast is called:
The DBA who thinks they are a programmer
Now, let me say that I have nothing against the typical DBA. In fact, I find the involvement of a professional DBA on a project to be a great help when it comes to normalization exercises, complex stored procedures, and triggers. Being that I've worn both the DBA and programmer hats I am able to do these things myself, but frankly they aren't my favorite things in the world to spend time on so I'd just as soon pass these duties off to someone who specializes in it.
So why is this non-typical DBA such a fearsome beast? It is simply because they constantly attempt to involve themselves in the logic and structure of the programming needs rather then taking the requirements and building the database to best meet those requirements. Case in point with the meeting yesterday. The application we are building is going to have at minimum eight different security groups that users will need to be placed in. Each of these groups is to have a mix and match of rights in the system. In addition, group members who misbehave in certain areas of the software need to be able to have their rights to those areas revoked without losing access to the other areas they do have.
A situation such as this is a perfect fit for role based security. I proposed that we create security groups, assign roles to the groups, and assign users to those groups. (We don't feel the need to go all the way down to assigning roles to individual users for various reasons I'm not going to go into.) I mocked up the table structure we'd need to support this in Visio and approached the DBA with the design and met a hard line of resistance. The conversation went something like this:
DBA: You're adding like 6 tables to the database, that adds too much complexity.
Me: That is the minimum number of tables it takes to properly implement role based security.
DBA: You should use an access level system instead.
Me: Huh?
DBA: There are 8 roles, so give each user an access level from 1 to 8 and give them increasing priviliges based on that number. Then you can just do a less than or equal query on the security level to grab all the roles.
Me: You're assuming that the roles in this system are sequential, they are not. In addition, what do we do if we have to insert a role?
DBA: Remember back in the days of old BASIC, you spaced your lines by 10 so that you could insert lines later. Make the access levels 10, 20, 30, ... 80
Me: That still assumes that roles are sequential, they aren't.
DBA: You can't tell me that you can't order those roles in a logical manner.
Me: We don't even have a complete spec yet so we don't know how many roles we will have. You access level idea just won't work, think about it this way:
We have roles A B C D E F. In your world we would have access level 1 with roles A B C Access level 10 has A B C D E F. Now lets say we want to add a level that has A B C E, where would be put it?
DBA: You'd put access level 5 in with roles A B C E.
Me: Right, now lets say we suddenly need a role with A B C F, where would we put that? If we put it at level 7 then your less than or equal query would give level 7 the E role which we didn't intend. If we put it at level 3 then level 5 would get the F role that we didn't intend.
DBA: I disagree. (Actually he went into this big discussion that made no logical sense, but I'm not going to post that here)
So in the end I had to develop a presentation and call a meeting with the business managers. I had to justify using role based security, the security style that is probably most pervasive in the software world today because of its flexibility. Having to do this wasted 4 hours of my time, and another 8 man hours of time from the people forced to attend a meeting so that I could overrule the DBA.
All of this could have been avoided if the DBA had done their job of accepting the logic that the development team had come up with and building the database accordingly instead of digging their heels in resisting a commonly used pattern that since the days of Windows NT has proven to be best practice.
DBAs - Be DBAs, optimize the crap out of that database, make sure my sql is good, make sure the relations are in place and enforced. Don't try to design my application though, it's not your speciality and in the case above you got made to look like a bad team player and pretty silly on a logical level. I certainly don't tell you how to design your triggers.