Post reply

What do you look for in a Database Audit?

  • December 23, 2008 at 2:14 pm

    #94297

    I am curious to find out what your team does when it comes to database audit?

    Here are a list of questions from me:

    - Do you check schema only to make sure it conforms to best practice?

    - Do you check data against business rules?

    - Do you perform any standard check on function and stored procedures?

    I would like to hear more on this from this community.

    Thanks

    Rajib Bahar
    http://www.rajib-bahar.com
    http://www.twitter.com/rajib2k5
    http://www.youtube.com/icsql

  • December 26, 2008 at 9:26 am

    #917832

    Depends completely on the objectives of the audit

  • December 26, 2008 at 9:52 am

    #917840

    I agree with Taylor. It depends on what you're doing.

    Is this a technical audit? looking for adherence to best practices? Corporate standards? Something else?

  • December 26, 2008 at 10:38 am

    #917865

    rajib (12/23/2008)

    I am curious to find out what your team does when it comes to database audit?

    Here are a list of questions from me:

    - Do you check schema only to make sure it conforms to best practice?

    - Do you check data against business rules?

    - Do you perform any standard check on function and stored procedures?

    I would like to hear more on this from this community.

    Thanks

    Actually, if you haven't been doing those 3 things all along, you're likely in deep trouble in all 3 areas. 😉

    For an "accounting" or SEC audits, you need to have proof that you've been doing all 3 of those all along as well as proving that theres a viable audit trail for changes in the DDL, DML, or the data itself.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 26, 2008 at 10:59 am

    #917873

    I love the word "depends". 😀

    How about we assume that I'm looking for everything (3 things mentioned above as well as other items not listed there)? I am interested in learning what others have done in their projects. That's all.

    Rajib Bahar
    http://www.rajib-bahar.com
    http://www.twitter.com/rajib2k5
    http://www.youtube.com/icsql

  • December 26, 2008 at 11:25 am

    #917882

    It's not that we don't want to answer, but this could be a book's worth of writing. We're asking what the goals of the audit are?

    Most auditing is to determine what has happened or what things are changing. You've mentioned a few things that aren't in that area, but are more of a reivew.

    What's the purpose of your audit?

  • December 26, 2008 at 11:39 am

    #917888

    rajib (12/26/2008)

    I love the word "depends". 😀

    How about we assume that I'm looking for everything (3 things mentioned above as well as other items not listed there)? I am interested in learning what others have done in their projects. That's all.

    Then I believe it's going to boil down to what I said... those 3 things, a viable audit trail for all changes, and security. On the security side for things like SEC and SOX audits, you'll be asked to prove that not even DBA's can change the data without an audit trail being left. That's just about impossible unless you have a shop where the DBA's don't have SA access.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 26, 2008 at 2:39 pm

    #917939

    Steve Jones - Editor (12/26/2008)

    It's not that we don't want to answer, but this could be a book's worth of writing. We're asking what the goals of the audit are?

    Most auditing is to determine what has happened or what things are changing. You've mentioned a few things that aren't in that area, but are more of a reivew.

    What's the purpose of your audit?

    I asked this question to learn more from my peers. I want to learn how every industry does their audit on their sql server. I saw some good response on concerns and issues raised in sarbanes oxley standard. Similarly, I would like to learn about other industry regulation such as HIPAA and others. I hope this kind of open ended question is OK to ask in this SSC forum.

    Thanks

    Rajib Bahar
    http://www.rajib-bahar.com
    http://www.twitter.com/rajib2k5
    http://www.youtube.com/icsql

  • December 26, 2008 at 2:59 pm

    #917942

    No standard including SOX, RFR/fact, sas 70, hipaa, pci, MA 201 etc. Provide guidance on implementation. At the end of the day you dbas know your systems and weaknesses better than auditors. That said there are no requirements on how to secure or configure databases to be compliant with sox.

    Regardint the point of this thread, if you want to self assess across compliance requirements check out appdetective or ask me, I've dabbled in db auditing departments at my firm

  • December 26, 2008 at 5:10 pm

    #917954

    rajib (12/26/2008)

    I hope this kind of open ended question is OK to ask in this SSC forum.

    Thanks

    Absolutely... it does help folks understand if they know you know it's a very open question and that a specific answer isn't necessarily what you're after. You're just looking for discussion as to what some folks may have done.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 26, 2008 at 5:17 pm

    #917955

    There are some things I look for in security... are things like SSN's, Pins, Passwords, Account numbers, or even the "last 4 of the SSN" encrypted? Who has access to the encryption keys??? I emphasize "keys" because there should be more than 1. The answer should be "nobody, but person x has a sealed envelope and person y has the other and they're both in safes and the rules for getting them are stated in company policy #xyz."

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 27, 2008 at 6:28 am

    #918013

    check out appdetective or ask me, I've dabbled in db auditing departments at my firm

    I'll google appdetective soon. Do you have links to their website or any useful articles or demos?

    Rajib Bahar
    http://www.rajib-bahar.com
    http://www.twitter.com/rajib2k5
    http://www.youtube.com/icsql

  • December 27, 2008 at 12:54 pm

    #918070

    http://www.appsecinc.com/products/appdetective/

    It's fine to ask the question, but you haven't given guidance on the purpose of the audit. What are you trying to learn about? There isn't necessarily a guideline per industry on open ended audits. Jeff brings up things different than others, and different than some things you mentioned.

    However you haven't given a purpose behind what you're doing. Not learning about auditing, but what auditing? What type of audit?

  • December 28, 2008 at 10:14 pm

    #918252

    Steve Jones - Editor (12/27/2008)

    http://www.appsecinc.com/products/appdetective/

    It's fine to ask the question, but you haven't given guidance on the purpose of the audit. What are you trying to learn about? There isn't necessarily a guideline per industry on open ended audits. Jeff brings up things different than others, and different than some things you mentioned.

    However you haven't given a purpose behind what you're doing. Not learning about auditing, but what auditing? What type of audit?

    Steve, Thanks for the link to appsecinc. I appreciate it.

    Jeff, and others thanks for your earlier replies.

    I just wanted to generate a discussion without any scope on audits. If I say the purpose is x, and what I am trying to do is y then the replies will be limited to that. Let us assume, I did not define this. If there were threads related to this topic then I'll appreciate your pointers.

    Rajib Bahar
    http://www.rajib-bahar.com
    http://www.twitter.com/rajib2k5
    http://www.youtube.com/icsql

  • December 30, 2008 at 2:35 pm

    #919320

    Depends.

    😀

    We have several specific audits throughout the year.

    We have security audits where we check, document, fix any logins that have higher permissions than the security norm for the type of server (development, test, production).

    We have space audits where we check, document, fix any drive space issues.

    We constantly audit our backups where we (yep) check, document, fix any backup issues.

    We constantly 'audit' our server's jobs to check and fix any that might be failing.

    There are lots of other things we audit frequently. Some are done more frequently than others, some are self-chosen audits, some are required from higher up, some are both (security ones, which we also do without being tasked).

    -SQLBill

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

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