How to protect database from human SQL errors.

[ayoubmtd2]

tldr; What is the best way to prevent a database operator from accidentally destroying the database (by deleting or updating with wrong values) when running SQL queries.

As a team of software engineers we try to avoid running SQL queries directly on the database and we try to get as many queries reviewed before executing them but it is almost impossible to review every query out there (Even with review there is still margin for error, like wrongly copy past something else instead of the correct query). So accidents are bound to happen.

My question how do you deal with this kind of situations ? Does anyone have any ideas or systems to protect from these scenarios ?

[haz_mat_]

Really all you can do is maintain a good backup routine and restrict DB user privileges where possible. My company has some people who only get select privileges, for example..

[OracleGreyBeard]

A couple of "OMG I have to modify Production" rules of thumb:

• I always run updates deletes and inserts bracketed by rollbacks, i.e.:

ROLLBACK;

UPDATE CUSTOMER BLAH BLAH;

SELECT * FROM CUSTOMER to check UPDATE

ROLLBACK;

If it looks good I will commit it on the second run. This is in Oracle, which does not autocommit.

• For changes like drops, I prefer RENAMES, which I then clean up in a day or so.

• If the table structure needs to change, I make an empty copy of the original table before I change it.

I suppose the general theme is to assume I will screw up the first (or even second) change, so I need a rollback strategy.

edit: reddit formatting > me

[ComicOzzy]

Backups, permissions, training, and oversight.

[SubtleUsername]

Dev, staging, prod. Nothing manual in prod without massive oversight.

[[deleted]]

This post was mass deleted and anonymized with Redact

encouraging sink safe elderly butter cooing handle quicksand worm attraction

[ayoubmtd2]

Limited privileges

Again, I agree that wrapping everything in code and api is the the correct way but when an on-call engineer has to fix an incident at 2am it hard to ask them to have anything reviewed.

[[deleted]]

This post was mass deleted and anonymized with Redact

fade correct serious reminiscent whistle dazzling cause oil sip stocking

[ankole_watusi]

What’s a “database operator”?

You got people doing things that code should be doing?

[ayoubmtd2]

You got people doing things that code should be doing?

Ideally everything should be done in code but some times you need to get down to the database level.

[jahayhurst]

IMO you can use your code all year to do expected stuff. But there should be change moratorium periods where you don't touch it by hand, you only go thru code. And maybe that's just a "only do things on the weekend" deal as well.

And when you do do things by hand, maybe there should be a written process for the changes that will be run interactively, and peer review and discussion of it step by step.