• (cs) in reply to dbnoob
    dbnoob:
    Is there a way to schedule a backup without relying on deprecated techniques or sp's?

    Sure. Replication on the same machine. Switch one daemon off, backup its files

    Replication also relies on system stored procedures.

  • (cs) in reply to Adam
    Adam:

    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?

    The backup command also uses system stored procedures.

    Basically, anything that doesn't deal with data commands (system commands, configuration commands, etc.) all use system stored procedures.

    Without the system procs, you can still select, update, delete, insert, etc., you can create tables and databases and procs and views and function, but you can't do anything much with the server services. It's more complex than that, regarding what you can and can't do, but without the system stored procedures, I don't believe using the SQL backup process is possible. (Someone correct me if I'm wrong. This weekend, I'll try setting up a virtual server with a copy of SQL on it with all system procs removed and see what I can and can't do. Now I'm curious - I've never worked with a server someone had sabotaged that way.)

  • (cs) in reply to Zygo
    Zygo:
    Irrelevant:
    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.

    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.

    Yikes.

    That post is a joke, right? If not, you win the "WTF Comment of the Day! (TM)" award.

    Here is your logic:

    Restricting access to your database to only Stored Procedures is BAD because what if someone did a bad job writing those stored procedures, therefore making them hackable? Therefore, it is better to not use stored procedures at all and let people execute direct SQL statements against the server, letting SQL Server somehow decide if a statement is an "attack" or an "administrative task".

    WTF!?? Can you give an example of one of these "hackable" stored procedures? Unless you have some moron concatenating random input together into dynamic sql and executing it blindly, I don't see it happening.

    That is like saying "it is better to swim from Boston to Paris because what if the boat is poorly designed?! It will sink! At least if I swim and drown, it will be my own fault!"

  • Franz Kafka (unregistered) in reply to btruitt
    btruitt:
    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.

    No, it seems fairly clear that he was tossed because he suggested reinstalling the SPs that some unknown bunch of yahoos deleted long ago.

  • Mr Steve (unregistered)

    What a nut!

    Captcha: poindexter

    what the f4ck?

  • SomeCoder (unregistered)

    Why can't they just restrict access to the stored procedures that could cause a risk (xp_cmdshell)? You don't have to DELETE them, you can just block them.

    Deleting them seems extreme to me.

    And this is a major WTF because when someone suggests something you don't think is a good idea or want to do your reaction should NOT be to say "GET OUT!!!" You should explain to them the security measures (even if those are a WTF in and of themselves and in this case, they certainly are).

  • Zygo (unregistered) in reply to skippy
    skippy:
    darin:
    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.

    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.

    Most DB's are in fact designed to handle this case (kill the DB server mid-transaction) provided that the underlying hardware behaves properly. Even MySQL (if properly configured to use the right underlying table drivers) can be abused this way.

    A fairly common feature of large-scale NAS devices is the ability to make a snapshot of a filesystem at some instant in time which can then be backed up, and for busy DB servers that snapshot will probably be taken mid-transaction since busy DB servers are always in the middle of some transaction or other. If the backup actually has to be restored, then a recovery procedure will need to be run as the DB comes up.

    Note that because of the NAS device's architecture the files in question are not "live"--the DB continues to run on one half of the NAS while the file backup feeds from the frozen half of the NAS. If you can't afford a NAS, a simple RAID1 system will work almost as well, if you can split a RAID1 mirrored pair into separate disks on the fly, do the backup from the non-live disk, then recombine the disks into a mirrored pair again after the backup is finished.

    Some DB's are specifically designed for live filesystem-level backups. Usually you have to advise the DB (e.g. by running an SQL script) that you are about to make such a copy, then you must copy the files in a specific order (data files first, log files last, sometimes there are more restrictions e.g. log files in order too). While you are doing this the DB will keep additional data that it would not otherwise keep (e.g. deletion of dropped table files will be deferred) so that the state of the DB can be reconstructed correctly.

    Safety note for the kids at home: If you are not jumping through a lot of hoops (e.g. shutting down the server, reconfiguring the underlying storage, or using a backup procedure specialized for the DB) before (and probably after, and maybe even during) doing a backup by copying the DB files, then you're definitely doing something wrong.

  • igitur (unregistered)

    I get the feeling that Trim wasn't the first consultant to point this out to Luke. If Luke was so quick with his "GET. OUT.", he has probably heard before of this so called "necessary missing stored procedures".

    So maybe Luke questioned this with his boss and his boss said, "If any consultant wants to reinstall the stored procedures, he is an idiot and a security risk to us. Throw him out."

    Probably Luke was only doing his job.

    The WTF lies higher up in the food chain.

  • (cs) in reply to skippy
    skippy:
    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?
    No sarcasm. I honestly don't know what stored procedures are. I'm an engineer, not a database admin. It sounds like code that is stored in the database itself, but why put the code there instead of in the application? Doesn't that horribly complicate source code control and management?
  • Andrew (unregistered) in reply to darin
    darin:
    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.

    Yes, this was bad reasoning. Some stored procedures cause security risks. So, let's delete ALL stored procedures. Is this also true? "Some humans are women. Therefore, all humans are women."

    Someone didn't check what stored procedures do before deleting them. I don't have a SQL Server background. I use Oracle & PostgreSQL.

    PostgreSQL keeps ALL its function, procedures, & operators in the data dictionary (table pg_proc). If this fool had deleted all pg_proc rows, then even the DECIMAL '+' operator would fail!

  • [ICR (unregistered) in reply to Ancient_Hacker

    "being a govt agency, they probably have no problem with having "office hours""

    "public-facing web site"

  • (cs) in reply to Ancient_Hacker
    Ancient_Hacker:
    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.

    better yet, use a real storage system, make a point in time copy of the data, have the web servers look at the copy, disconnect the database server from the original copy, back it up, reconnect the database server to the copy. 0 downtime, 0 data loss.

  • Zygo (unregistered) in reply to Jeff S
    Jeff S:
    Zygo:
    Irrelevant:
    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.

    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.

    Yikes.

    That post is a joke, right? If not, you win the "WTF Comment of the Day! (TM)" award.

    Here is your logic:

    Restricting access to your database to only Stored Procedures is BAD because what if someone did a bad job writing those stored procedures, therefore making them hackable? Therefore, it is better to not use stored procedures at all and let people execute direct SQL statements against the server, letting SQL Server somehow decide if a statement is an "attack" or an "administrative task".

    WTF!?? Can you give an example of one of these "hackable" stored procedures? Unless you have some moron concatenating random input together into dynamic sql and executing it blindly, I don't see it happening.

    Well, dynamic SQL injection attacks are possible too, but I was thinking more along the lines of:

    http://archives.postgresql.org/pgsql-announce/2007-02/msg00014.php

    plus experience dealing with other people's real-world implementations. If the stored procedure has enough privilege to do something that the user otherwise couldn't, then the stored procedure must be trusted, and distributing trust in a system is bad for security.

    To answer your earlier question, an "attack" would be something that SQL access rules don't allow a given user to do, while an "administrative task" would be something that they do allow a given user to do. Stored procedures should normally execute within the access granted to a user (role), not casually grab other privileges on a whim.

    OTOH, views and table triggers/rules are much better tools to implement security without giving away the store too easily. These can strictly control access to data without bypassing standard (or at least well-known) rules of SQL access logic.

    Don't get me wrong, I'm not advising against the use of stored procedures. I've written medium-sized applications where 30% of the code by line count is stored procedures. Stored procedures are good for a lot of things, it's just that security isn't one of them.

    Firewalls, administrative monitoring, random and ad-hoc network traffic inspection, automatic and statistical network traffic analysis, the audit logs from the DB servers (fed from table triggers and yes, stored procedures) correlated with logs from the user-facing front end servers, system-wide security audits, timely installation of security patches, backups, rigorous QA and SCM procedures, physically secure operations areas, the design principle of least-privilege, and a healthy dose of paranoia give me security.

    Stored procedures can help improve system architecture and performance, but they don't generally result in improved security.

    Jeff S:
    That is like saying "it is better to swim from Boston to Paris because what if the boat is poorly designed?! It will sink! At least if I swim and drown, it will be my own fault!"

    Close. More like "don't assume that you can easily build a GPS-based computer control system for a boat that cannot be driven at catastrophic speed into a reef, if you are already allowing users access to the engine compartment and rudder assembly, and you let them bring their own sails."

  • Jasmine (unregistered) in reply to Zygo
    Zygo:
    Irrelevant:
    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.

    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.

    All of that was really wrong, dude. In fact the reality of the situation is exactly the opposite. A limited set of stored procs limits the access points, and if the procs are coded correctly, they are unbreakable, and will not give anyone system access. I'd be happy to show you how it's done. Most people make the mistake of having their application log in as "SA" which completely eliminates any security you might have, whether you use procs or not.

    Don't open the dynamic SQL vs. Stored Procs issue. I've been arguing that point for years and I have been able to hack many a site using dynamic SQL, and very few that use procs. Either method can be a huge security hole, but stored procs provide less margin for error. Both methods require proper user setup and input-checking, and that's where people fail miserably no matter what they seem to do.

    In the article, they may have good reason for deleting the system procs, as those can be exploited as a security hole. However, whenever I open a database and I see no stored procs at all, I assume I'm dealing with a rank amateur, and if I was "Tris" - I would be running for the door long before they ever told me to get out.

  • (cs)

    I'm sure Luke had a good reason for behaving like that.

  • Theo (unregistered)

    Luke. The force is strong with this one.

  • (cs) in reply to darin
    darin:
    I honestly don't know what stored procedures are. I'm an engineer, not a database admin. It sounds like code that is stored in the database itself, but why put the code there instead of in the application? Doesn't that horribly complicate source code control and management?

    Multiple reasons, including reducing network traffic and refactoring logic that would otherwise be duplicated in multiple applications.

    SCCM works the same as with any other type of source code. The source code is a text file along the lines of

    ALTER PROCEDURE (name) AS BEGIN (code) END

    The working copy of the stored procedure within the database should be treated like a binary; you "compile" using Query Analyzer or OSQL or whatever, similarly to compiling C source code using GCC or whatever.

  • dkf (unregistered) in reply to Jeff S
    Jeff S:
    That is like saying "it is better to swim from Boston to Paris because what if the boat is poorly designed?!
    Reminds me of going long distance with Google Maps, especially stage 9...
  • Bob (unregistered)

    I wonder if he sounded like Robert Patrick in Terminator 2 when he said 'Get Out'.

  • (cs) in reply to sir_flexalot
    sir_flexalot:
    Furthermore, rackmount monitors don't pull out...were they using an lcd and a keyboard on a tray?
    Uhm, sure they do... Pull out the keyboard, the lcd monitor comes with it. When they are pulled all of the way out, the monitor pops up.
  • Watson (unregistered) in reply to Gsquared

    [quote user=Gsquared]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.[/quote] I wouldn't be surprised if this lay at the root of Luke's reaction. They shouldn't have left Tris alone in the server room, and should have asked him to declare any portable media he had on him, but slacked off. Tris's first suggestion was to reinstall the SPs off the CD he happened to have with him. Luke short-circuited the logic and was left with the conclusion that if Tris went ahead it would have exposed his and the guard's sloppiness earlier.

    Another thought is that Luke, being one of those responsible, was clueless about the whole thing (after all, if he could do it they wouldn't have needed a consultant to do a backup, right?) and was terrified of the Black Magic that appeared about to ensue.

  • An apprentice (unregistered) in reply to [ICR
    [ICR:
    "being a govt agency, they probably have no problem with having "office hours""

    "public-facing web site"

    It has been done before. http://forums.worsethanfailure.com/forums/thread/118444.aspx

  • (cs) in reply to emurphy
    emurphy:
    Multiple reasons, including reducing network traffic and refactoring logic that would otherwise be duplicated in multiple applications. ... The working copy of the stored procedure within the database should be treated like a binary;
    This makes more sense now, thanks.
  • Flarelocke (unregistered)

    It seems to me that this was inside a highly-classified environment, that Tris was there in gross violation of a huge number of security procedures starting with his mere presence, and that Luke was doing what he did under serious protest. When Tris suggested violating yet another security procedure (and, in having the disk on him, revealed one he didn't even know about), Luke took a principled stand for national security and kicked him out.

    If it was such an environment, Luke and his bosses seven levels up would be fired (and most of those prosecuted) and the next three's careers would be over if those even higher up learned of the gross malfeasance.

  • (cs) in reply to skippy
    skippy:
    darin:
    I thought databases were used to store data, and you backed them up by copying the physical files that the databases is stored in.

    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.

    The funny thing is, Oracle 7 on VMS was so buggy that you had to do a SHUTDOWN ABORT, then a STARTUP RESTRICT, before you could perform a normal shutdown. Killing the db server was pretty much required. Somehow, we rarely had data inconsistencies.

  • (cs) in reply to Zygo
    Zygo:
    If the stored procedure has enough privilege to do something that the user otherwise couldn't, then the stored procedure must be trusted, and distributing trust in a system is bad for security. (my emphasis)
    So how exactly is "combining all the trust required in a single piece of code that can be invoked and managed atomically" distributing trust, when on the face of it it looks like it centralises trust...?

    Addendum (2007-04-18 00:26):

    Yes, but the security issue there is a pretty obvious one (referring in supposedly secure SPs to operations which can be redefined arbitrarily at runtime), and consequently easily avoided (don't do that). Also, as someone noted above, PostgreSQL is unusually flexible.

    I'm not sure what you are advocating exactly, and if all you're saying is "don't assume your application is magically more secure just because you've stuck all your insecure bits in stored procedures", then whilst I might disagree (SQL injection attacks are much less likely, for one), it's a reasonable caution to voice. But if your argument is that stored procedures distribute security, whereas allowing the execution of arbitrary SQL presented by any client doesn't - sorry, but that's just silly; and since it appears to be your thrust, but I can't believe it's what you actually mean, I think you need to clarify even more than you've attempted to.

    PS. Jesus... this whole damn comment interface is a steaming great pile of crap! "Whoops - too slow...?" On the Preview button? Every time I change what I'm saying, no matter that being conscientious and previewing, I saw it the first time...? The Submit button coming immediately between the textbox and the Preview button in tab order, so that you're one finger fumble away from submitting an incomplete comment...?

    shakes head in resigned sadness Sadly, it appears that worsethanfailure.com is determined to eat its own dogshit.

    Addendum (2007-04-18 00:31): (And yes, the metaphor abuse is intentional.)

  • (cs)

    I am a big surly man working at a government department who has been in IT far too long. I also believe in saying as little as when there is nothing to be said and I usually consider idle talk (not posts, though) a waste of time.

    However, having stated the above, there is no excuse for the kind of impolite, deragatory and dismissive behaviour which is described in the last part of the OP.

    I believe that Tris was treated by Luke the way he was because there were internal departmental politics involved. Possibly Tris visit was imposed on Luke's section of the department by another section and Luke therefore was just witing to f--- Tris off in order to be able give a disfavorable, dismissive report on the assistance Tris visit has provided.

  • (cs) in reply to Gsquared
    Gsquared:
    JRoyale:
    snoofle:
    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

    Who needs stored procedures?

    And I take it you've never heard of xp_cmdshell

    People making comments like this one should research the subject just a tiny bit first. xp_cmdshell IS a stored procedure, in the master database.

    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.

    < Snip >

    So what: you can still run a scheduled OS batch job issuing the "backup database" command. Of that is not very secure either because you need to have the password for the user sa in plain in the batch script. But that's okay as long as the departmental security regulations are followed ......

  • (cs) in reply to darin
    darin:
    skippy:
    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?
    No sarcasm. I honestly don't know what stored procedures are. I'm an engineer, not a database admin. It sounds like code that is stored in the database itself, but why put the code there instead of in the application? Doesn't that horribly complicate source code control and management?

    Actually, if you are using a clear design strategy, then stored procedures do not add unnecessary complication. I'm afraid the Wikipedia article linked in a previous response sort of muddies the issue, since it suggests using SPs for business logic. Don't. Business logic should be maintained in well-defined object code in program source.

    Stored procedures are best suited for tasks that require data-intensive activities that require high familiarity with the data structure itself. Running inside the DB, they have ideal access to the data without the added network traffic of multiple requests. Since they are stored as data themselves, they can be examined or modified with the same interfaces used for structuring the data they work on.

    Stored procedures form one of the tiers of 3-tier applications:

    1. Presentation and user interface:

    Logic here is focused on mediating interactions with the user: Presenting information in a manner that makes sense in the context of the user's field, and allowing it's manipulation, likewise in the context of the user's field of expertise.

    1. Business Rules and program logic:

    This is the realm of traditional "source code". Here the program organization and logic should reflect how different parts of the overall system interact. Object-oriented approaches can be very helpful in doing this.

    1. Data layer:

    This layer is where the data as data gets manipulated. The focus here is on translating the requests for information or commands to update into the structure in which data is stored.

    or, to put it another way: UI: information Business layer: processing, translation between data and information Data: data storage schema, retrieval, and manipulation

  • (cs) in reply to Flarelocke
    Flarelocke:
    It seems to me that this was inside a highly-classified environment, that Tris was there in gross violation of a huge number of security procedures starting with his mere presence, and that Luke was doing what he did under serious protest. When Tris suggested violating yet another security procedure (and, in having the disk on him, revealed one he didn't even know about), Luke took a principled stand for national security and kicked him out.

    If it was such an environment, Luke and his bosses seven levels up would be fired (and most of those prosecuted) and the next three's careers would be over if those even higher up learned of the gross malfeasance.

    I don't know - I assume Alex would have mentioned if there had been this kind of security rules at the site. Of course, bringing a (fresh) consultant into the organization without giving him a security briefing is a WTF in itself if the need for security is that high. Leaving him then in the server room unattended then is an even bigger WTF in that case. But again, that could have been intentionally done by Luke in order to be able to report to his manager that he had to eject Tris because of a perceived security violation (see also my earlier post on office polictics on this).

    Addendum (2007-04-18 02:55): I am continuing my post after thinking about it a little bit:

    Maybe I am stating the obvious for a lot of readers of this forum: If one section of the organization (e.g. IT development) imposes a consultant on another section of the organization (e.g. IT operations) and that consultant gets ejected from a core security area due to a blatant security violation then that is of course an "egg on the face" of the the first section and an unmitigated victory over interference for the second part of the organization. This is more true for larger organizations and triple so for governmental departments.

    You wouldn't believe the infighting that is very often going on in large government department's IT organizations.

    In that context Luke may very well have been a "hitman" ordered to behave as he did. And it is obvious that Tris was (in-)advertently physically intimidated by Luke right from the start.

  • error27 (unregistered) in reply to Zygo

    On the one hand I'm everything you wrote is true. On the other hand, was it you who setup the backup plan here?

  • (cs) in reply to SomeCoder
    SomeCoder:
    And this is a major WTF because when someone suggests something you don't think is a good idea or want to do your reaction should NOT be to say "GET OUT!!!" You should explain to them the security measures (even if those are a WTF in and of themselves and in this case, they certainly are).

    I don't really trust the anecdote. It's clear that the DBA was scared beyond belief, and scared people don't recall things very clearly.

    Maybe Thuggee-man saw the DBA pissing himself in fear and decided to protect the servers from an influx of urine?

  • Scoldog (unregistered) in reply to wiregoat
    wiregoat:
    A Volgan government perhaps. Anyone see the movie Idiocracy? I think its what SQL servers want.
    For a second there, I thought you meant Vogon.

    Did someone feed Luke's grandmother to the Ravenous Bugblatter Beast of Traal that morning?

  • Laurence (unregistered) in reply to Stupidumb

    I'm sure i'll get slammed for this but doesn't volume shadow copy crate a virtual parked copy of sql server 2005 to back up? I thought that was the point of it?

  • A (unregistered) in reply to JRoyale

    Is disabled for a reason!

  • (cs) in reply to sir_flexalot
    sir_flexalot:
    The WTF is that they left a consultant/etc unattended in the server room for any length of time.

    number 1 WTF here. A new consultant gets ALONE into the Server Room and logs on to the Server with ADMINISTRATIVE privileges? Who gave him the server account and DB passwords? Lots of Idiocy-related WTF here (and then someone blame about "security policy"... !!!)

    Yes, I agree with all "politics" related issues here (been there, done that)...

  • J (unregistered)

    What are the odds that the security consultant was Luke? :P

  • Jeltz (unregistered) in reply to luke727
    luke727:
    I'm sure Luke had a good reason for behaving like that.

    Maybe he had a good reason, but the greatest problem here is lack of communication. He should have told the consultant why he isn't allowed to reinstall the procedures. If you don't tell people what they are doing wrong you don't give them any chance. Maybe the consultant didn't understand that reinstalling them would violate a security policy.

    It was just a suggestion. It would have been something totally different if he had went ahead installing the procedures without asking first. Then the right thing could be to just send him out with a "get out!".

  • Noah Slater (unregistered)

    This story is clearly bullshit. Made up.

    This simply wouldn’t happen.

  • SnapShot (unregistered) in reply to gman
    gman:
    sammy:
    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.

    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?

    Easily, because you left out:

    "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.

    No, they are generally not there for a (good) reason. They are there because PHB need a reason to justify their existence. Or worse, a A+ Certified tech tells his boss about something he read on the interwebs (OMG, stored procedures are BAD!) and a new security requirement is implemented before COB.

    You are right, however, if the client says you have to do it one way you do it one way even if the client has survived through their government career (and reached the peak of their success as a terminal GS-13) by hiding their utter and complete incompetence behind a rude, unwashed, and potentially violent facade.

    P.S. The "real reason" he was escorted out of the building was for sneaking recordable media (the SQL Server CDROM) into a secure government facility (yes, you know and I know that that there was no CD-R on the server, but requirements are requirements...)

  • SnapShot (unregistered) in reply to Noah Slater
    Noah Slater:
    This story is clearly bullshit. Made up.

    This simply wouldn’t happen.

    Never worked as a DoD contractor have you?

  • FP (unregistered) in reply to Laurence
    Laurence:
    I'm sure i'll get slammed for this but doesn't volume shadow copy crate a virtual parked copy of sql server 2005 to back up? I thought that was the point of it?

    For a shadow copy the files must be in a consistent state. Either the files are closed or the application with open files knows the volume shadow copy is started and makes sure all the (open) files are consistent before the virtual snapshot is made.

    Suppose you open a file and write the first part and at that time a shadow copy is requested. You go ahead writing your data while the copy program happily creates a backup of only part of your file.

    Same with the db-server, it can be in the midst of a file write and if the server is not VSS-aware you create a backup of an inconsistent state of your database.

  • (cs)

    Looks like the BOFH is out of a job. What's the use of disabling the panic button if the victim prefers a dose of halon over the security guards?

  • (cs) in reply to dkf
    dkf:
    Reminds me of going long distance with Google Maps, especially stage 9...

    LOL!

  • (cs) in reply to darin
    darin:
    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.

    You're absolutely right. Oh wait, I forgot, most corporations try not to use Access for their enterprise database.

  • (cs)

    Everybody knows how to do it without stored procedures, right?

    copy con DoBackup.bat osql -S ImpData -U sa -P password "BACKUP DATABASE MyImportantData TO DISK=C:\Backups\MainDataBackup.dat" osql -S ImpData -U sa -P password "BACKUP LOG MyImportantData to DISK=C:\Backups\MainDataBackup.log" ^

    Then create a scheduled task to run the above batch file every night.

    This has to be more secure than stored procs, doesn't it? :-)

  • (cs) in reply to KenW
    KenW:
    Everybody knows how to do it without stored procedures, right?

    copy con DoBackup.bat osql -S ImpData -U sa -P password "BACKUP DATABASE MyImportantData TO DISK=C:\Backups\MainDataBackup.dat" osql -S ImpData -U sa -P password "BACKUP LOG MyImportantData to DISK=C:\Backups\MainDataBackup.log" ^

    Then create a scheduled task to run the above batch file every night.

    This has to be more secure than stored procs, doesn't it? :-)

    For bonus points, forget to back up the contents of the C:\Backups\ directory.

    For more bonus points, forget to use WITH INIT. (Oh, wait...)

  • Milkshake (unregistered) in reply to SnapShot
    SnapShot:
    Noah Slater:
    This story is clearly bullshit. Made up.

    This simply wouldn’t happen.

    Never worked as a DoD contractor have you?

    My thoughts, exactly. Hell, I can look out my door, right now, and see people who fit that profile.

  • (cs) in reply to sir_flexalot

    sir_flexalot:"Furthermore, rackmount monitors don't pull out...were they using an lcd and a keyboard on a tray?"

    Probably. Furthermore, I'd bet that tray was mounted to the rack, and the monitor to the tray, hence, the monitor is rack mounted, and pulls out.

  • (cs) in reply to Noah Slater
    Noah Slater:
    This story is clearly bullshit. Made up.

    This simply wouldn’t happen.

    On the contrary, this sort of thing happens far too often.

    In my (sadly not-so-limited experience), pretty much any time two departments have objectives that overlap in any way, shape or form, you'll get this kind of politics - usually until one side establishes dominance/priority over the other.

Leave a comment on “Government Department”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article