5 Enabling Internal Metadata Table Auditing

This chapter contains the following topics:

• About Internal Table Auditing
  Oracle Life Sciences Data Hub uses Oracle audit trail functionality to track all data changes in selected internal tables.
• Audited Tables
  When you enable auditing of internal tables in Oracle Life Sciences Data Hub, you enable auditing for a predefined set of tables and columns in those tables, listed below.

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).

Audit Data Handling

The automatically generated Insert, Update, or Delete trigger in the After event on the audited table performs the auditing.

For each record, the trigger calls the corresponding procedure to compare each audited column to see if its value has changed. If changed, the system creates one row in the shadow table to store the old value(s).

The shadow table stores data in "sparse" format; unchanged values are stored as nulls. When a new record is inserted into an audited table, the system adds a row to the shadow table with a transaction type of Insert (I) with all column values represented as nulls (since null represents their previous, nonexistent, values). When a record is deleted from an audited table, the system adds a row to the shadow table with a transaction type of Delete (D) and all values stored.

Shadow tables do not compress data.

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).

For example, because the STATUS_RC column in the CDR_ROLES_B table is part of the unique key, whenever you modify a role there is a change to a unique key column and the system adds two rows to the shadow table, one to delete the old record and another to insert the new one.

Audited Tables

When you enable auditing of internal tables in Oracle Life Sciences Data Hub, you enable auditing for a predefined set of tables and columns in those tables, listed below.

Where two tables share the same name with either _B or _TL appended, B means base and TL means translatable. If a table includes columns whose values can be translated and displayed on screen in a different language, the table is split into a base and a translatable table with the same primary key. For every row in the base table, the translatable table includes one row per supported language.

For further information, see the Oracle® Application Framework Personalization Guide at http://download.oracle.com/docs/cd/B53825_08/current/acrobat/121fwkpg.pdf.

• CDR_CONNECTIONS. This table stores information about user-defined connections to remote databases (both user-specific and shared connections). All columns are audited.
• CDR_NAMINGS. This table stores information on every user-defined object. Only the NAME column is audited.
• CDR_NAMING_VERSIONS. This tables stores information on every version of every user-defined object. Only the VALIDATION_STATUS_RC column is audited.
• CDR_OBJ_CLA_MAPPINGS. This table stores information on the assignment of classification values to user-defined objects. The columns CLA_LEVEL_ID, CLA_OBJ_ID, PAR_COMPANY_ID, andPAR_OBJ_ID, PAR_OBJ_VER are audited.
• CDR_OBJ_UG. This table stores information on the assignment of user groups to user-defined objects. All columns are audited.
• CDR_OBJECT_SUBTYPES_B. This table stores base information on object subtypes. All columns are audited.
• CDR_OBJECT_SUBTYPES_TL. This table stores translated language values on object subtypes. All columns are audited.
• CDR_OUTPUTS. This table stores information on all outputs generated by user-defined executables. Only the BLINDING_STATUS_RC column is audited.
• CDR_REMOTE_LOCATIONS. This table stores information on remote databases registered by users in Oracle LSH. All columns are audited.
• CDR_ROLES_B. This table stores base information on user-defined roles. All columns are audited.
• CDR_ROLES_TL. This table stores translated language values for user-defined roles. All columns are audited.
• CDR_SECURITY_DBACCOUNT. This table stores information on all database accounts defined in the Oracle LSH user interface. All columns are audited.
• CDR_SOURCE_CODES. This table stores information on user-defined source code objects. Only the SHAREABLE_FLAG_RC column is audited.
• CDR_SUBTYPE_OPR_ROLES. This table stores information about the roles and object subtypes associated with each other. All columns are audited.
• CDR_TABLE_REFS. This table stores information about all user-defined Table instances. Only the BLINDING_STATUS_RC column is audited.
• CDR_UG_ROLES. This table stores information about the roles associated with each user group. All columns are audited.
• CDR_USER_GROUPS_B. This table stores base information on user groups. All columns are audited.
• CDR_USER_GROUPS_TL. This table stores translated language values for user groups. All columns are audited.
• CDR_USER_UG_ROLES. This table stores information about the roles and users associated with each user group. All columns are audited.
• DME_FLAG_DATA_A. This table stores the history of flag assignments to data in Oracle Health Sciences Data Management Workbench (Oracle DMW).