This discussion is archived
14 Replies Latest reply: Jan 16, 2011 6:36 AM by Duncan Casemore RSS

Audit option

827971 Newbie
Currently Being Moderated
Dear All,

I'm using the built in Audit trial in oracle

my question is can i determine the operation that i want to do the Audit on it .

i mean can i chose just for insert action or just for update action , or delete action , or insert and delete.....

i want to chose between these operation i dont want the system to register all types of transactions

any one can help please ?
  • 1. Re: Audit option
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post details of OS, database and EBS versions.

    Are you referring to database audit options or EBS audit options ?

    HTH
    Srini
  • 2. Re: Audit option
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    Please see these docs/links for details.

    Understanding Data Auditing in Oracle Application Tables [ID 69660.1]
    FAQ (Audit Trail) [ID 107330.1]
    Setup & Usage (Audit Trail) [ID 105630.1]
    Troubleshooting (Audit Trail) [ID 105624.1]

    Audit Trail
    http://forums.oracle.com/forums/search.jspa?threadID=&q=Audit+Trail&objID=c3&dateRange=all&userID=&numResults=15&rankBy=10001

    Thanks,
    Hussein
  • 3. Re: Audit option
    827971 Newbie
    Currently Being Moderated
    I Know what's oracle do for Audit , my question is how can i make the audit just for delete operations .

    I have a solution but I want the standard one , I can go to the database and disable the triggers related to add and update.

    I want a standard solution
  • 4. Re: Audit option
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    I Know what's oracle do for Audit , my question is how can i make the audit just for delete operations .

    I have a solution but I want the standard one , I can go to the database and disable the triggers related to add and update.

    I want a standard solution
    What do you mean by standard solution? Please elaborate more.

    Thanks,
    Hussein
  • 5. Re: Audit option
    827971 Newbie
    Currently Being Moderated
    ok. when you want to enable the audit trail
    you have to do this
    1- go to the audit group , create a new group that hold all tables that you want to do the audit for
    2- you enable the owner of your application to be audited from the install window
    3- go to the audit table window then select your columns that you want to audit for each table you select
    4- run the audit table request

    after you run this request oracle creates
    1- shadow tables for your selected tables
    2 -in non hr table create 3 triggers one for insert , one for update on for delete
    3- function and procedure that oracle will use it for the audit reports and triggers

    in this way you cant decide the operation that you want to do audit for
    I mean you cant just chose delete , or just update , there is no screen or window to chose between the operations.

    Really I'm looking for this window or screen , for me using oracle application window is the standard solution.

    I dont want to go to the database and disable the insert or update or delete trigger that oracle creates when you ran the audit request
  • 6. Re: Audit option
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    Please see (Note: 69660.1), "2. Creates the after event ..." section.

    Thanks,
    Hussein
  • 7. Re: Audit option
    827971 Newbie
    Currently Being Moderated
    Dear Hussein

    Really I would like to thank you for you attention, but I have read the notes and the system administrator user guide.

    The note in the Oracle Metalink just explain what happened in the update operation , just for example to understand the process , it did not mention how can I just do the audit for the update or delete or insert only.

    so still I did not have the answer if there is a standard solution for my question .
  • 8. Re: Audit option
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    so still I did not have the answer if there is a standard solution for my question .
    AFAIK, there is no standard solution and you will have to create an event as mentioned in the doc -- Please log a SR to confirm this with Oracle support.

    Thanks,
    Hussein
  • 9. Re: Audit option
    Duncan Casemore Expert
    Currently Being Moderated
    What table are you trying to audit? I see you've posted this in the HRMS forums and HRMS does have auditing capabilities on some transactional tables that provide a better level of abstraction than standard AOL auditing.
  • 10. Re: Audit option
    827971 Newbie
    Currently Being Moderated
    You mean date track.

    I have a customer that want to do the audit just on the delete operations on employee information
  • 11. Re: Audit option
    Duncan Casemore Expert
    Currently Being Moderated
    Employee information meaning the Person or Assignment record?

    Date-track is what makes AOL auditing data difficult to interpret. A delete on a table row is quite different to a date-track operation. As part of, say, a 'Delete Next Change', 'Replace Future Changes' or 'Purge', the date-track operation will update or delete multiple table rows. So if you use AOL auditing it is very difficult to interpret a bunch of table operations into actions that users have taken.

    Payroll have solved this for you in standard Oracle HRMS through the Payroll Event Model and a set of APIs called the 'Interpreter'. These are used by proration and retropay, as well as other areas across HRMS.

    You don't need to be running payroll to use this functionality but you might need to enable the triggers and functional areas if they're not already running.

    Once the triggers are capturing events you can then create an Event Group to register your interest in business-level events, such as 'Date-Track Purge' rather than delete row and use the APIs in Pay_Interpreter_pkg to pull back changes in your Event Group.
  • 12. Re: Audit option
    Duncan Casemore Expert
    Currently Being Moderated
    The payroll User Guides provide all sorts of information on enabling triggers and setting up Event Groups.

    If you're looking for a little more background, check out:

    HRMS DEVELOPMENT WHITE PAPER - Continuous Calculation (Doc ID 188154.1)

    It's written for a payroll process (Continuous Calc) but offers an excellent introduction to the Event Model and Interpreter.
  • 13. Re: Audit option
    827971 Newbie
    Currently Being Moderated
    Ok if I use the event group functionality , I have to create my table to store the old value of delete and create a customized report for that .
  • 14. Re: Audit option
    Duncan Casemore Expert
    Currently Being Moderated
    I don't think you'll need to create your own table for the deleted events - they're stored in the payroll event tables. But yes you'll need some way of viewing the events, like a report.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points