Re: Can this be done with a trigger?

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 6 Aug 2008 20:45:17 -0800
Message-ID: <489a6fcd$1@news.victoria.tc.ca>

DA Morgan (damorgan_at_psoug.org) wrote:
: artmerar_at_yahoo.com wrote:
: > On Aug 5, 6:28 pm, DA Morgan <damor..._at_psoug.org> wrote:
: >> artme..._at_yahoo.com wrote:
: >>> Hi,
: >>> We have a table like this:
: >>> CUSTOMER_ID
: >>> PRODUCT_ID
: >>> FORMAT
: >>> STATUS
: >>> DATE_ADDED
: >>> DATE_MODIFIED
: >>> We also have a history table like this:
: >>> CUSTOMER_ID
: >>> PRODUCT_ID
: >>> FORMAT
: >>> STATUS
: >>> SOURCE
: >>> DATE_ADDED
: >>> We want to put a trigger on the first table such that when a record is
: >>> inserted or updated it will make the entry to the history table.
: >>> However, the problem is the 'SOURCE' column on the history table. It
: >>> is not present or needed in the first table.
: >>> Entries into the first table are done through a PL/SQL program. So,
: >>> what we want is that when the entry is made into the first table, it
: >>> fires the trigger to make the second entry. But, how can this be done
: >>> if the columns do not match?
: >>> We do not want to code it in the PL/SQL program because then someone
: >>> can make an entry to the first table and we cannot record the history
: >>> on it, yet the SOURCE column only gets populated in the PL/SQL
: >>> program....
: >>> And, to make things more complicated, depending on what is being
: >>> UPDATED, we may need the values of :NEW or :OLD........
: >>> Is there a way to do this?
: >> In what version of Oracle?
: >>
: >> Assuming 9i or above you are reinventing the wheel: Use Fine Grained
: >> Auditing.
: >> --
: >> Daniel A. Morgan
: >> Oracle Ace Director & Instructor
: >> University of Washington
: >> damor..._at_x.washington.edu (replace x with u to respond)
: >> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
: >>
: >> - Show quoted text -
: >
: >
: > We are running Oracle 10g R2. 10.2.0.1.0 to be exact.......does this
: > tell you anyhting??
: >

: It does ... you are reinventing the wheel:
: http://www.psoug.org/reference/dbms_fga.html

Perhaps I misunderstand it, but it seem to me that the FGA data you get doesn't seem to make it easy to answer questions such as

 "Did user X edit record Y?".
 "Which people have edited record X?".
 "What was the value of column Z in record X at 8:00 am on July 1st, what

  were the previous and next values, and who set them?"  "What are all the edits that have been made to record X?"

http://www.oracle.com/technology/pub/articles/nanda_fga_pt3.html mentions a couple of potential problems,

• ... may lead to ... false positive entries in the audit trail
• ... FGA records ... SQL statements ... but not the values before and after the change (which many of us might assume would be among the most important details you would want to know).

The fga auditing seems most appropriate for "hacker" or "naughty programmer" security, along with numerous other log files, web logs, login logs, etc etc.

$0.10 Received on Wed Aug 06 2008 - 23:45:17 CDT