- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
This wins the award for Most Confusing WTF of all time.
Is there a punch line? I mean it's obvious the WTF is the stored procedures bit, but what's the rest of the story?
Admin
There's just no helping some people.
Admin
A Volgan government perhaps. Anyone see the movie Idiocracy? I think its what SQL servers want.
Admin
one week later, the database crashed. Since there were no problems prior to Tris arriving he was the one blamed. As proof they showed Tris' prints as lifted from the keyboard in the secured room. Tris has since disappeared and the crashed web site has had every evidence of its existence expunge from public and private record, thereby once again proving that government systems never crash or ever have any failures ever in recorded history.
"And now you know, the rest of the story."
Admin
Security > BCDR Plan (pwned!)
Admin
I honestly don't know which is worse: deleting necessary stored procs (in the name of security) without understanding that they are needed, or blindly doing it in spite of knowing that they're needed...
sigh
Admin
My vote for the worst part of it all is a DBA who was not only incapable of setting up a backup without system stored procedures, but seemed unaware that such a thing was even possible.
Admin
To be fair, according to the story he never even got the chance to offer the option of an alternative.
Admin
Even if they didn't want the DBA to use stored procedures, suddenly telling him to 'get out' when he suggests them is pretty messed up.
Admin
um, although not ideal, you can use TSQL to backup the databases, assuming the master tables are in tact, which the sytem wouldn't work if they weren't.
Yes Stored procedures allow you a nice wizard to do the backups, but . . . . aren't needed per say, although the stored procedures being a security risk, I'm not sure I quite understand that.
Admin
What?
"Hey, what's wrong with my backups?"
"Lemme see... uh. Did you know that someone removed a whole mess of stored procedures here?"
"GET OUT."
And this is the author's fault?
Admin
Yes, definitely a government operation. Paranoia mixed with a complete inability to properly explain anything about the system, and stupid rules that are implemented from a high level that have no obvious connection to reality. Probably can't load anything onto the system, because they can't have any devices connected that could possibly allow someone to steal the data. I bet most of the ports have also been filled with glue to prevent anything unauthorized from being connected.
However, this isn't where I work, because we aren't allowed to leave visitors unattended for any reason.
Admin
"They were removed because they were insecure."
"Oh, well, I can reinstall the security flaws for you..."
"GET OUT."
I'd have done the same thing. Instead of offering to reinstall procedures that were removed for security reasons, he should have started offering alternatives. Security requirements may seem silly, but they're there for a reason, and they shouldn't be ignored.
Work with the client, don't ignore their requirements. Chances are, they were required to remove them by some higher-up, and readding them was simply not an option.
Admin
Who needs store procedures to backup up a database?
Hell, just use bcp if you have too.
Stupid consultants.
Admin
It may sound harsh, but sending someone who even so much as thinks about breaking a securety rule away actually is the sensible thing to do. Probably Tris' employer won't get paid, and another firm is hired to do the job.
Admin
Perhaps, but the WTF is that the requirements to remove them were almost certainly added by some "manager" (syn.: idiot) three levels up the chain. Why was the requirement added? The "manager" read that stored procedures sometimes contained security flaws, asked a "technical expert" (e.g. C/Pascal/PL1 programmer) if they knew of any reason to keep them, and got a "no" for an answer. So I think that the contractor's reaction was not so far from the right one.
Admin
Depressing story. Obviously there is a whole mess of communication issues going on there.
It's like even the option of granting the option of discussion is not open to discussion.
Admin
The WTF is that they left a consultant/etc unattended in the server room for any length of time.
Admin
Furthermore, rackmount monitors don't pull out...were they using an lcd and a keyboard on a tray?
Admin
Who needs stored procedures?
And I take it you've never heard of xp_cmdshell
Admin
Dude gets hired to configure backups on SQL Server. Dude finds all System Stored Procs missing. Dude asks why. Other dude tells them it's against security procedures. Dude says he can reinstall them anyways. Other dude yells at dude and tells him to get out.
This is the first time I completely understood the technology and context but still felt like something was missing.
Admin
hnng...
An increasing number of the WTFs recently seem to be the sort that make me want to hunt down the antagonist and stab him in the face. Throwing out consultants because they made a single inappropriate (as you see it) suggestion sounds like a way to go through a lot of consultants without getting anything fixed.
Also, I was under the impression that storedprocs added to security, not took it away. An account with only access to storedprocs, not any-old-sql, has less choice of how to f*** things up.
Admin
I'm confused. What are stored procedures, and why are the needed to do backups? I thought databases were used to store data, and you backed them up by copying the physical files that the databases is stored in.
Admin
I think you forgot the <sarcasm> tags. At least I hope you did. Ever try backing up a live DB by simply copying the files? Might as well just kill the DB server mid-transaction and hope the db was in a safe state.
Admin
Luke was a man a of few words. And also a very cold man. Tris didn't understand those words. Luke understood that there was no way he could do the task he was hired to do. And summed up everything in two words, 'GET OUT'.
Admin
At times I have been asked to declare all media that I am bringing into a facility, it's then recorded in the log.
The fact that our protagonist had CDs he didn't declare could have been cause for ejection and subsequent cavity searches.
Admin
There's also the distinct possibility that he was thrown out because he brought ANY kind of media into a secure facility with him. Along with leaving a visitor alone in a server room, that also doesn't fly.
Of course, it's the security gaurd's job to make sure that doesn't happen and not necessarily the consultant's fault.
Admin
http://en.wikipedia.org/wiki/Stored_procedure
Copying the physical files is only possible when SQL Server isn't using them - and it's always using them, unless you specifically tell it to detach from them for a while. Instead, you typically use either BACKUP DATABASE, or some stored procedures that make it more convenient to set up automated maintenance plans like "back up each database to such-and-such folder each night at 2 am, appending the date and time to the filename, and delete any previous backups that are more than 2 days old". (Then your regular file-based backup system backs up the contents of that folder at 3 am.)
Anyway, AFAICT, the WTF rests largely with the security restrictions (for having no good justification), slightly with Tris (for not stating this explicitly before suggesting circumventing them), and largely with Luke (for overreacting).
How much you want to bet that Government Department's web site was susceptible to SQL injection?
Admin
Luke is rude and stupid. Although Tris didn't "get it" about the site security procedures, I guess ( Enginner C programmer here ) that the stored procedures would make backup easier, it's no excuse for Luke being rude. And no excuse for Tris being a wimp. Ask "oh, ok, I just though that re-installing the procedures as possible for your operations. From the look on your face it insn't. So, what about we try this instead..." Explain to him that although procedures MIGHT be a security risk, not backing up the database is a greater security risk and work with him the possible solutions given their security procedures. Get in a fight... Seesh, if for some reason Luke is insane and would punch you in the face for screwing up his already-screwed-up database, you'd get a fat check for a broken nose. Or of course Luke got back to his chair and said to one of the other guys there : "Did it again! The guy was SO scared that we just left running! I think the growling was a nice touch!"
Admin
This is (again) one of those cases where you'd like to hear the other side of the story. I have a feeling that this is a slightly abbreviated version, leaving something crucial out.
Admin
SQL Server places a write lock on all database files, so the only way to copy the files is to stop the server. Some SQL systems aren't used 24/7, so it can be an acceptable way to backup the server.
T-Sql has a backup command that is strangely enough called BACKUP DATABASE, which is typically used to backup databases and has been part of T-Sql since before Microsoft stole the code from Sybase. This is the command that is called from the store procedures store procedures that were removed.
What we have here is a very dumb consultant. He's answer should have been. "Ok, without the stored procedures, I'm going to have replicate their functionality. No problem, see, but it is going to take some time." He was stupid because as a consultant time=money.
Admin
I would've enjoyed the story much more if it ended a bit differently...
"Rather than demanding that Luke leave immediately, Luke faithfully returned to his cubicle and filed 57 pieces of paperwork in triplicate requesting that Tris be made to leave the premises. By the time the paperwork was completed, posted, agreed to by committee, duly noted and filed, Luke trudged back to the server room....only to find the skeletal remains of Tris, his cold and bony fingers still clutching the SQL Server CD."
Admin
that remind me of the oracle "wizard"
that wizard will gladly tell you that you should remove all privileges from some sys packages due to security.. then it will report many new errors... related to those privileges it just told you to remove
wtf?
Admin
Admin
And for those saying, "Use T-SQL to do the backups", please keep in mind that with no system stored procedures, you can't schedule these backups, since SQL Agent depends on system stored procedures to run scheduled tasks. Backups that have to be done by someone logging into Query Analyzer or any other SQL management application, and then manually running the backups, are NOT reliable.
So, without the system stored procedures, and the errors in Enterprise Manager said they were missing the system stored procedures, you can't automate the backups from within SQL.
You might be able to get a third party backup system running, and back up the .mdb and .log files directly from the OS, but you'd have to shut down SQL Server (as a service) before you ran the backups and turn it back on after, since otherwise you're trying to back up files that are being used (which only partially works), and you would definitely miss out on things like truncating the committed transactions out of the log file, etc.
So, before you attack the author for wanting a few necessary system stored procedures, please, please, please actually understand what you're attacking.
On the other hand, the point about leaving him in the server room with a critical database without supervision is valid. I'd fire the security guard, the employee and probably their managers while I was at it. If the system contained any confidential material at all, I'd also prosecute the employees involved. "Sure, this database is so important that if it crashes the consequences are unimaginably bad, but go ahead and surf porn while the backup runs (if you can get it running). We don't have enough spyware, etc., on this server."
Admin
Ah but that's what osql and task scheduler are for, cheerio old chap!
Admin
captcha: xevious ... whaaaaaa?
Admin
Yes, but osql has been deprecated and can be removed - so you should not really rely on it being there one day. I have used sqlcmd in some outside scripts on a 2005 install. Personally I consider the use of sqlcmd and/or osql to be just as big of a security risk as system sprocs.
Admin
Admin
The problem here is consultants milking the government for money without giving them any real service, or providing them information that is always taken for granted.
On my very first job as a programmer, my last project was classic ASP 2.0 and Oracle 7.x set up as follows:
Why? Because some consultant told our client that IIS was not secure. Also, we were not allowed to write stored procedures in Oracle, instead we had to rely on one of our team members who was designated as the official DBA for the project.
Every table we created went through a client review process where they nitpicked through every field size and index ("why is this field 20 characters long when the data is 18 characters long?").
As for why this WTF is funny, it is because by removing these system stored procedures the server was pretty much crippled. Any consultant that tells you to delete all stored procedures because they are a security risk is obviously smoking crack.
Admin
I'm confused, and also rather inexperienced with database administration. But given that you somehow send the 'backup' command to the SQL server, why would it be impossible to through together a program that sends that command, and schedule that program to run whenever needed? Stored procedures wouldn't be used, and the server wouldn't have to be shut down - right?
Admin
Ok, I'm just curious...
Is there a way to schedule a backup without relying on deprecated techniques or sp's?
Admin
Sure. Replication on the same machine. Switch one daemon off, backup its files
Admin
I fully agree. And on a second note, regardless of leaving a consultant unattended or whatever - what ever happened to explaining the task, THEN let the person doing the job start doing it?
Admin
And are you saying that in those days IIS was secure? I mean IIS is still not secure, but it's getting better, but in those days, you had to go through a lot of trouble just to secure IIS out of the box.
Admin
I thought I was the only one who ever used Chili!Soft ASP. We got hit with Code Red and all those viruses at the same time, so we were effectively banned from using Windows for essential internal services. They setup Solaris 8, iPlanet webserver, and Chili ASP!. I begged them to let me convert the website code from asp to php, but no cigar.
I remember some funny bugs/features. You could use repsonse, response, or reponse when doing a response.write and it would work.
Admin
I disagree with this part of the interpretation:
Other dude tells them it's against security procedures. Dude says he can reinstall them anyways.
That's not how I read it, at least. It sounded more like it could have been interpreted as some consultant decided on his own that the stored procedures were insecure, and took it upon himself to delete them, thereby farking up the entire system.
The story is a bit ambiguous -- nothing is mentioned about actual security procedures, and from the sound of it, big oafy IT guy didn't offer much information either. So that's the real problem here, I think: communication. Sounds like more of a case of big-oafy-full-of-himself IT guy offering a 2-word explanation and getting angry when he wasn't understood.
Admin
Adam: Yeah, you could do that.
But that's a WTF of its own, compared to the sane way, which is to use the built-in stored procedures that handle backups, and delete only the ones that are insecure, like the aforementioned xp_cmdshell.
And note, everyone, that the guard didn't say that the deleted SPs violated a policy; he said that the previous Security Consultant deleted them and implies that said Consultant said they were insecure.
There's a world of difference, eh? "Our security consultant uninstalled all the stored procedures. They were too insecure." does not quite equate to "Security policy says no stored procedures". Thus offering to reinstall the useful, non-insecure ones is not a Bad Thing in itself.
(PS: Whoever wrote the code to generate the CAPTCHA needs his own WTF. Black text on a nearly-black background is not exactly going to be readable by anyone that ISN'T a robot.)
Admin
Yes, instead of being maybe able to exploit one of a thousand different entry points that might break the system, the system, I am forced to choose between a dozen ways, each of which almost certainly can break the system.
Usually if an attacker can get to the DB port at all, the game of security is lost. Remote attackers usually have better luck attacking or circumventing the security system directly (e.g. the Slammer worm, the Borland Backdoor, etc.) than they do trying to jump through the hoops your DBA set up with GRANT and REVOKE commands.
Stored procedures, especially unreviewed application-specific stored procedures, like all unreviewed application-specific code, usually don't survive very long under the scrutiny of deliberate attack. Contrast with any-old-SQL, which has security rules that are well understood and (hopefully) have an implementation in the RDBMS that has survived years of service and accumulated years of bugfixes.
Of course neither is very useful if your DBA graduated from WTFU. A lot of production systems implement "security" (i.e. business rules that grant or deny authorization or perform authentication) on the client side of the client-server boundary (e.g. the infamous "ADMIN=yes" cookie, or NFS's authentication scheme, or all DRM schemes). There's almost no point in securing the server at all in these cases, since the client is where all the administrative policy lives.
Stored procedures help against accidental damage by moving logic from clients (where it could be out of date, inconsistent from one client to the next, or just plain wrong) into the server (where it can only be the last of the three). But that does not constitute a security feature.
Admin
being a govt agency, they probably have no problem with having "office hours". How about at closing time the database shuts down and a little .bat file copies the database files to a backup disk? Sheesh. This ain't rocket science.