Prevent accidental deletes in production

Question

Prevent accidental deletes in production

Indianrock

SSC-Insane

Points: 20333
More actions
February 6, 2017 at 10:26 am

#319279

We have a number of production support people who do live updates and deletes in production every day. Some of these are experienced, some not so much, but anyone can make a mistake. I'm looking for tools, free or otherwise, that will either limit transactions to 1 record or prevent their execution if there is no where clause. ( or if things like TOP or Select Count aren't being used ).
These "scripts" aren't going through QA or anything like that and often they're highlighting blocks of code, not running the entire thing ( that's the intention anyway ) I saw one person using begin transaction but then occasionally she'd forget to commit afterwards and cause blocking.
I also suggested they use Set No Exec on, but some probably feel I'm either meddling or that it's a personal attack. But we know who would be up all night restoring trashed databases ( not them )
SetNoExec

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jasona.work SSC Guru Points: 50118 More actions · Answer 1

Possibly the best route to take would be to go to the bosses with your concerns, along with several possible solutions / mitigations (and rank the solutions honestly based on something like "easy to implement / slightly reduced risk" to "hard / costly to implement / greatly reduced risk") Go prepared to explain the potential hazards if someone accidently runs a DELETE * and forgets to highlight the WHERE clause.

Estimate how much time, effort and cost if you have to repair a trashed database, and present those numbers to them.

Basically, either get the bosses behind your efforts to resolve the issue, or get it in writing that the bosses are OK with the potential disaster...

(I didn't read the SetNoExec article you linked, it won't come up on my work computer...)

Sue_H SSC Guru Points: 90860 More actions · Answer 2

I've worked at places that had support people doing the same types of things. One place ended up creating a front end for that group to use which had additional "checks" for the different activities. But even with that there were still problems, not as bad but still problems.
Another stopped allowing direct inserts, updates, deletes - there were stored procedures they had to use instead which required parameters to limit how many rows they could break. They could still read data directly so the select * from BigTable still happened but less got hosed from bad deletes or updates.
That kind of situation your in is bad since it's really a workflow problem for the company and how they do business. I don't know that there are any decent ways to address that, other than not allowing it. Or removing the D key from all of their keyboards.

Sue

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 3

Is there a staging environment where they could run tests on first for validation before running on a production system?

Also, I am a big fan of explicit transactions for these reasons. It makes fixing human errors a lot easier since if no commit was issued the work can be rolled back.

Joie Andrew
"Since 1982"

isaac_654135 Default port Points: 1414 More actions · Answer 4

Setting aside all of the high-level advice you will get (most / all of which you probably already know, but it's out of your control), I have used one specific thing - and it was to protect me against my own self, since even in a "development" environment, despite claims to the contrary, mistakes actually do sometimes cause trouble or re-work, especially without source control in place.

I simply set After Update/Delete triggers on several tables, which rolled the transaction back if the comparison of @@rowcount versus all records came back a certain way.

Of course you can endlessly customize that, and it wouldn't withstand all kinds of theoretical criticism, but as long as you are essentially sure that you will be creating no conflicts (which are greater than the trouble you are solving), then there you go.

Some days my finger just seemed to have a magnet toward the F5 key. I got frustrated once when dealing with my own little project tables and wrote that trigger. It helped me at least twice. And thinking back to that environment I was in at the time (where I don't see any other solution I could have implemented, better in writing as it may have been), I DEFINITELY would have slept better at night and put it on a lot more tables, if there were (as you describe) people outside of the development group who were allowed to write SQL commands directly to the database with no application in between.

You could also try some trigger-driven audit or logging solutions.

Another idea is to save logs of transactions on update/delete/insert statements with user ID and full text command, just the knowledge that such a thing is in place generally raises people's level of care.

And of course, the suggestion I gave about After Update triggers could be used to check if @@rowcount is 1 or not. You could even raise a custom error, if you can get that back to the calling application, if they use one, but at least it will show in management studio in no uncertain terms - maybe help them understand what happened before they ask you. Heck, you could even sp_dbsendmail to their manager ... 🙂

Indianrock SSC-Insane Points: 20333 More actions · Answer 5

Great ideas. Thanks to all. For the time being what I've done is asked our Security manager to email these folks, and their managers, expressing his wishes that things like set no exec and using "TOP" in update and delete statements be used. Set rowcount is going to be deprecated so I didn't want to suggest that.

Sqlprompt by Redgate has a "no where clause, no run" tool but it's $369 per seat.

The Security manager will be able to say something like "your cooperation is appreciated AND EXPECTED."

If I see stubborness I may just disable logins randomly.

isaac_654135 Default port Points: 1414 More actions · Answer 6

I wasn't sure if your set rowcount was referring to my post or not, but a trigger testing @@rowcount against all records in table will catch & prohibit all statements without where clauses and isn't deprecated.

Glad you found something that makes you feel a bit better, either way. 🙂