About Internal Table Auditing

Oracle Life Sciences Data Hub uses Oracle audit trail functionality to track all data changes in selected internal tables.

Auditing tables helps to satisfy regulatory requirements. You must explicitly enable auditing on Oracle LSH tables. For more details, see the Oracle Life Sciences Data Hub Installation Guide.

For each audited table, Oracle creates a shadow table in the same schema in which to store the audit information. The shadow table contains additional columns that contain auditing information, including AUDIT_USER_NAME, AUDIT_TIMESTAMP, ADUIT_TRANSACTION_TYPE, and AUDIT_TRUE_NULLS. The name of the shadow table consists of the first 24 characters of the audited table's name plus _A (for Audit).

Oracle creates one or more views onto each shadow table. You can access the views in SQL*Plus when you log in as apps. Each view's name consists of the first 24 characters of the audited table's name plus _AC# or _AV#, where C and V are types of views and # represents a number. Combined (C)-type views allow you to see all columns and therefore reconstruct all row values at a particular point in time. View (V)-type views allow access to fewer columns, but provide information about when a value was changed. If there are multiple V-type views (due to size limitations), the system numbers them sequentially.

Oracle also creates three audit triggers and corresponding procedures for each table, one each for Insert (I), Update (U), and Delete (D). The trigger and procedure names both begin with the first 24 characters of the audited table's name followed by, for triggers, _AI, _AU, or _AD or, for procedures, _AIP, _AUP, or _ADP.

For further information, see the Oracle® E-Business Suite System Administrator's Guide - Security at http://download.oracle.com/docs/cd/B53825_08/current/acrobat/121sasg.pdf.

This section contains the following topics:

• Audit Data Handling
  The automatically generated Insert, Update, or Delete trigger in the After event on the audited table performs the auditing.
• Audit Handling of Changes to Primary and Unique Key Values
  If a value changes in a column that is part of the table's primary or unique key, Oracle inserts two new rows in the shadow table, one with a transaction type of Delete (D) and one with a transaction type of Insert (I).