Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Block the DBA? Expand / Collapse
Author
Message
Posted Saturday, January 25, 2003 12:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/letsblockthedba.asp



Robert W. Marda
SQL Programmer
Ipreo
Post #9519
Posted Sunday, January 26, 2003 6:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 27, 2008 9:40 PM
Points: 19, Visits: 3
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
www.queryplan.com


Chris Cubley
www.queryplan.com



Chris Cubley
www.queryplan.com
Post #52437
Posted Sunday, January 26, 2003 9:36 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 8:57 AM
Points: 6,634, Visits: 1,872
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #52438
Posted Monday, January 27, 2003 3:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:11 AM
Points: 7,010, Visits: 8,461
Great article. Especialy the conclusion, wich IMO should be put en a red colour and an extra bold font.



Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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 but most of the time this is me
Post #52439
Posted Monday, January 27, 2003 3:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:44 AM
Points: 2,902, Visits: 1,817
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"!





LinkedIn Profile
Newbie on www.simple-talk.com
Post #52440
Posted Monday, January 27, 2003 4:07 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Yesterday @ 12:49 PM
Points: 8,369, Visits: 739
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.




Post #52441
Posted Monday, January 27, 2003 4:40 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 9:16 AM
Points: 6,784, Visits: 1,895
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/




Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #52442
Posted Monday, January 27, 2003 8:14 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #52443
Posted Tuesday, January 28, 2003 10:31 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, Visits: 1
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
www.chriskempster.com




Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Post #52444
Posted Wednesday, January 29, 2003 1:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:44 AM
Points: 2,902, Visits: 1,817
Actually your article highlights a need that isn't satisfied via legitemate means i.e. triggers on system tables.

Role on YUKON.



LinkedIn Profile
Newbie on www.simple-talk.com
Post #52445
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse