... needs a database user that can actually modify or drop production tables???

I would have fired the DBA.

But I've seen too many database where everyone connects with highly privileged users. Assigning correct permissions is work...

It was written / customised by a third party and sounds like things didn't go well

You have all the processes you need to perform thoroughly documented, peer reviewed by at least two other people, tested in a non-live environment and then only performed on the live database by approved people who fully understand what they are doing.

And when I say documented, I mean written by a human with experience, and not hallucinated by some shitty AI womble.

This is why you SCRIPT stuff like this, and only deploy it in production after testing in a dev/QA environment. No one should be given live SQL access to a production SAP database. Like, ever!

If you need to muck about it in for "custom" reports that can't be scripted then you have a daily replica copy created and do your reporting on that. If someone balks at the cost, you justify it based on the inevitability of human error and the fact that the replica combined with copy-on-write to preserve the original replica can act as a backup/DR depending on its location. If they still won't spring for it, you save a copy of the email where you listed the potential consequences and their refusal to approve it for when shit inevitably goes pear shaped and the people at the top are looking for a head to chop.

Email? Require it in writing. Signed. Allow ink as a concession after making clear you'd have preferred blood.

Meanwhile, back in the real world of small/medium business ...

"Various users" use direct SQL access for ad-hoc queries. Been there, done that.

If it's a "proper" database, then as already suggested, you properly apply permissions and/or create views to prevent such accidental deletions. In our case, the system used C-ISAM files and we used Informix to access the files behind the system's back. When we built the Informix databases, we'd create the tables in Informix, then delete the files and replace them with symbolic links to the real C-ISAM files. That worked well enough for us, especially after I found out how to automatically extract table descriptions and script creation of the whole lot in one go. It also meant that if any one did drop a table (no-one ever did, only 4 of us in IT used it - end users came to use for reports) it would only drop the symlink and do no harm.

No process, documented, tested, approved, backed-up etc. which is operated by a human, experienced or otherwise, can avoid Murphy's Law (other names are available): If it can go wrong, it will.

Rather like complex numbers, AI introduces another route, which despite having an imaginary content, has real effects.

Afterwards, both humans and AI will make up an excuse why it wasn't their fault.

"Hey, don't blame us!" said Orinoco, "There's a lot of rubbish for us to clear up on Wimbledon Common so we haven't had time to train our AI avatars yet."

Why the hell are people deleting records/dropping tables when doing QUERIES and REPORTING??

You don't just "accidentally" type "delete" or "drop" instead of "select"

Also, transactions don't save you from dropping a table. "Dat shit's gone, bro"

I'm pretty sure that this is one of the big advantages of using Views

Have you ever needed to use CREATE TEMPORARY TABLE for a complex report? Maybe forgetting the temporary bit and/or tidying up afterwards? I know that I have. I also know that I have good backups!!

These things shouldn't be happening, but inevitably do. Permissions should definitely be restricted though.

Indeed, I did. And I also did it only on a replica of the production DB created for reporting purpose. No harm done and any mistake is taken care of in the next replica job run.

I can't say I've ever checked but I'd expect the temps to go away when the connection's closed so maybe no need to drop explicitly if you're worried about that. But give the temp tables clear names so you're aware that that's what you're dropping.

They do but while you are developing the code, you are always in the same session and have to keep dropping them to rerun the code after bug fixing.

As the Regomiser has christened him "Holden", can we surmise that it was a car Company with the keyboard in the drawing room Australia?

Was he playing Texas Holden with the database?

After that mess, Holden said the company issued strict instructions that the team must always use the BEGIN TRANSACTION , ROLLBACK TRANSACTION, and COMMIT TRANSACTION commands!

And also, access should only be granted to a SELECT few

But always including little Bobby Tables, lest we lose such illuminating illustrations as this!

At one time or other I guess we have all wished we could prune the family tree.

Former colleague (let's call him "H") in pre-internet days deleted a whole user directory containing about 2 years work from a shared PC. He had run "DELTREE" on a directory higher than the one he had intended. I think the screams were audible about 400m away...

Fortunately I am one of those obsessive people who learned to back everything up in 2 places (the hard way but that's a different story) and I had a box of 3.25" floppy discs in my locked desk drawer on which I had backed everything up the previous afternoon (fortunately including all of the project files for the whole team). Rebuilding the directories only took a few minutes and then half an hour or so to reload the data from floppy.

All that was lost was a single days' work in the end.

I admit to having used some industrial strength language to my colleague along with threats of physical violence should he ever do something so stupid again, closely followed by RTFM before you ever do anything with the operating system.

Of all the people I worked with over the years H was one of the best demonstrations of Dunning-Kruger syndrome that I have ever come across.

I very occasionally use direct SQL to update the live ERP or Financial data, usually due to a bug in the software which fails to set a status field correctly, but it's rare. Even then the policy is to make sure I run the criteria as a SELECT first and the ALTER only once that's confirmed (I normally input the change in comments in a script which prevents it being run prematurely by accident).

I can see no reason for any reporting functions to involve manipulating data in the live database - anything complicated needing separate tables is in another database so at the worst there may be an interruption in a report working, never the live systems.

It would be preferable to follow the protocols mentioned above to have two people peer review the changes but when you're the only person in the organisation who knows SQL that's simply not possible, so you have to be meticulous!

It's the mentality of "I've done this before".

All I need to do is type in this command, Enter and - oh shit, I wasn't expecting that !

That is what happens when you don't have a test environment. You do everything in live and you do it fast because 1) pressure from Up High and 2) false pride in thinking that the quicker you are, the better your reputation. Except that going quickly has to mean skimping on checking your work and, unfortunately, you're never as good as you think you are so, mistakes happen.

As mentioned once or twice on here before; every company has a test environment, don’t they?

And a few also have a separate live environment!

The fraction of time between making a mistake and realizing it.

It's also the smallest measurable unit of time.