It's hard to make things totally impossible, but hopefully a good post-mortem will identify the systemic issues that led to it happening, fix them, and not throw anyone under the bus.
"Ah yeah - it got in a bad state somehow, let me fix it manually"
UPDATE permissions SET allow = 1 WHERE user=671156 AND permission=16 AND org=101 OR 102;
* Change your account to readonly. Make a new admin account, and put its credentials somewhere hard to get (and audited!).
* Make a directory in git for 'one off sql statements'. Make them all go through code review and have an automated system run them on merge/deploy.
* Enforce style rules with a linter/test, like "UPDATE must have a LIMIT"
* Anything the above process is too burdensome to do should have an API or admin interface built for the purpose.
* Aim to eventually get rid of your readonly account. A leaked customer data dump could kill the company and shouldn't be available to any malware on your machine. You aren't as secure as you think you are.
I always recommend that you are wrapping calls in transactions if you are touching prod data. I only left the WHERE clause of my update query off once in my life before I started doing that.
BEGIN; UPDATE... SELECT for verification ROLLBACK;
And then just change ROLLBACK to COMMIT, after you're sattisfied. There's no need to do it interactively.
Do we have any example of any company going under due to a data breach? Unfortunately, there seem to be a lot of examples of enormous breaches that did essentially nothing. (like Experian)
As someone who has never had access to a DB with any serious number of users, can you explain this one further? What if you really do want to update every row? Do you just do LIMIT INT_MAX or the like, and just force people to write that so that they always know they're updating the entire table? Or are you saying you should only ever use UPDATE on a known finite (and small?) number of rows?
Basically, if your table is over a few gigabytes, updating every row in one query on a production instance is a really bad plan.
BEGIN; Statement COMMIT; or ROLLBACK
etc
It should be
UPDATE permissions SET allow = 1 WHERE user=671156 AND permission=16 AND org=101 OR org=102; (user=671156 AND permission=16 AND org=101) OR (org=102) (user=671156 AND permission=16 AND org=101) OR 102
org IN (101, 102)It is often worth running a SELECT on the WHERE clause you are about to use for your UPDATE. That way you can make sure only a limited amount of data comes back before you launch something catastrophic.