Audit Database Changes in the Real World

  • Comments posted to this topic are about the item Audit Database Changes in the Real World

  • Nice one. The success of the thing totally depends on the DBA. He or she has to take out time to complete the documentation after implementing the changes. 🙂

  • Nice Article,

    I wish to set up a solution as the one that you explain.

    I am interested to see the code which you have create to inspire me on SQL Server.

    Thx.

  • Why do all these articles assume that the DBA can be trusted?

  • Excellent article TJ. Congratulations on a well ordered process.

    Any chance of posting the code for the procs that start the traces? I'm curious about some of the details of how you did that.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • A very nice integration of SOX with your change control process. I especially liked step 11 with the yelling and finger pointing. I certainly hope that this is documented for the auditors as part of the official process.

  • Grant Fritchey (6/11/2008)


    Excellent article TJ. Congratulations on a well ordered process.

    Any chance of posting the code for the procs that start the traces? I'm curious about some of the details of how you did that.

    I second the comment and the question. It would be nice to have the scripts for all the objects.

  • msbasssinger (6/11/2008)


    I think I have a less complicated approach to audit trails.

    Isn't yours a different type of auditing i.e. changes to data as opposed to what the article is about (changes to database objects) ?

  • Yes. I grabbed the wrong audit article. My bad, and my apologies.

    Time to revist the coffee pot. 🙂

  • tonyf (6/11/2008)


    Why do all these articles assume that the DBA can be trusted?

    In this articles case, i am the DBA, and of course i can be trusted...

    Its a good point though. And a difficult one, when im the one creating the system to monitor myself. If there were more players in this puzzle, i would definitely defer to them to monitor me. But in my experience, it needed to be monitored prior to the other players coming on the field.

    This is one way. Obviously other policies and procedures can be implemented to assume that distrust and more effectively safegaurd the systems against those untrustworthy DBA's

  • What a great system! What are the requirements for the monitoring server and how many does yours monitor? Can it be done on the cheap with express or workgroup edition?

  • tonyf (6/11/2008)


    Why do all these articles assume that the DBA can be trusted?

    Trust has to be there and I believe most of the DBAs can be trusted.

  • magarity kerns (6/11/2008)


    What a great system! What are the requirements for the monitoring server and how many does yours monitor? Can it be done on the cheap with express or workgroup edition?

    Ive done this on a couple systems, and you are limited by hdd size as the data grows. you will have trace files taking up space. and you will have data files growing as you collect data. You need to keep that in mind as you create a similar system, cause once the file space shrinks, no monitoring occurs... and then you are left with your pants down, so to speak. nothing like having to explain to an auditor why you have a gap in your data collection...

    since you are running a server side trace, you will need to be able to fire off those procs to accomplish this. im not sure if these are available in express. to be honest, ive never used it. Maybe someone can answer that portion?

    best suggestion that i can give you is to try it out on whatever system you can get your hands on. even housing this on a prod system is an option, if you have no other sql box to put it on. And as time goes, you'll see if it needs to be moved. moving it is fairly easy to do, as well.

  • tonyf (6/11/2008)


    Why do all these articles assume that the DBA can be trusted?

    Because the DBA knows that independent auditors will eventually catch up with him/her. Maybe not this audit cycle, but someday. Since the DBA's pay and bonuses are a lot less likely to be oriented to benefit cheating, unlike, say, a star sales rep, the DBA is among the least likely to be untrustworthy.

    At some point there just isn't a next level of watchers to watch the watchers, so don't let the paranoia keep your organization from functioning.

  • magarity kerns (6/11/2008)


    tonyf (6/11/2008)


    Why do all these articles assume that the DBA can be trusted?

    Because the DBA knows that independent auditors will eventually catch up with him/her. Maybe not this audit cycle, but someday. Since the DBA's pay and bonuses are a lot less likely to be oriented to benefit cheating, unlike, say, a star sales rep, the DBA is among the least likely to be untrustworthy.

    At some point there just isn't a next level of watchers to watch the watchers, so don't let the paranoia keep your organization from functioning.

    great point. its what i have felt for a long time. I was so frustrated when the auditor told me that a system like this wouldnt help, cause i could edit the data. he said the same about a spreadsheet report, being that its not an acceptable form of data reporting, because its editable.

    so i really got his goose one day when he asked for a screenshot (assumed the best form of verification). I went to my virus software, and did a screenshot of the dates of files, then edited them in my image software, putting the date a few hundred years in the future... and it blew his mind that that was no longer a valid and safe form of reporting...

    it always comes back to trust. If i have something watching me, im more likely to be honest. the more monitoring that occurs, the better the higher ups will feel, and the auditors. and i am free to do my job, which i would do honestly in the first place...

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

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