In addition to the whitepaper, I have been taking a new look at this topic and providing some additional and supplemental information via blog posts. If you missed the 2 earlier blog posts, I recommend taking a look at them. Today, I am going to complete this topic by taking a look at the importance of security.
Guardian of the Data
I sometimes get funny looks when I tell people that the DBA is the guardian of the data. I often hear DBAs complain that they have no say-so in who gets access, and they are forced to give everyone any access they want. But when data disappears, who is going to be the person expected to recover it? If production processes are blocked by a careless query, who is going to be blamed for “SQL blocking”? It always comes back to SQL Server and the DBA. No matter who tells the DBA to give someone the access they want, it is he DBA who will be held accountable for allowing it to happen; for not making them understand that this could happen.
When someone requests access to a production database, the request should only be granted if it meets certain criteria. It must be necessary and justified. Some of the common reasons people request access that is NOT necessary and justified include:
- It would require a lot more work to do it in non-production
- The non-production environments do not support that feature
- The non-production environments do not have enough or current data
- My manager/lead/PM told me I need this access
- This error only happens in production
- We don’t have time to set it up in non-production
We don’t do things in production because it’s easier. We don’t do things in production because we don’t want to spend the effort to make a non-production environment work for our needs. And we definitely don’t do things in production because someone told us to.
Another common thing I see is the DBA may say no at first, but then the person requesting the access complains to someone that they can’t do their job because the DBA won’t give them access. So some manager gets involved and tries to force the DBA to give the access. This is the point where a lot of DBAs cave in and give access. It is not a DBA’s job to cave in to pressure and give someone access. It is a DBA’s job to protect the data from unnecessary risks. If access is not necessary and justified, then access should not be granted no matter who says they should get it.
Sadly, there are times where a manager or executive above the DBA forces them to grant access that is not necessary and justified. As a DBA, it falls to us to make sure that person understands the potential consequences. When I have been in this position, I laid out exactly why the access wasn’t necessary and justified and the potential harm that could result from granting the access. In writing.
This occurred to me once at a former employer. The PM for the engineering team wanted all members of the test team to have read-only access to the production database even though there was a replica available for such activities. The PM escalated to the Director of Operations, and we were put into a position where we forced to grant read access to all test team members. I laid out that there were alternatives to direct production access and the potential harm in granting the access and was over-ruled.
Now fast forward to about 4 months later and a new member of the test team ran a large query at the end of the day … on Friday evening after most people had already gone home. The query was taking a long time so the test person went home with the intent to check it later that night. The on-call DBA had to be paged because a main production table was locked by the test person’s query and had been blocking new activity for 45 minutes. If this application was unavailable to users, the company lost an average of $4200 revenue every 5 minutes. By the time the on-call DBA logged in, found the problem, and killed the query, almost an hour had passed. If you multiply $4200 X 11 (55 minutes), that comes to $46,200 in lost revenue due to a careless query executed by a read-only user.
In case you have not already guessed it, the PM for the application blamed the DBAs for giving the new test team member access to production. I showed the written evidence where I protested and outlined the alternatives and risks to show them where the blame truly lay. We were able to remove access for all test team members after that fiasco, but it took a $46K+ mistake to get this done.
If I had merely caved in and granted the access, I would not have been able to defend that it wasn’t my fault. I would have been held accountable. I have seen DBAs lose their jobs because they granted access without following protocol for requiring justification. And you could lose yours too. Protect yoru data and protect yourself.
Chris Yates
Great piece of advice. With data comes great responsibility, and as data professionals we must realize that the always hurry up and get it done routine is not the best avenue to take. Well said on all fronts. Thanks for that
SQLSoldier
Thanks! People seem to think I’m a bit on the strict side.
Dale
While there are mechanisms in place for a lot of this stuff, I have found pushing the onus back on the team’s themselves to be the best option.
Requiring the use of security groups for all access helps in the formulation of policy. Then the policy-maker is on the hook for allowing someone specific levels of access by adding them to required security groups.
Internally, there is even a tool that will help enforce this type of policy, thankfully, including auto-approval.
I do agree that making security a part of your toolbox is vital.
SQLSoldier
FIM is available externally as well. It doesn’t do everything that the internal tool you’re referring to does, but it does a lot.
That said, I’m not a huge fan of giving ownership of the group to an outside party. I would rather own the group and control who can be a member because I’ve seen it abused too many times. Unfortunately, in some places, there are too many groups for a small DBA team to try to manage themselves, and it is best to give the group ownership to someone else to control for an application they own. You just have to educate them on what that means.