Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2010
    Posts
    8

    Unanswered: Audit Table for tracking Data Changes in Oracle

    Hi,
    I want to audit all the data changes that occur on any table row/column in my application so that I can track the old and new values. I understand that we can use triggers to achieve this

    The Events that are to be captured are Insert/Update/Delete that affect any column/row with the modified time and also the user who triggered this event (This may be a user from the front end UI application or a Java Application).

    What are the various options to achieve this ?

    I do not want to use a normal audit trail that logs also the unchanged information in the audit table

    The Approach should be efficient and also widely used.

    Thanks in advance

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    If you are on 11g you could also use flashback archives. They will do all the magic for you.

  3. #3
    Join Date
    Jul 2010
    Posts
    8
    I am using Oracle 10 G Version

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Is the application 2 or 3 tier?
    Does application utilize connection pooling?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jul 2010
    Posts
    8
    Hi,
    The Application will be using Connection Pooling

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The Application will be using Connection Pooling
    Connection pooling GREATLY complicates auditing end user changes.
    IMO, any such auditing must be done at application level; not at database level.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jul 2010
    Posts
    8
    Can you guide me on how to go about if we do not use connection pooling? thanks

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    How do YOU identify programatically the identity of the end user? -> Please post actual code

    You write trigger on every table where you want DML logged/audited
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Jul 2010
    Posts
    8
    Hi,
    I will be posting the code shortly. Can you please guide me on how the connection pooling affects end user auditing.
    Thanks

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can you please guide me on how the connection pooling affects end user auditing.
    A single connection is shared or used by multiple end users.
    Dynamically & in real time how does DB know or identify which end user resulted in DML to be executed.
    All DB can see or know is FIXED USER from application server is doing DML.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jul 2010
    Posts
    8
    CREATE TABLE "ACCOUNT"
    ( "ID" NUMBER(12,0),
    "ACCT_NAME" VARCHAR2(200 BYTE),
    "ACCOUNT_TYPE" VARCHAR2(4000 BYTE),
    "LAST_MODIFIED_DATE" TIMESTAMP (6) DEFAULT sysdate,
    "LAST_MODIFIED_USER" VARCHAR2(4000 BYTE)
    )



    CREATE TABLE "AUDIT_LOG"
    ( "LOG_ID" NUMBER(12,0),
    "TRANSACTION_ID" NUMBER(10,0),
    "TRANSACTION_DATE" TIMESTAMP (6),
    "TABLE_NAME" VARCHAR2(32 BYTE),
    "TABLE_PK_VALUE" NUMBER(12,0),
    "COLUMN_NAME" VARCHAR2(32 BYTE),
    "PREVIOUS_VALUE" VARCHAR2(64 BYTE),
    "CHANGED_VALUE" VARCHAR2(64 BYTE),
    "CHANGE_BY" VARCHAR2(32 BYTE),
    "ACTION" VARCHAR2(20 BYTE),
    CONSTRAINT "LOG_LOG_ID_PK" PRIMARY KEY ("LOG_ID")
    TABLESPACE "SYSTEM" ENABLE
    )

    CREATE or REPLACE TRIGGER ACCOUNT$TRIGGER
    AFTER INSERT OR UPDATE OR DELETE ON ACCOUNT
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
    v_transaction_id number(20);
    BEGIN
    SELECT NVL(MAX(TRANSACTION_ID), 0) + 1
    INTO v_transaction_id
    FROM AUDIT_LOG;
    /* -- IF For Inserting -- */
    IF INSERTING THEN
    INSERT INTO AUDIT_LOG
    (log_id, transaction_id, transaction_date, table_name,
    table_pk_value, column_name, previous_value, changed_value,
    change_by,action)
    VALUES
    ((SELECT NVL(MAX(LOG_ID), 0) + 1 FROM AUDIT_LOG),
    v_transaction_id, SYSDATE, 'ACCOUNT',
    :NEW.ID,
    'ACCT_NAME', :OLD.ACCT_NAME, :NEW.ACCT_NAME,
    :NEW.LAST_MODIFIED_USER,'INSERT');

    INSERT INTO AUDIT_LOG
    (log_id, transaction_id, transaction_date, table_name,
    table_pk_value, column_name, previous_value, changed_value,
    change_by,action)
    VALUES
    ((SELECT NVL(MAX(LOG_ID), 0) + 1 FROM AUDIT_LOG),
    v_transaction_id, SYSDATE, 'ACCOUNT',
    :NEW.ID,
    'ACCOUNT_TYPE', :OLD.ACCOUNT_TYPE, :NEW.ACCOUNT_TYPE,
    :NEW.LAST_MODIFIED_USER,'INSERT');
    END IF; /* -- End of If for Inserting-- */
    /**--- Start of Updating ---*/
    IF UPDATING THEN
    IF (:OLD.ACCT_NAME != :NEW.ACCT_NAME) or (:OLD.ACCT_NAME is not null and :NEW.ACCT_NAME is null) or
    (:OLD.ACCT_NAME is null and :NEW.ACCT_NAME is not null) THEN
    INSERT INTO AUDIT_LOG
    (log_id, transaction_id, transaction_date, table_name,
    table_pk_value, column_name, previous_value, changed_value,
    change_by,action)
    VALUES
    ((SELECT NVL(MAX(LOG_ID), 0) + 1 FROM AUDIT_LOG),
    v_transaction_id, SYSDATE, 'ACCOUNT',
    :NEW.ID,
    'ACCT_NAME', :OLD.ACCT_NAME, :NEW.ACCT_NAME,
    :NEW.LAST_MODIFIED_USER,'UPDATE');
    END IF;
    IF (:OLD.ACCOUNT_TYPE != :NEW.ACCOUNT_TYPE) or (:OLD.ACCOUNT_TYPE is not null and :NEW.ACCOUNT_TYPE is null ) or
    (:OLD.ACCOUNT_TYPE is null and :NEW.ACCOUNT_TYPE is not null) THEN

    INSERT INTO AUDIT_LOG
    (log_id, transaction_id, transaction_date, table_name,
    table_pk_value, column_name, previous_value, changed_value,
    change_by,action)
    VALUES
    ((SELECT NVL(MAX(LOG_ID), 0) + 1 FROM AUDIT_LOG),
    v_transaction_id, SYSDATE, 'ACCOUNT',
    :NEW.ID,
    'ACCOUNT_TYPE', :OLD.ACCOUNT_TYPE, :NEW.ACCOUNT_TYPE,
    :NEW.LAST_MODIFIED_USER,'UPDATE');
    END IF;

    END IF; /*--End if for Updating -- */

    /**--- Start of DELETING ---*/
    IF DELETING THEN
    INSERT INTO AUDIT_LOG
    (log_id, transaction_id, transaction_date, table_name,
    table_pk_value, column_name, previous_value, changed_value,
    change_by,action)
    VALUES
    ((SELECT NVL(MAX(LOG_ID), 0) + 1 FROM AUDIT_LOG),
    v_transaction_id, SYSDATE, 'ACCOUNT',
    :OLD.ID,
    'ACCT_NAME', :OLD.ACCT_NAME, :NEW.ACCT_NAME,
    :OLD.LAST_MODIFIED_USER,'DELETE');

    INSERT INTO AUDIT_LOG
    (log_id, transaction_id, transaction_date, table_name,
    table_pk_value, column_name, previous_value, changed_value,
    change_by,action)
    VALUES
    ((SELECT NVL(MAX(LOG_ID), 0) + 1 FROM AUDIT_LOG),
    v_transaction_id, SYSDATE, 'ACCOUNT',
    :OLD.ID,
    'ACCOUNT_TYPE', :OLD.ACCOUNT_TYPE, :NEW.ACCOUNT_TYPE,
    :OLD.LAST_MODIFIED_USER,'DELETE');
    END IF; /*--End if for Deleting -- */
    END ACCOUNT$TRIGGER;


    How will I be able to identify the LAST_MODIFIED_USER from the master table for the delete operation to log it? The LAST_MODIFIED_USER would be the user name of the user from the front end application

  12. #12
    Join Date
    Jul 2010
    Posts
    8
    LAST_MODIFIED_USER is a user who has account in the application. He has a user name and password in the application. He is not the DB User. We log the user from the front end who has made the update/insert. In Insert/Update case,the application will update the LAST_MODIFIED_USER with the user name of the user who logged into the application. But in the case of delete since we delete the row directly we will not be able to update the Last_Modified_USer?

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How will I be able to identify the LAST_MODIFIED_USER from the master table for the delete operation to log it?

    >The LAST_MODIFIED_USER would be the user name of the user from the front end application

    Answers to both are application dependent.
    It is your application, your DB, & your data.
    Solution must be designed & implemented into application.
    No data is just magically available inside database.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  14. #14
    Join Date
    Jul 2010
    Posts
    8
    Whenever a delete operation is to be performed, can I read the record that has to be deleted and insert it once in the master table so that a record gets created in the audit with the values to be deleted and I can mark them with the status as Delete in the audit. Then I can perform the actual delete on the master table (which i will not be logging as I have marked the insert operation in the last step as a delete). Is this an acceptable solution? Please advice Thanks

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is this an acceptable solution?
    actual TESTING will provide you the answer.
    It does not matter what I or anyone else thinks or says.

    Realize that with connection pooling for the same end user "transaction" different DB sessions can be involved.
    Realize that with Oracle's Read Consistent View implementation, an uncommitted row in audit table can NOT be seen by another session!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •