Dear CIOs: I know why your environment is unstable.
The number oneÂ cause of instability in most database environments is SQL Server permissions.Â And specifically, the problem is that developers, QA engineers, application teams, managers, VPs, etc. have more access to systems than they should.
Iâ€™ve been a lead DBA in large shops for twenty years,Â and Iâ€™ve seen this more times than I can count.Â Let me walk you through a typical lockdown.
For a very short executive summary,Â skip to the bottom of this article.
1. The Lockdown Email
Often, a manager will get tired of all of the extra people in the database causing fires, and so he initiates a lockdown.Â He tells the database team: “Everyone who doesnâ€™t need to be in the database needs to go. And everyone else should have only what they need to do their job.”
The DBAs don’t know what permissions everyone needs, so they send out an email to explain what’s up. “It’s time for a lockdown on database access. PleaseÂ send what permissions youÂ need to do yourÂ job.”
This is the beginning of the end.Â It’sÂ this first step that causes the entire project to fail. Â We’ll get to why later, but for now letâ€™s move on to the next step: the knee-jerk reaction.
2. The Knee-jerk Reaction
Now, everyone whoÂ got the email replies that they do indeed have the access they need to do their jobs. Â Steve – who does nothing but write reports – definitely needs sysadmin*. And Carol, whoÂ does quality assurance,Â totallyÂ needs to run, alter, create, and delete every job on the system…even though she only manages twoÂ jobs.
The rest of the staff are pretty much the same. Â Nobody wants the DBAs to trim their permissions.
*Sysadmin gives a user total control of the system, including the ability to dropÂ entire databases and grant otherÂ users’ permissions.
3. The Heavy Hand
Next, the DBAs take a deep breath and explain that the lockdownÂ is going to happen;Â everyone has to start being realistic about whatÂ permissions they actually need, and not just what they want.
It doesnâ€™t matter how many meetings you hold, or how polite or rude the DBAs are about it, this is happening because the environment is just too unstable.
Now the rest of this lockdown will continue inÂ one of twoÂ ways.
4. The Pushback
Option 1: EveryoneÂ ignores the lockdown and hopes the problem will go away.
Option 2:Â The users allÂ whine to their managers and higher ups that their permissions are being taken away. “We wonâ€™t be able to do ourÂ jobs! The whole place will come to a screeching halt! Customers wonâ€™t get what they need because weÂ donâ€™t have the permissions to service them properly!” The overall message isÂ that they needÂ to be in production, and they need to have full access. Because, of course, they never know what will come up and they’ll need to react at a momentâ€™s notice.
5. The Implementation
Option 1: The managers, vice presidents, and maybe even the CIO gets scared and scraps the whole project. They just canâ€™t risk compromising production support.
Option 2: The DBAs are able to convince those in chargeÂ that the users willÂ have the permissions they need and all will be well.Â The lockdown continues as scheduled.
6. The First Production Issue
If we made it all the way through to this point (following option 2 above), we stillÂ have the first post-lockdown production issue. Â That will test theÂ newly locked-down environment.
When the issue hits,Â some developer encounters aÂ minor issue with his permissions. This is the perfect opportunity to completely blow the situation up, so he immediately runs to the VP and screams that customers are suffering! That the entire company is at stake!
The VP then freaks out, because this developer has been there for years and heâ€™s never let themÂ down before. Heâ€™s got to be telling the truth (and not exaggerating at all).Â So, the VP orders the DBAs to reinstate the developer’s sysadmin access.
The rest of the security lockdown soon crumbles, because once one exception is made, the rest will soon follow.
I’ve seen this whole thing play outÂ many, many times.
The Lockdown Analysis: What went wrong?
Thatâ€™s a cautionary tale if Iâ€™ve ever seen one. Â Now letâ€™s take a minute to talk about what went wrong.
Remember when I said that the DBA’s announcement email in step 1Â was the big mistake?Â Hereâ€™s why:Â The DBAs and their manager did not properly inform the vice presidentsÂ and CIO.Â I have gone both routes, and it almost always fails when I do it as outlined above. It always succeeds when I insert this following step.
Step 1A: The Upper BrassÂ Email
Before sending out the email or letting anyone else know theyâ€™re thinking of trimming permissions, the DBAÂ should meet – if possible, all at once – with all the managers, the VPs, and the CIO (you). At that meeting, the DBA should tell everyone what theyâ€™re planning to do and why.Â Then they must explainÂ that when they send out the email, there will be plenty of pushback.
This meeting’s talk should look something like this:
Developers donâ€™t want to lose the access theyâ€™ve had for years, even though they never use most of it.Â So theyâ€™re going to come up with all kinds of knee-jerk reactions.Â Theyâ€™re going to come to you guys and say that customers will suffer, and that deadlines are going to slip, and the quality of the company will generally be compromised.Â
Itâ€™s okay.Â None of that will happen.Â
All of that posturing is really just a tantrum, because people donâ€™t want things to change.Â And we donâ€™t give in to tantrums.Â
The success of this is going to rely on how good the information they give us is.Â If they tell us all the permissions they need, then weâ€™ll probably get it right the first time.Â If they don’t, then there may be a couple of minor security hiccups that cause us to go back and give them another permission.Â It only takes a couple minutes, and they can wait that long.Â
Theyâ€™ll tell you that the issue is so important that it canâ€™t possibly wait that long. Â “I have to handle itÂ right now!” they’ll say. Â But think about that: What if anÂ issue comesÂ in while a dev is in the bathroom, or at lunch? Â It wouldÂ have to wait for at least a few minutes, and probably a lot longer.Â And this is no different.Â
I just wanted you to know that weâ€™re not only trimming permissions, but that the guys are going to come to you with doom and gloom and pleas for emergency permissions. Â And you need to know that now so you donâ€™t fall for it.Â Itâ€™s nothing but a tantrum and this needs to happen. Â
Right now weâ€™ve got threeÂ major production issues a week, and we can trace them all back to someone making a mistake with something they shouldnâ€™t even be able to do. These permissions are really important to them, but you know what?Â In a couple weeks they wonâ€™t even care anymore.Â Theyâ€™ll be able to do anything they need to, and they wonâ€™t know the difference.Â
The only real difference is that after the lockdown, if they do something they shouldnâ€™t,Â whether on purpose or by mistake, the system will stop them.
That is what your DBAs should be telling you.Â And that is what you should tell yourself if they donâ€™t.
The Dangers of an “Unlocked” Shop
We alluded to it earlier: production issues happen when someone who shouldn’t has access, makes a mistakeÂ in production. I’ve seen dropped tables, altered schemas, renamed procedures, changed permissions, and evenÂ deletedÂ databases in production thatÂ came directly from some dev or report writing mucking around in production, where they have no business being. And that’s just the tip of the iceberg.
You should also conduct this lockdown because of external threats.Â SQL Server permissions are nothing to scoff at.Â Why do you thinkÂ hackers steal dataÂ from companies so often?Â Because too many people and applications have permissions they shouldn’t have.Â Hackers are a very real threat and once they get into your environment they can only do what you’ve already given them permissions to do.
You can’t look at it like you’re giving your own developers and applicationsÂ SQL Server permissions. You have to look at it like you’re giving thousands of hackers those permissions.Â Because if they get into your server, they’re going to use those permissions against you.
Trust Your Security Experts
Stop letting your developers run your shop.Â Trust your DBAs.Â Because hereâ€™s a secret that your developers arenâ€™t going to tell you: theyÂ donâ€™t know anything about security or permissions.Â Your DBAs (hopefully) do.
Even though youâ€™ve known this developer for five years, that doesnâ€™t mean the developer knows what heâ€™s talking about. Â Remember thatÂ the loudest wheel gets the grease. Â When it comes to production privileges, the developers are always the loudest wheels.Â But instead of greasing them with tough love, you grease them with extra permissions.Â And thatâ€™s why your environment is so unstable.
Executive summary:Â SQL Server shops are unstable because too many people have high level permissions. HaveÂ your DBAs conduct a lockdown, but first, warn all of the managers, VPs, and the CIO that people are going to complain and predict doom and gloom. There is no doom and gloom, just a lot of tantrums and dangerously unsecured servers.
Download FREE Maintenance maintenance modules: