1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Enable Oracle Auditing

Discussion in 'Server Administration and Options' started by noufal_y, Aug 23, 2009.

  1. noufal_y

    noufal_y Forum Advisor

    Messages:
    38
    Likes Received:
    0
    Trophy Points:
    80
    Hi,

    In our database i need to enable the Audit trial for Basic ALTER, CREATE, DROP, GRANT, REVOKE. There is one instance using for our application. Please help me to do this .
    I tried to enable it through OEM. But it was unsuccess.

    I am using OEM console to manage the DB.Now audit trial shows NONE.
    Please help me.
    Thanks in advance.

    Regards,
    Noufal Y
     
  2. apunhiran

    apunhiran Active Member

    Messages:
    7
    Likes Received:
    2
    Trophy Points:
    65
    Re: Auditing

    Hello,

    For enabling basic DDL auditing, assuming you are using oracle 10g.

    Step1: Setup the auditing system parameters:
    Code (Text):
    ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
    Restart the database to take the effect.

    Step2: As sys:
    Code (Text):
    audit grant any role by access;
    audit grant any object privilege by access;
    audit grant any privilege by access;
    If your "ALTER, CREATE, DROP" is with regards table then:
    Code (Text):
    audit CREATE ANY TABLE by access;
    audit alter any table by access;
    audit drop any table by access;
    Else for all DDL:
    Code (Text):
    AUDIT ALL BY ACCESS;
    For specific cases like index, procedure etc. you can:
    Code (Text):
    alter any <index/procedure/package..etc> by access.
    To check your current settings:
    Code (Text):
    SELECT * FROM DBA_STMT_AUDIT_OPTS;
    To check the audit:
    check table dba_audit_trail

    Regards
    Apun
     
    Naveen Rachapally and noufal_y like this.
  3. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    noufal_y likes this.
  4. noufal_y

    noufal_y Forum Advisor

    Messages:
    38
    Likes Received:
    0
    Trophy Points:
    80
    Thanks verymuch...will do it. bye
     
  5. jesthr@gmail.com

    jesthr@gmail.com Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    80
    Dear!

    I'm useing the Oracle 11g database.
    I have a requirement to auditing insert,update,delete and select activity on the table XXXXX.
    With only one exception to this applies not only to one Oracle user TJOHN.

    Audit I learned this:
    SQL> AUDIT SELECT,INSERT, UPDATE, DELETE ON XXXXX BY ACCESS WHENEVER SUCCESSFUL;

    How can I specify that the AUDIT does not apply to Oracle user TJOHN?

    Regards!

    Hine
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    By specifying all the users the policy DOES apply to:

    Code (SQL):

    audit SELECT, INSERT, UPDATE DELETE ON XXXXXXX BY bob, ed, al, waldo, clyde, farina;
     
    BY ACCESS audits everyone; as shown above you CAN specify which users to audit. You would simply generate a list of usernames absent TJOHN and use that in the audit statement.
     
  7. jesthr@gmail.com

    jesthr@gmail.com Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    80
    Yes, I understand,
    But I have 400 users, and almost every day someone new comes and who goes!
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Then the criteria for monitoring users is weak and poorly defined. There should be a limited number of users exempt from auditing; the rest should be audited. A simple query of the audit trail can be written to exclude the limited number of non-auditable users so the audit by access option should be the one you use.
     
  9. deeplydrink

    deeplydrink Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    it's not a good idea to use the db audit itself, the best solution maybe should be using third party software, for example mirroring the traffic of network swtich.

    Because, it may cause the performance issue if the audit was made by db itself
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    WHY is it not a good idea to use the database internal auditing?
     
  11. deeplydrink

    deeplydrink Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    use the db to audit itself, it's logically incorrect.I mean you want to audit all the operation of all db users including SYS,the option should be of out db scope.Because,the sys could modify the audit_trail cause sys could do everything in db scope.second,I think it will cause the overhead of cost to do audit in db although it may be little.

    let the db focus on transaction.
     
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, SYS cannot audit its own audit trail as it's written outside of the database into an operating system file. This can also be configured to write into the UNIX/Linux syslog which is owned by 'root', untouchable by SYS. Modifications to the db audit trail will be recorded in this external file.

    You haven't provided a good reason to disable auditing in the database. Do you have another argument?