Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2008
    Posts
    10

    Unanswered: Track inserted/updated/deleted rows with Oracle audit?

    Hi all,

    Is there an ability in 9i to track primary keys or some another data about inserted/updated/deleted rows with oracle audit?

  2. #2
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Maybe. It depends on what you want, why you want it , and what you plan to do with it.

    You'll have to be more specific.

  3. #3
    Join Date
    Oct 2008
    Posts
    10
    what you want,
    I have two tables with surrogate PK's and one many-to-many table with two columns-FK's to tables with PK's. There is an UNIQUE(FK1, FK2) constraint on many-to-many table. I want to track all changes in these tables to replicate them to non-oracle database. So, I want at some moment to get PK's of updated/deleted/new rows in tables with PK's and a combination of FK1 & FK2 updated/deleted/new rows in many-to-many table. when I get identidiers that point at specific rows, I select them and copy to another database.

    I prefer not to use triggers.

  4. #4
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Interesting. I am not sure how I would do this without triggers. I wonder if there is anyway you could leverage a materialized view log? I'll leave that as an exercise for your research.

    It's Friday, my work day is over and I am heading home ........

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Can you change the code which updated/deleted/new rows in tables with PK's?
    If so, when DML occurs, INSERT changes into NEW_AUDIT.
    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
    Oct 2008
    Posts
    10
    Quote Originally Posted by anacedent
    Can you change the code which updated/deleted/new rows in tables with PK's?
    I can but it is undesirable..
    Quote Originally Posted by anacedent
    If so, when DML occurs, INSERT changes into NEW_AUDIT.
    Yeah, looks like it is the only way

    shammat
    thanks for the links, but I'm running 9i
    btw guys, some question about FGA audit on 10g/11g..
    I see a record about some sql statement in dba_audit_trail when audit in 'DB, EXTENDED' mode. If it was not SELECT, how to check, was the transaction commited or rolled back? To check the value as it is appears in dba_audit_trail, exists in the table? For example, if I see
    SQL_TEXT
    ----------------------
    UPDATE TAB SET COL = 'A' WHEN PK = 444

    I need to check
    SELECT COL FROM TAB WHERE PK = 444
    and if COL = 'A' then committed...

  8. #8
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by student8
    shammat
    thanks for the links, but I'm running 9i
    Then you should have told us that right at the beginning.

    So you are to lazy to check the 9i documentation?

    FGA was available in 9ias:

    http://download.oracle.com/docs/cd/B...02.htm#1006142

    The AUDIT command was available in 9i as well:
    http://download.oracle.com/docs/cd/B...8a.htm#2059074

  9. #9
    Join Date
    Oct 2008
    Posts
    10
    Quote Originally Posted by shammat
    Then you should have told us that right at the beginning.
    please look at my first post.
    Quote Originally Posted by shammat
    So you are to lazy to check the 9i documentation?
    take it easy. I checked it. Look at this:
    Fine-Grained Auditing for Real-World Problems, Part 3
    this man says
    FGA in Oracle9i Database

    Let's briefly recap the benefits provided by FGA. For a complete discussion, please see Part 1 and Part 2 of this series.

    Regular auditing (via the AUDIT statement) records the statement used—such as SELECT or INSERT—and who issued it, from which terminal, when, and much more. However, the most important piece of information — which particular record was changed and the change in the data itself — is not captured. Instead, many users write triggers to capture the data value before and after the change and record them in user-defined tables. But because triggers are possible only on DML statements such as insert, update, and delete, one major area of access — the SELECT statement — can't be audited via this route.

    Hence the value of FGA: it captures SELECT statements only. Along with triggers and the Log Miner tool, FGA provides a mechanism for auditing all types of value changes and non-change related data accesses.
    I am trying to understand, is there any way to track chages without triggers.

    My other question is how to check was the audited insert/update/delete statement in 10g/11g committed or rolled back.

    Thanks for links again

  10. #10
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    please look at my first post.
    Sorry, I was mixing your question with a similar one where no version information was given.

    However, the most important piece of information — which particular record was changed and the change in the data itself — is not captured
    Hmm, that comment refers to the AUDIT command not the FGA. With FGA it should be possible to identify the details of what has changed.

    Quote Originally Posted by student8
    My other question is how to check was the audited insert/update/delete statement in 10g/11g committed or rolled back.
    I would assume that any change that is rolled back will not apear as it has not been "applied".

    The only way you could do that is to use an autonomous transaction inside the trigger that inserts the information into your own audit table and commits that autonomous transaction, so the row will always be written regardless of the "surrounding" transaction.

  11. #11
    Join Date
    Oct 2008
    Posts
    10
    Hmm, that comment refers to the AUDIT command not the FGA. With FGA it should be possible to identify the details of what has changed.
    Are you sure? look at this Inside OCP: Testing Database Security
    Fine-Grained Auditing

    Auditing is the monitoring and recording of selected user database actions. In Oracle9i Database, FGA enabled recording of row-level changes, along with SCN values, to reconstruct old data. FGA worked for SELECT statements only and not for DML statements such as UPDATE, INSERT, and DELETE. For instance, by using FGA in Oracle9i Database, you could determine that user Smith had updated the SALES table that is owned by SH but you could not see if user Smith had updated the AMOUNT_SOLD column or see the value of the AMOUNT_SOLD column before an update. In Oracle Database 10g, FGA can audit DML statements.
    another man
    Oracle Security Fine Grained Auditing (FGA)* Enhancements
    n Oracle 9i, only select statements could be audited; 10g has expanded this functionality to audit even DML statements such as insert, update and delete.
    Can you post sample dbms_fga.add_policy sample for controlling INSERTs for 9i server?

  12. #12
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by student8
    Can you post sample dbms_fga.add_policy sample for controlling INSERTs for 9i server?
    No, because it's apparently only available in 10g and above.
    I thought your comment was aimed at 10/11g

    Btw: why are you still using 9i?
    As far as I know it is not supported any more but I could be mistaken.

    I would definitely take an upgrade into consideration if that will give me the features I need for "free".

    Building an auditing system is not a trivial task if you want to get it right. My assumption would be that upgrading to 10g or 11g will be a lot less work than implementing something home-grown on top of 9i

  13. #13
    Join Date
    Oct 2008
    Posts
    10
    Btw: why are you still using 9i?
    not me
    9i works stable, there are a lot af Oracle Forms production apps working with 9i now, there is no 10g-experienced personell, no money, etc etc...
    ok, thanks,
    this is actual (another database, another case)
    My other question is how to check was the audited insert/update/delete statement in 10g/11g committed or rolled back.

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Change Data Capture?
    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.

  15. #15
    Join Date
    Oct 2008
    Posts
    10
    Quote Originally Posted by anacedent
    Change Data Capture?
    triggers?

    no solutions with analyzing audit table with plsql/java?
    any solutions on analyzing redo log from plsql?

Posting Permissions

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