Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2007
    Posts
    7

    Unanswered: Validating stored proc code

    This is for SQL Server 2005

    The problem: I need to validate all stored procedures on my server that they have not been altered after the application has been created.

    Our server installation creates the user stored procedures for the application. Once created, I need a way to validate that the proc code has not been altered.

    Previously, we were validating against the value of stats_schema_ver and crdate in the sysobjects table for the given stored procedure. During installation we would create a hash value of the crdate and stats_schema_Ver values for all our user defined procs. Then during validation, we would recreate the hash value and compare to that created during installation. If they differed, we knew that the proc code had been altered in some way.

    Any alteration of the stored proc would update the stats_schema_ver- if the proc was dropped and recreated, it would update the crdate.

    In 2005, the stats_shema_ver value is no longer updated when the proc is altered.

    Perhaps this method was completely off-base to begin with...

    So, how *does* one verify after installation that no proc code has been altered since installation?

    The other option was to create a hash of the proc text definition- but that seems absolutely tedious to rehash the current proc definition every time the proc is accessed. There's also the problem that if you use the "with encryption" directive, it's no longer easy to get the text definition of the proc back out.

    Appreciate any general thoughts or specific feedback.

    Cheers,
    Eric

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    there is a modify_date column in sys.objects, you may be able to use that since you are on 2005.

  3. #3
    Join Date
    Jan 2007
    Posts
    7
    Quote Originally Posted by jezemine
    there is a modify_date column in sys.objects, you may be able to use that since you are on 2005.
    My fault, meant to state that the I looked into using the modify_date field, however, this date changes by simply running sp_recompile on the proc... so there's a liklihood of false positives using this method...

    Should have stated that in my initial post.

    Eric

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    how about writing a DDL trigger that throws an exception if you try to modify any proc?

    It's no good for monitoring whether any changes occured, but it would prevent anyone from modifying them, unless they drop the DDL trigger first...

  5. #5
    Join Date
    Jan 2007
    Posts
    7
    Quote Originally Posted by jezemine
    how about writing a DDL trigger that throws an exception if you try to modify any proc?

    It's no good for monitoring whether any changes occured, but it would prevent anyone from modifying them, unless they drop the DDL trigger first...
    This looks promising... thanks. Definitely have the issue of dropping the trigger or even suspending the trigger (I'm guessing you can suspend DDL triggers like you can regular triggers)... gives me at least another option to look at- thanks.

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    here's another idea, for fun:

    1. when you ship, run this little app of mine called scriptdb.exe and check the result into source control: http://www.elsasoft.org/tools.htm

    2. then when you want to see if the procs changed in your production db, run scriptdb.exe on it and then diff the result with what you generated in step 1.

    EDIT: step 2 could be automated because scriptdb.exe is a console app.
    Last edited by jezemine; 05-02-07 at 16:28.

  7. #7
    Join Date
    Jan 2007
    Posts
    7
    The source control option is interesting, but wouldn't work very well in our customer environment...

    I've got the basic DDL trigger control working fairly well. I'm using it to protect against alter/drop for all procs, functions and triggers. However, the major hole I see here is simply, that even with the DDL triggers, a user can simply run "Disable Trigger <trigger_name>" on a DML trigger to by-pass any trigger code that is being protected in the first place. Kind of nullifies things in a hurry.

    I have not found a way of protecting against simply disabling DML triggers... there does not seem to be a DDL trigger that can be written to prevent disabling a DML trigger...

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    why are you so worried anyway? does your client have an extreme habit of poking around and messing things up?

    maybe you can just put it in the contract: "if you touch any of this, you void the support contract." something like that.

  9. #9
    Join Date
    Jan 2007
    Posts
    7
    Quote Originally Posted by jezemine
    why are you so worried anyway? does your client have an extreme habit of poking around and messing things up?

    maybe you can just put it in the contract: "if you touch any of this, you void the support contract." something like that.
    Well, this isn't exactly a standard client-vendor relationship... can't really go into all the why's and where for's but it's such an application that must be protected as much as possible, even from rogue "administrators" - yes, I realize how silly that sounds. If you're an admin, you get the keys to the front door... it's all about layers.

    My biggest concern was that 2005 makes it quite easy to disable triggers on tables- it's a right-click menu option. However, I just discovered that it's running the disable as an "alter table" statement- so I created another DDL trigger to prevent alter table commands and voila... yes, you can still run the straight disable trigger <trigger_name> on <table_name> from query analzyer, but it's another step in the process...

    Thanks again for the help.

    Eric

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you might want to create all your code WITH ENCRYPTION then - it might lessen the temptation to fiddle.

  11. #11
    Join Date
    Jan 2007
    Posts
    7
    Quote Originally Posted by jezemine
    you might want to create all your code WITH ENCRYPTION then - it might lessen the temptation to fiddle.
    That's a definite- already doing so...

  12. #12
    Join Date
    Dec 2002
    Posts
    1,245
    Is it possible to derive a hash value from the text of your SPs and store this somewhere? The you could compare that hash with a similarly derived hash at a later date.

    I have to say though, if the vendor/client relationship has deteriorated this far, there are other things to be concerned about.

    Regards,

    hmscott
    Have you hugged your backup today?

  13. #13
    Join Date
    Jan 2007
    Posts
    7
    Quote Originally Posted by hmscott
    Is it possible to derive a hash value from the text of your SPs and store this somewhere? The you could compare that hash with a similarly derived hash at a later date.

    I have to say though, if the vendor/client relationship has deteriorated this far, there are other things to be concerned about.

    Regards,

    hmscott
    That was the original method we were to employ, however, you can no longer easily do this if you are using the 'with encryption' directive...

    And it's not the client-vendor relationship so much as a high-visibility/risk area that must simultaneously be available over many distributed environments and yet locked down as tight as possible with the ability to validate at any time that nothing has changed.

    e

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •