FAQ
Hello all,


I was asked to look into Oracle database auditing capabilities to flag
any activity done in the database outside the normal use of an
application. I'm reading the Oracle manual for starters, but can any of
you send me some recommendations, white papers I can look at or any
third-party tools that will do this?


Thanks.


Abraham Guerra.
Oracle DBA
American Family Insurance.

Search Discussions

  • William B Ferguson at Jul 28, 2006 at 7:13 pm
    TOAD (maybe with the DBA option?) actually makes it extremely easy, though
    there's an awful lot than can be audited.

    As an experiment, I went through and activated all auditing on everything
    in a development database, and I only have 4 people using this database. I
    was generating around 1 GB of auditing data each day! Needless to say, I
    turned off auditing after a few weeks, as I was getting tired of
    truncating the audit table.

    I figured I'll wait and see what is 'officially' recommended by the
    'Security Group' (who don't know databases), since they so far have only
    recommened enabling auditing, but no details on what to audit or how.

    Bill Ferguson
    U.S. Geological Survey - Minerals Information Team
    PO Box 25046, MS-750
    Denver Federal Center
    Denver, Colorado 80225
    Voice (303)236-8747 ext. 321 Fax (303)236-4208
    ~ Think on a grand scale, start to implement on a small scale ~

    "Guerra, Abraham J"
    Sent by: oracle-l-bounce_at_freelists.org
    07/28/2006 09:47 AM
    Please respond to
    AGUERRA_at_amfam.com

    To

    cc

    Subject
    Oracle Auditing Recommendations

    Hello all,


    I was asked to look into Oracle database auditing capabilities to flag any
    activity done in the database outside the normal use of an application.
    I'm reading the Oracle manual for starters, but can any of you send me
    some recommendations, white papers I can look at or any third-party tools
    that will do this?


    Thanks.


    Abraham Guerra.
    Oracle DBA
    American Family Insurance.
  • Rjamya at Aug 7, 2006 at 5:38 pm
    I guess you do know what is meant ny "outside the normal use of an
    application". I would get confised on such a generic recommendation
    from a highly paid auditor.

    Actually you don't want to audit everything, just audit the things you
    need. That way you can minimize the auditing you do.

    Raj

    Got RAC?
  • Alex Gorbachev at Aug 7, 2006 at 8:07 pm
    Well, from security point of view "audit anything you need" is wrong!
    Better is "audit all except what you know for sure is legitimate",
    which is exactly the standard phrase any auditor uses - "outside the
    normal use of an application". The problem is to find as much
    legitimate things as possible - as you mentioned.

    Worse yet, sometimes (should I say most of the time) it's not possible
    to figure that out in deterministic way. Often, you can only
    distinguish non-legitimate operations after playing with collected
    data in some BI tool. The level of collection... well it's fine-tuned
    as you need and as you go through your analysis. Like start with
    connection audit, add some DDL, more, add some DMLs on some object and
    etc. until you are comfortable.

    For example, if you take Audit Vault - it's actually just
    pre-configure audit data warehouse with OLAP tools configured to play
    with audit data. OK, maybe I am over-simplifying but that's an idea
    and it seems like a very good approach.

    Regarding a highly paid auditor - you don't pay for good advice - you
    pay for a stamp.;-) Usually, it doesn't make you any secure. For that
    you need to hire another guys and they won't give you any stamps.;-)

    Actually, it has nothing to do with just IT. Every auditor must have
    the balls to "stamp" its customers.

    2006/8/7, rjamya :
    I guess you do know what is meant ny "outside the normal use of an
    application". I would get confised on such a generic recommendation
    from a highly paid auditor.

    Actually you don't want to audit everything, just audit the things you
    need. That way you can minimize the auditing you do.

    Raj
    ----------------------------------------------
    Got RAC?
    --
    http://www.freelists.org/webpage/oracle-l

    --
    Best regards,
    Alex Gorbachev

    http://blog.oracloid.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Rodd Holman at Aug 7, 2006 at 8:34 pm
    Also remember, auditors are hired to find things wrong. If everything
    they find comes up good, then their supervisors question their diligence
    in their jobs. So every auditor needs to find something they can report
    just to show that they were doing their job. No auditor wants to be
    found eligible for the Enron audit team.

    Judge the severity of the audit issue with your management before you
    shell out big $$$ for a minor audit blip. It could just be someone
    leaving their "stamp" on the report.

    Alex Gorbachev wrote:
    Actually, it has nothing to do with just IT. Every auditor must have
    the balls to "stamp" its customers.
    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Aug 8, 2006 at 10:19 am
    On 8/7/06, Rodd Holman wrote:

    >
    Also remember, auditors are hired to find things wrong. If everything
    they find comes up good, then their supervisors question their diligence
    in their jobs. So every auditor needs to find something they can report
    just to show that they were doing their job. No auditor wants to be
    found eligible for the Enron audit team.
    Not true. Auditors are hired to verify and evidence that things are as
    people say they are. This is different from being hired to find things
    wrong. Or to use another analogy, this statement is equivalent to saying
    that DBAs are hired to prevent people from accessing data or code from being
    put into production. If a DBA allows people access or puts code into
    production without finding it lacking then their supervisors question their
    diligence.

    For sure Auditors are picky, beauracratic and irritating. This is a good
    thing given their role. They aren't out to find mistakes though. They are
    out to verify and evidence.
  • Rodd Holman at Aug 8, 2006 at 4:04 pm
    I'll agree with you for the most part. However,
    when an auditor comes in and reports a discrepancy in that
    the DBA's have the SYS password as a problem, I
    have to say that's "putting a stamp". How else do
    you create the database if you don't know and give it
    the sys password.

    Yes, this was a real life audit example.
    The auditor who was clueless about what a DBA was
    or did, had this checklist of items and just lumped
    DBA's in as users and since we knew how to get
    at the base level of the DB we were considered an
    audit risk. We all volunteered to give up the
    password and go home. Our boss wasn't impressed.

    Niall Litchfield wrote:
    On 8/7/06, Rodd Holman wrote:


    Also remember, auditors are hired to find things wrong. If everything
    they find comes up good, then their supervisors question their diligence
    in their jobs. So every auditor needs to find something they can report
    just to show that they were doing their job. No auditor wants to be
    found eligible for the Enron audit team.

    Not true. Auditors are hired to verify and evidence that things are as
    people say they are. This is different from being hired to find things
    wrong. Or to use another analogy, this statement is equivalent to saying
    that DBAs are hired to prevent people from accessing data or code from
    being
    put into production. If a DBA allows people access or puts code into
    production without finding it lacking then their supervisors question their
    diligence.

    For sure Auditors are picky, beauracratic and irritating. This is a good
    thing given their role. They aren't out to find mistakes though. They are
    out to verify and evidence.
    --
    http://www.freelists.org/webpage/oracle-l
  • Alex Gorbachev at Aug 8, 2006 at 4:14 pm
    Security Vault gives you the possibility to limit sys privileges. :)
    Interesting solution but old as this world. There is another
    super-user and it controls data access while sys user is for DBAs to
    stop/start/backup/troubleshoot whatever.
    Looks like a security system with two keys that should be turned at
    the same time to open the lock. :-)

    2006/8/8, Rodd Holman :
    I'll agree with you for the most part. However,
    when an auditor comes in and reports a discrepancy in that
    the DBA's have the SYS password as a problem, I
    have to say that's "putting a stamp". How else do
    you create the database if you don't know and give it
    the sys password.

    Yes, this was a real life audit example.
    The auditor who was clueless about what a DBA was
    or did, had this checklist of items and just lumped
    DBA's in as users and since we knew how to get
    at the base level of the DB we were considered an
    audit risk. We all volunteered to give up the
    password and go home. Our boss wasn't impressed.
    --
    Best regards,
    Alex Gorbachev

    http://blog.oracloid.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Aug 8, 2006 at 4:28 pm
    my reaction depends on at least 3 things. was it a problem or risk?
    its certainly a risk. how many people know the password?is use of the
    privilege recorded?
    On 8/8/06, Rodd Holman wrote:
    I'll agree with you for the most part. However,
    when an auditor comes in and reports a discrepancy in that
    the DBA's have the SYS password as a problem, I
    have to say that's "putting a stamp". How else do
    you create the database if you don't know and give it
    the sys password.

    Yes, this was a real life audit example.
    The auditor who was clueless about what a DBA was
    or did, had this checklist of items and just lumped
    DBA's in as users and since we knew how to get
    at the base level of the DB we were considered an
    audit risk. We all volunteered to give up the
    password and go home. Our boss wasn't impressed.

    Niall Litchfield wrote:
    On 8/7/06, Rodd Holman wrote:

    Also remember, auditors are hired to find things wrong. If everything
    they find comes up good, then their supervisors question their diligence
    in their jobs. So every auditor needs to find something they can report
    just to show that they were doing their job. No auditor wants to be
    found eligible for the Enron audit team.

    Not true. Auditors are hired to verify and evidence that things are as
    people say they are. This is different from being hired to find things
    wrong. Or to use another analogy, this statement is equivalent to saying
    that DBAs are hired to prevent people from accessing data or code from
    being
    put into production. If a DBA allows people access or puts code into
    production without finding it lacking then their supervisors question their
    diligence.

    For sure Auditors are picky, beauracratic and irritating. This is a good
    thing given their role. They aren't out to find mistakes though. They are
    out to verify and evidence.
    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info
    --
    http://www.freelists.org/webpage/oracle-l
  • Rodd Holman at Aug 8, 2006 at 5:01 pm
    It was a risk, senior management read it as a problem.
    I'm sure that's not a surprise to anyone. We had to
    go through some detailed explanations with the C-level
    execs about what we did as DBA's and why we needed
    the password (actually our boss got that fun task). :)
    We're a group of 5 DBA's and access as SYS or
    oracle (at the unix level) is recorded. We don't
    get root that's reserved for SA's. That was another
    dance our boss had to do also. SA's having
    root access to the servers was another item on
    the report. :)

    Yes, knowing the password is a risk.
    Having access to the server room is a risk.
    Crossing the street is a risk. Our job is not
    risk avoidance, but risk management. Assessing the
    level of risk vs. the cost of mitigating work arounds.

    Niall Litchfield wrote:
    my reaction depends on at least 3 things. was it a problem or risk?
    its certainly a risk. how many people know the password?is use of the
    privilege recorded?
    On 8/8/06, Rodd Holman wrote:

    I'll agree with you for the most part. However,
    when an auditor comes in and reports a discrepancy in that
    the DBA's have the SYS password as a problem, I
    have to say that's "putting a stamp". How else do
    you create the database if you don't know and give it
    the sys password.

    Yes, this was a real life audit example.
    The auditor who was clueless about what a DBA was
    or did, had this checklist of items and just lumped
    DBA's in as users and since we knew how to get
    at the base level of the DB we were considered an
    audit risk. We all volunteered to give up the
    password and go home. Our boss wasn't impressed.

    Niall Litchfield wrote:
    On 8/7/06, Rodd Holman wrote:
    --
    http://www.freelists.org/webpage/oracle-l
  • Wolfson Larry - lwolfs at Aug 7, 2006 at 9:57 pm
    Got this from client:

    "Related to the XXX Account used to link the Application to the DB, how
    can we get a log to see, if this Account has been used from anybody else
    beside the application itself? The Auditors are asking for a proof, that
    this account has not been used to access the DB for any necessary
    support activity."

    I looked at AUD$ thinking I'd see the osusers/servers and see if
    any weren't coming from valid osusers/servers. But all I saw was
    userhost and that not populated.

    Any ideas?

    Thanks
    Larry

    \

    The information contained in this communication is confidential, is
    intended only for the use of the recipient named above, and may be
    legally privileged.

    If the reader of this message is not the intended recipient, you are
    hereby notified that any dissemination, distribution or copying of this
    communication is strictly prohibited.

    If you have received this communication in error, please resend this
    communication to the sender and delete the original message or any copy
    of it from your computer system.

    Thank you.
  • Alex Gorbachev at Aug 8, 2006 at 8:19 am
    You should have a look at DBA_AUDIT_SESSION. Obviously, AUDIT_TRAIL
    must be set to DB.

    2006/8/7, Wolfson Larry - lwolfs :
    I looked at AUD$ thinking I'd see the osusers/servers and see if
    any weren't coming from valid osusers/servers. But all I saw was
    userhost and that not populated.
    --
    Best regards,
    Alex Gorbachev

    http://blog.oracloid.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Rodd Holman at Aug 8, 2006 at 5:27 pm
    We handle both operations and development.
    We do a lot of cloning and creating of the db's
    for dev and testing environments. As far as sys
    goes, most of the time we go in as the oracle user
    and just / as sysdba. This has the same
    security implication as SYS/password as sysdba.

    Normally we are only in as SYS during create/clone
    and startup and shutdown operations. It's
    actually VERY sparingly used by the DBA group.
    We're a rather paranoid bunch about going in
    with that much access ourselves. It's too
    easy to do something damaging.

    Terrian, Tom (Contractor) (J6D) wrote:
    Curious, since we lock and expire the sys account on all of our
    databases, what reason did you give your bosses as to why you needed the
    sys password?

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Rodd Holman
    Sent: Tuesday, August 08, 2006 1:02 PM
    To: Niall Litchfield
    Cc: gorbyx_at_gmail.com; rjamya_at_gmail.com; AGUERRA_at_amfam.com;
    oracle-l_at_freelists.org
    Subject: Re: Oracle Auditing Recommendations

    It was a risk, senior management read it as a problem.
    I'm sure that's not a surprise to anyone. We had to
    go through some detailed explanations with the C-level
    execs about what we did as DBA's and why we needed
    the password (actually our boss got that fun task). :)
    We're a group of 5 DBA's and access as SYS or
    oracle (at the unix level) is recorded. We don't
    get root that's reserved for SA's. That was another
    dance our boss had to do also. SA's having
    root access to the servers was another item on
    the report. :)

    Yes, knowing the password is a risk.
    Having access to the server room is a risk.
    Crossing the street is a risk. Our job is not
    risk avoidance, but risk management. Assessing the
    level of risk vs. the cost of mitigating work arounds.
    --
    http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Aug 8, 2006 at 5:46 pm

    On 8/8/06, Rodd Holman wrote:
    We handle both operations and development.
    That should probably have been on there as well, with a recommendation to
    split duties and increase staff. I bet it wasn't though :)
  • Rodd Holman at Aug 8, 2006 at 6:32 pm
    Increase staff. What is that term?
    I'm not sure I understand such language.;)
    I'm sure the position approval folks don't.

    Niall Litchfield wrote:
    On 8/8/06, Rodd Holman wrote:


    We handle both operations and development.


    That should probably have been on there as well, with a recommendation to
    split duties and increase staff. I bet it wasn't though :)
    --
    http://www.freelists.org/webpage/oracle-l
  • Wolfson Larry - lwolfs at Aug 8, 2006 at 8:01 pm
    Alex,

    Thanks a lot. Probably should have had another thread, just
    forgot to change subject.

    Ran
    SELECT DISTINCT USERNAME

    , OS_USERNAME, COUNT(*)

    FROM DBA_AUDIT_SESSION

    WHERE USERNAME IN ('XXX','yyy'
    )
    GROUP BY USERNAME,OS_USERNAME

    ;

    and got what I wanted. Can't seem to get userhost info.
    Found jbdc connections coming in from app server as "oracle"
    It's an older version of ARIBA and I don't know how the jbdc part works.
    Rather see the app connections as something like "buyerX"

    -----Original Message-----
    From: Alex Gorbachev
    Sent: Tuesday, August 08, 2006 3:19 AM
    To: Wolfson Larry - lwolfs
    Cc: oracle-l_at_freelists.org
    Subject: Re: Oracle Auditing Recommendations

    You should have a look at DBA_AUDIT_SESSION. Obviously, AUDIT_TRAIL must
    be set to DB.

    2006/8/7, Wolfson Larry - lwolfs :
    I looked at AUD$ thinking I'd see the osusers/servers and see
    if any weren't coming from valid osusers/servers. But all I saw was
    userhost and that not populated.
    --
    Best regards,
    Alex Gorbachev

    http://blog.oracloid.com
    *************************************************************************
    The information contained in this communication is confidential, is
    intended only for the use of the recipient named above, and may be
    legally privileged.

    If the reader of this message is not the intended recipient, you are
    hereby notified that any dissemination, distribution or copying of this
    communication is strictly prohibited.

    If you have received this communication in error, please resend this
    communication to the sender and delete the original message or any copy
    of it from your computer system.

    Thank you.
    *************************************************************************
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 28, '06 at 3:47p
activeAug 8, '06 at 8:01p
posts16
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase