Block the DBA?

  • Robert W Marda

    SSChampion

    Points: 13413

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/letsblockthedba.asp

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • ccubley@queryplan.com

    SSC-Addicted

    Points: 411

    While its often fun to think about how you might block a problem DBA from screwing up a database, the methods presented in this article won't do much practical to address the issue. The solutions presented here could very well end up doing more harm than good. Microsoft specifically states that modifications to system tables are dangerous and should be avoided. Putting a trigger on a system table could have consequences that were not expected or intended.

    I realize that at the end of the article you state that the techniques presented should not be used on a production database and hold little practical value; however, the lead-in to the article does not present the things in this light. While learning the structure of the system tables is useful, this article does not demonstrate any practical use of some potentially useful information. To me, it is dangerous and irresponsible to present, even momentarily, these techniques as a plausible solution to a very real problem.

    Chris Cubley, MCSD

    http://www.queryplan.com

    Chris Cubley

    http://www.queryplan.com


    Chris Cubley
    www.queryplan.com

  • K. Brian Kelley

    SSC Guru

    Points: 114486

    As far as making a table read-only, one of the things you can do is create a separate filegroup and build that table in the filegroup. Then use the ALTER DATABASE command and set the filegroup to read-only.

    The one catch to this is that you may have to undo the read-only status prior to applying a service pack. Remove read-only, apply the service pack, and then reapply the read-only status would be the procedure there.

    As far as preventing tables from being dropped or users from being added, I think this goes back to trust. Just as you have to trust whoever is holding domain admin rights (or enterprise admin rights for that matter), you are going to have to trust your senior DBAs. Junior DBAs can have reduced permissions, but you'd expect your senior DBAs not to go making changes without careful planning.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • Johan Bijnens

    SSC Guru

    Points: 134310

    Great article. Especialy the conclusion, wich IMO should be put en a red colour and an extra bold font.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] but most of the time this is me

  • David.Poole

    SSC Guru

    Points: 75402

    I know you said it was wortst practice in your article, but the icy hand of DBA's past gripped my heart when I read about altering system tables.

    I am always nervous of changing catalogues, be they system or application catalogues unless I have a guarantee from the vendor that such changes can do no harm.

    You would have to have throrough documentation on what you had done and a role back procedure so that all the changes could be dropped prior to the application of a service pack then reinstigated after the service pack.

    The problem with altering system tables is that there is no guarantee that the changes will be applicable after an update i.e. MS change their methodology so that XTYPE is no longer used to validate system tables.

    When it comes to enforcing best practice I prefer the approach of "Do it right and I'll buy you a pint, do stupid things (more than once) and its the big stick with the nails in it for you my boy"!

  • Antares686

    SSC Guru

    Points: 125444

    Also, with triggers there are no guarantees they will fire on system tables and that they might not cause your server heartburn so to speak within system transactions. Good concepts and I have played with the system tables myself on test servers but they always end up getting you in trouble down the line. Or what works one time may cause issues the next.

  • Andy Warren

    SSC Guru

    Points: 119694

    Interesting stuff. Im not opposed to someone using system tables if they know what they are doing. The downside I see is not that something might break (hey, you changed it!) but that it may get undone by a service pack (will you remember to check and reapply your change) and that it you're doing something not expected/easily visible for the next dba to come along.

    I don't have a jr dba - idea kinda gives me chills! I think when you get to the point that you don't trust them, time for them to go. Note that I make the distinction between trust and worrying that they may make a mistake. Mistakes will happen, especially if you're learning. I think the thing to do is teach them to never do things without a recovery plan. Never never never. Maybe invest in LogExplorer too!

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Robert W Marda

    SSChampion

    Points: 13413

    I knew I was likely to get various negative comments to this article. It isn't inteneded to be as serious as the others I have published. Please forgive me for not making my warning at the beginning of the article as strong as the warning at the end of the article. My advice is to use this article as a learning experience and to not use these techniques in a production environment until you have exausted all other possible solutions (this should mean never, if you think you have exhausted all other possible solutions then you probably haven't tried hard enough).

    For those who have read some of my posts, you know I have no problem with modifying system tables in a development environment while exploring possible solutions and I have reaped the problems associated with doing things wrong.

    The only change to system tables I have made in a production environment is deleting a user from sysusers when the ID is not matched and I don't think I've done that recently since I discovered a system SP that will fix the login.

    And since you mentioned the unreliablility of triggers on system tables. The one described in the article worked reliably for me. I put an auditing trigger on sysprocesses in the master database. The trigger worked on other system tables but it didn't fire while on sysprocesses. It was there though, and even stayed after I shut down the SQL Server service and restarted it. I thought that when I switched xtype of sysprocesses from S to U that I might crash the server and even break it so that I would have to rebuild the server. That didn't happen, but the trigger on that system table would not fire. Please, don't try this on anything but a server you are willing to rebuild.

    Robert W. Marda

    SQL Programmer

    bigdough.com

    The world’s leading capital markets contact database and software platform.

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • ckempste

    SSCoach

    Points: 17983

    Hi

    Some very interesting thinking. I can thinking of a variety of senarios where some of these would apply but as the only dba, i can trust myself 😉

    Cheers

    Chris K

    http://www.chriskempster.com


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • David.Poole

    SSC Guru

    Points: 75402

    Actually your article highlights a need that isn't satisfied via legitemate means i.e. triggers on system tables.

    Role on YUKON.

  • vyaskn

    Old Hand

    Points: 344

    While you bring up some interesting points, a not-so-experienced DBA might be quick to implement these suggestions, without reading the 'conclusion' section.

    Eventually this type of techniques end up doing more harm than good, as they are not supported by Microsoft, and these changes interfere with the SQL Server's functionality. Results can be unpredictable.

    If you don't trust your DBA, you should keep them away from critical servers by restricting their access at the Windows and server level.

    HTH,

    Vyas

    http://vyaskn.tripod.com/


    HTH,
    Vyas
    SQL Server MVP
    http://vyaskn.tripod.com/

  • Robert W Marda

    SSChampion

    Points: 13413

    Yes, I think triggers on system tables can be useful in some cases. At the very least Microsoft could provide some triggers in an inactive form that could be activated should a DBA want them. The reason I tried to put a trigger on sysprocesses is because I developed a way to detect deadlocks and save the query of both processes involved in the deadlock. This would eliminate the need to review profiler data and to monitor for a certain period of time until a deadlock appeared.

    Even though I am unlikely to use these techniques, I do believe that used correctly and with caution they would not cause an adverse effect on a SQL Server. Take, for example the first technique described. SQL Server sets this flag every time you set up replication, so the technique is not doing something that should never be done it is simply doing it without setting up replication. If you try to drop a replicated table you will get the same error as when you use the technique I described in example 1.

    Also, these techniques can be used to avoid accidents. I think we have all made mistakes on production SQL Servers at one time or another.

    The placing of triggers on system tables should only be done as experiments since this is something that SQL Server does not do naturally under any conditions and so they will be very risky at this point. Who knows, if I'm lucky someone from Microsoft will read this article and consider its implications and allow those who want triggers on system triggers to put them there.

    Robert W. Marda

    SQL Programmer

    bigdough.com

    The world’s leading capital markets contact database and software platform.

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • mromm

    SSCarpal Tunnel

    Points: 4301

    Ladies and Gentlemen,

    Please do not be so serious. This article is entertaining - that's it. If a junior (or "SENIOR") DBA should take it seriously, the worst thing that could happen is that a production server gets burned to the ground. Not a big deal. Not as big as the Great Flood - for sure.

    There is one way of teaching: you show your students the "worst practice" and "prohibited areas", how to go aroubd there, then you teach them not to be kiddish and irresponsible. After all, we are free people, right? (AKA, "guns do not shoot by themselves"...)

    Michael

  • yousef Ekhtiari

    Ten Centuries

    Points: 1283

    is it possible to avoid dropping TRIGGERS ?

    --YOUSEF

  • Robert W Marda

    SSChampion

    Points: 13413

    You can use the ALTER TABLE command to disable and enable triggers. Also you can use sp_trigger created by Rodrigo G. Acosta. Its in the SQLServerCentral scripts area at: http://www.sqlservercentral.com/scripts/contributions/484.asp

    Robert W. Marda

    SQL Programmer

    bigdough.com

    The world’s leading capital markets contact database and software platform.

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply