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!

Urgent Need Help on Oracle Auditing

Discussion in 'General' started by rahulkadam, Jul 11, 2009.

  1. rahulkadam

    rahulkadam Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi All,

    We are tyring to turn On auditing for our Oracle Database(9i/10g).

    REquirement is to audit the insert/delete/update statements of the team members. So for every delete/update statement we would liek to have the before image and after image of the database. What the row data was before updating the database.

    We have 100 tables and 12 schemas. I do not want to write triggers. I am looking for any functionality in Oracle which will help me achieve this.

    Thanks in advance.

    Regards,
    Rahul
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Hi Rahul,

    You can use the AUDIT command to audit all operations by all members of your team. Auditing is disabled b default. One of the ways to enable it would be

    Code (Text):
    SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
    You need to restart the database server after this. For example, if we wanted to audit all statements executed by the user TEST, we can do the following.
    Code (Text):

    CONNECT sys/password AS SYSDBA

    AUDIT ALL BY test BY ACCESS;
    AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY test BY ACCESS;
    AUDIT EXECUTE PROCEDURE BY test BY ACCESS;
    Also note that Auditing would not give data image of tables before/after statements were executed. It would tell you all details when was the statement executed by whom on which object etc.

    Hope this helps.
     
  3. rahulkadam

    rahulkadam Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Thanks.

    But I hope below will help me acheive. I am trying to acheive the below schema. Is it possible through the mentioned statememnts.

    "User", "Date", "Action", "ROW_DATA"

    rkadam <today> <delete> <old row>
    rkadam <today> <insert> <new row>
    rkadam <today> <update> <old row>
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Hmmm, i don't think you can achieve row_data level audit by default. Before and After snapshots of data for every transaction are not available from Oracle's own auditing features (afaik). You can use one of the methods suggested in the below article.

    http://www.oracle-base.com/articles/9i/CompleteDataAudit.php
     
  5. rahulkadam

    rahulkadam Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Thanks.. Is it possible to have a trigger syntax in Oracle liek sybase.. In sybase i can have "*" but i believe in Oracle I have to use the OLD/NEW keyword. The advantage of using a "*" is even if the schema changes in future i do not have to change the backup table.
    Code (Text):

    create trigger tgr_gfix_coverage_in
    on gfix_coverage
    for insert
    as
    insert gfix_coverage_bak
    select  'INSERT',
            getdate(),
            suser_name(),
            *
            from inserted
    go
    In Oracle it will be like ....
    Code (Text):
    ('INSERT', sysdate, 'rkadam',  :old.service_id, :old.client_code,
            :old.client_username, :old.exchange, :old.strategy, :old.value);
     
  6. Adain

    Adain Guest

    Thanks for taking the time to help, I really apprciate it.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Sybase uses an INSERTED and DELETED 'table' to store such images; Oracle uses 'blocks' for the same purpose hence the ":eek:ld" and ":new" references. You must write the trigger as you've illustrated for Oracle if you want it to function and populate your transactional history table.
     
  8. debrah.h48

    debrah.h48 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    80