‘ERP down for emergency maintenance’ was code for ‘You deleted what?’ Another Monday is upon us and The Register therefore presents a fresh instalment of Who, Me? It’s the reader-contributed confessional column in which you admit to making mistakes, and explain how you made it out alive afterwards. This week, meet a reader we’ll Regomize as “Holden” who told us a tale from a time his employer “ …
If the DBA in question was *ordered* to provide that access by the powers that be said DBA is between a rock and a hard place. Especially if the command staid after the aforementioned DBA explicitly stated the risks, and the reasons why write access for even one developer is a bad idea.
Or so I’ve been told…
I can only suspect that the DBA hated their users, and actively wanted them to mess up.
At the very least remove permissions to do this sort of thing on the production tables. No-one should have permissions to do that except DBAs, who only uses their admin accounts when necessary.
If the users really need to be creating and dropping temporary working tables, then they should be doing that in their own separate schema, (supposing the database used provides for that).
I'm going to guess there was exactly one (1) database user, with full access to everything. They undoubtedly had a very simple password.
This user was then used by everyone and everything.
I don't think I ever worked at this company, but I've worked at places very similar to this company
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.
You don't need it in writing unless it would be for something that would go to a criminal court - like if you were being asked to do something clearly illegal and you wanted your refusal on the record.
After getting it in writing you should begin a job search because that isn't likely to be the last time they try to rope you into doing something illegal, and next time maybe you don't know that what they're asking is against the law.
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.
I liked the approach taken by a former colleague which was yes you might be allowed to do it, after:
I have seen what it is you propose to do.
I have approved what it is you propose to do.
Someone else with my level of knowledge and experience concurs with my approval of what it is you propose to do.
I watch you do what you propose to do on a test server.
I confirm that what you did has worked successfully and has caused no issues.
.
.
.
.
.
.
.
.
Then and only then you might get to do it on the Prod server, this is not guaranteed.
I work in pharma. A formal version of that is precisely what we do for any code changes:
1. Propose the concept of the change
2. Get official approval from multiple departments for the concept
3. Now that the concept is approved, have each impacted department provide details of exactly what needs changing and how, and what paperwork, documentation revisions, verifications, etc. are needed along the way. (Setting it up on a dev system is a good way to see what needs changing.)
4. Once all that is agreed and approved, write the instructions for how to test it (almost always on a dev system). Have those approved by both an SME and the appropriate person from Quality. (Smart coders will dry-run the instructions before submitting for approval; this catches errors in both the instructions and the actual code.)
5. Implement on dev. Run the test, recording results (in pen on paper, with initial and date for each step). Have an SME review it and sign that it's right. Have Quality review it and sign that it's right.
6. Implement on production.
7. Finish all the other stuff identified in step 3.
8. Show that production is working. (For changes that are "in case this rare event happens, do this", "show it's working" means "still producing as normal after the change".)
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.
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.
That's why usually CREATE TEMPORARY TABLE uses a different permission set. You can grant a user the rights to create temporary tables - and delete/truncate/drop them - without having any permission but SELECT on real production tables. And if you need more permanent ones you're going to create them in a separate database/schema/whatever where you can play without creating havoc.
The "everyone is root/administrator/dba" approach is always the reason of big disasters. permissions/ACLs do exist for a good reason - not using them properly is only laziness.
Still I see libraries for programming languages that want to create and upgrade the database themselves.... so people use them, give the application all privileges, then run the application with the same user.... what could go wrong?
This post has been deleted by its author
And....
Don't ever leave a transaction open on Prod, even if you just did a SELECT.
Certainly on MSSQL, running a SELECT takes out locks on the table rows visited (and sometimes those which aren't)
So if you forget to commit or rollback, everything looks great on your SQL window, but every other user will be gummed up waiting for you to release the lock!
Ask me how I learnt about this one :-)
Done that the once. Was my own deployment script, written and tested and safe, full ROLLBACK in case of things going amiss.
I was so confident of it I left it running and went on my lunch break. Came back to find chaos. I don't remember all the details now, but we'd been working with an offshore team whose code I'd plugged into mine as it had been peer-reviewed and approved. Turned out that it fell over because "Germany" isn't the name of a language and my code had a hard-coded list in it with no failsafe. Very much tail between legs for me, though it taught me to anticipate absolutely anything in your "sanitised" datasets. When we looked closer at the other values for language names that the outsourced team had shipped, we also found "DenMark" and "Janpanese". ¯\_(ツ)_/¯
So if you forget to commit or rollback, everything looks great on your SQL window, but every other user will be gummed up waiting for you to release the lock!
This is why always doing a COMMIT TRAN twice is always a good idea when making urgent manual updates - once to commit the transaction and remove the table lock that has frozen the application for a few thousand users, and a second time to commit the transaction and remove the table lock that has frozen the application for a few thousand users because you thought you'd already done it once but hadn't!
And don't ask me how I learnt about this one, because it still makes me twitch thinking about it...
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.
> learned to back everything up in 2 places the hard way
Isn't that the way EVERYBODY learns?
"There are those that make backups, and those that have yet to lose irreplaceable data."
"You don't convince family members to take periodic backups. Repeated, tragic data loss convinces family members to take periodic backups. Same as everyone else."
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.
My manager actively encourages us to slow down. I've had him speak some (mildly!) unkind words because I clicked the wrong thing on the screen (missed the right one by a couple millimeters), trying to get the work done quicker. If our department had a mascot, we'd be the Thinking Turtles.
Don't knock it - it works!
Very, very few reports need up to the minute data. In many cases, they don't want that at all.
So the answer is to clone the transaction database into a reporting copy. Now you have a copy that users can access and if they screw it up, no problems, it will refresh at the end of the day. And as a bonus, you have an online copy of yesterdays end of business data, just in case someone does drop a table from production.
... and to ensure the reports aren't made inaccurate by people changing the reporting copy, make their access to that reporting copy read-only.
If they need to change things to make their reports work, they can make a copy of the copy, change rights/permissions on that copy-of-the-copy as needed, and modify it as they like.
All major relational database systems allow DBAs to control what each user account can do by granting different levels of privilege, all the way from superuser status down to "you can connect to the database but you can't see or alter any data". So why -- especially after the first incident -- didn't the DBAs severely restrict the reporting account to allow it only to execute SELECT? That's what we do in my organisation. If you have a valid need to view data, but not to alter it, we grant you SELECT privileges only. You can try to execute INSERT or UPDATE or DELETE, but the database will respond with "you don't have the privileges to do that".
Briefly worked on a project which stored all data in a massive Oracle database. All data was returned through named queries or views, with no direct access allowed to the database. They then decided to implement database security for the named queries and views, and *everything* broke. It was so borken that they had to give back admin rights to everyone. Learnt a valuable lesson there: "integrate security early", which I promptly took to my next job.
1. Some years ago I got a call from a fellow in a related organization, whose database we hosted. He said "I tried SELECT * FROM important_table, and it says that important_table doesn't exist." I connected with full privileges, checked, and said, Well, no, it doesn't exist. My recollection is that he was down to about three tables out of about 100. Fortunately, this was after Oracle had introduced the recycle bin, so restoring his tables took only a few minutes.
2. Some years before that, some people on another team found a certain table, or maybe two or three tables, vanishing. I suspected that this came from clicking the button in TOAD that runs everything in the edit window, rather than just the command under the cursor. They said not. I created for them a table of tables not to be dropped, and then a before DDL trigger. Whether through more cautious use of TOAD or because of the trigger, their work ran more smoothly.
So, this is a combination of :
Poor requirements management and a short sighted project scope.
Poor security model
Poor operating model
There should not be any need to report directly against the database. Big impacts on availability, when it goes wrong, big impacts on operational performance when someone does a bad join and flattens the database server. More risk of deadlocks, impacting main operations.
Major issues here with the lack of security, auditing and the risk of data leakage and data maniupulation, outside of any validation performed by any UI elements.
Why would reporting be done done with write permissions. In any halfway sane system the reporting would done with read only accounts, or if you need to write using a separate DB.
Confessional time, I was working on an internal application that had never been finished so user management was done by SQL query. I accidentally forgot a WHERE and gave 30 odd people the same first name, resolved by swiftly restoring the table from backup before anyone noticed
Not being a dev or anything serious like that........
But we do seem to hear of big data managing projects that cost trillions more than they were meant to, took years longer than they were meant to ( if they completed at all), and still never worked properly ,an awful lot. To the point that the whole industry starts to sound like an enormous scam. Maybe the idea of an all singing, all dancing, do everything system is a bit of a dream
$bigCompany did just that. After the most humungous fallout, big boss got everyone together and said “let’s not do that again, shall we?”. Do it in stages so when you finally realise you are trying to boil the ocean you can take a step back and have a grown up conversation with the customer.
I cannot believe a live ERP database had Dev access to write queries and updates and deleted on its backend.
Rule one of Reporting and DB access is give only the required access you need. Not fudge the reporting off it. That is insanity.
There is enough 365 licensing out there for Powerbi, or license the reporting component of the software ERP provider for this reason, Cognos, Qlik variant of products. Sap Business Objects and others to boot.
If your limit take copy of the data overnight to a seperate SQL or Postgres if your going cheap and still want Enterprise level and even homebrew by CTE and only Queries you might be a day or possibly half day behind or feed it with the ERP Secondary to avoid concurrency issues.
But, no wonder they busted it. What the hell were they thinking as there is no excuse and to get to six months. Is just luck. We all have had to stich missing data in a reporting stack, but to have to do that with an ERP just wow.
Should be a service account for erp service for maintaining the system. A set of raised access for DBA and service accounts for sysadmins with no DB access for patching etc. With a service account for replication. But, no way write and update access to Report Devs.
The Register 
Biting the hand that feeds IT
Copyright. All rights reserved © 1998–2025
