February 24, 2009 at 7:22 am
I have seen some posts that touched on this, but I haven't seen anything describing my particular scenario.
In the past, our developers have all been given read permissions on production databases. Now my management wants to allow the developers update authority in production for handling night-call situations when errors/problems occur. He would like them to be able to use this security to update production at anytime and wants to make sure that we can audit anything that occurs using these secure logins.
I can't possibly log every update to every table in our databases without performance issues so I'm at a loss. He says that even a log showing when they logged in/out with that id would be sufficient - they basically would have to be accountable for every time they used the secure id. I have found audit logging that will show every login, but not when they logged out. I'm not sure I like this approach anyway, just exploring my options.
Anyone have any opinions or suggestions for how to handle 'emergency' updates to production by the developer staff?
Thanks,
Kim
February 24, 2009 at 7:44 am
kim.talley (2/24/2009)
Anyone have any opinions or suggestions for how to handle 'emergency' updates to production by the developer staff?
Yeah. Don't.
All updates, even emergency ones, should be done by the DBAs. In my experience, allowing devs change rights on prod (especially if you can't audit changes made) is a bad idea
Who's on call at night? You or the developers? How often do things break at night and how critical is it if they do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2009 at 8:06 am
We had a similar situation...Developers were givien full access because an important project needed to be updated to production as soon as the developers finished a module.
the problem was, because of the "rush", the developers promoted code that was not 100% tested.
...they deployed it to production, found out there was errors, and then rushed to fix those errors...
...and because they had access, deployed their untested fixes to the code they didn't fully test to fix their first batch of errors...
...and so on...
...and so on...
until their access was taken away, and the code went back to being tested via normal channels.
Taken away because production users reported "why are there so many errors all the time now.
No if you've got developers who never make mistakes, you are fine giving them full access anytime they want to update production, but I haven't met one of those developers yet.
Lowell
February 25, 2009 at 7:28 am
Lowell (2/24/2009)
No if you've got developers who never make mistakes, you are fine giving them full access anytime they want to update production, but I haven't met one of those developers yet.
So true..:-)
It is always risky to allow anyone else to connect to the DB and do modification. I am on the verge of removing the Net Admins access to DB.
-Roy
February 25, 2009 at 7:47 am
When you say Update authority, do you mean the ability to update data or do you mean Alter [object] authority? There is a difference. If it is to update data then, yes, in a busy system the overhead of auditing data changes could be potentially prohibitive. In theory you could limit this by checking the user making the changes in the audit trigger and only log changes made by your devs, but then this could be a maintenance nightmare as devs change.
If it is Alter [object] then auditing that with a DDL trigger would not be as bad on the system.
In reality, I'm not sure why these situations need to be handled differently than problems that occur during business hours. There is a reason why you have processes in place for making changes. The other issue is are you going to remove those rights during business hours and replace them for off-hours? If you don't do this, what is going to keep the developers from making changes whenever they want?
I'd be sure to discuss this with management, presenting why you disagree and the problems posed by this change. I'd even ask for something in writing so that you can show that you have presented your reasons for being against this change.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 25, 2009 at 9:04 am
Jack Corbett (2/25/2009)
When you say Update authority, do you mean the ability to update data or do you mean Alter [object] authority? There is a difference. If it is to update data then, yes, in a busy system the overhead of auditing data changes could be potentially prohibitive. In theory you could limit this by checking the user making the changes in the audit trigger and only log changes made by your devs, but then this could be a maintenance nightmare as devs change.If it is Alter [object] then auditing that with a DDL trigger would not be as bad on the system.
In reality, I'm not sure why these situations need to be handled differently than problems that occur during business hours. There is a reason why you have processes in place for making changes. The other issue is are you going to remove those rights during business hours and replace them for off-hours? If you don't do this, what is going to keep the developers from making changes whenever they want?
I'd be sure to discuss this with management, presenting why you disagree and the problems posed by this change. I'd even ask for something in writing so that you can show that you have presented your reasons for being against this change.
This is for updating data, not for altering any database objects. This could actually be during business or after hours. The point is that if the developer finds an issue with the data, they should be able to correct that without the involvement of the DBA team. I'm not sure I'm going to come up with a solution that will satisfy both management and the DBA group!! I don't really want to get called at night to update tables if a developer promotes bad code, but that is better than having my production data messed with.
February 25, 2009 at 9:12 am
Updating data in production in the middle of the night, that too probably with couple of beers inside and not enough caffeine, its a bad idea. You might do a blanket update. No where clause... 😛
-Roy
February 25, 2009 at 9:14 am
What kind of system is this? What data are you storing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2009 at 10:23 am
We are a transportation company - operational systems as well as payroll and accounting systems. Some are third-party apps but the majority are inhouse written apps created by our developers.
March 7, 2009 at 8:52 am
Funny i had the same questions asked me yesterday - my reply was no....first you have tested it then it not be signed off and you want to put in production noway.
I had to move a table into production and it wasn't normalized it was a hugh flat file i denied it - it caused all kinds of concerns - so when developers thing they have something great it not actually to standards so i would say no. If that means waking me up then thats what they do.
Give your management all the pros and cons...one now backup prior to adding the update and then your responsibile for developers.....
March 7, 2009 at 3:00 pm
I agree with what's been said, already. The paradox of "emergency changes" is that's when bad code and mistakes are most likely to happen simply because there's a rush on to accomplish the task. No developer should have update privs to the production DB. The act of forcing the developer to go through a DBA will, if nothing else, cause the code to be reviewed by a 2nd party. And, likely as not, developers won't take the proper precautions of finding out what the expected row count of the update will be, wrapping the code in an explicit transaction, and verifying the rowcount prior to either commiting or rolling back.
Nah... giving developers, or any one else for that matter, the kind of access necessary to make emergency repairs will simply back out the screws from all of the hinges on Pandora's box whether the actions are fully logged, or not.
Do you want to be the one who gets the call at 3 in the morning from someone that says "I accidently updated all the customers in the Customer table and couldn't do a rollback because I forgot to do a BEGIN TRAN"?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2009 at 3:56 pm
At your company, who is currently in charge for maintaining CIA (Confidentiality, Integrity, Availability of data) an who will be after granting production access to developers?
If the DBA group is responsible and will be, the answer should be: no!
A "in-a-rush-working-at-midnight" developer is likely to oversee some side effects of the changes he applies - even if his code looks fine in the first place. Maybe he won't even notice after implementation...
But then you have -theoretically- those reports being generated at 5am that will be spread out over the company showing wrong data. Who will get the call from first shift at 6am? The dev? I don't think so.
We are using the following method at our company (running a two shift production):
If a project went into production we have a 24hr DBA-support for a given time to support the developers in case of hot fixes. This is temporary, frequency decreasing, known by management, and accepted (even paid 🙂 )
That surely means that a few of us work their 8 plus "x" hours and after that spend the night next to their phone and laptop - just in case.
But we figured this is much easier to handle than getting that 6am-call facing all the damage that's already done - and having the devolper unavailable 'cause he left short after applying his changes...
Maintaining CIA also means -at least from my point of view- some sort of "CYA". So, if one of our dev's is asking for a DBA-bypass we usually offer him full responsibility for CIA starting with his first login. That usually helps a lot...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply