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 your data and protect yourself.
*Reposted with permission from SQLSoldier.com.