Purpose
======
The purpose of this paper is to provide a clear understanding of what
data auditing is and how it is used in Oracle Applications.
What is AuditTrail?
============
AuditTrail is a way of keeping track of changes made to important data
in Oracle Application tables. AuditTrail keeps a history of the
following three questions:
1. What changed
2. Who changed it
3. When did the change take place
This can be done on a row by row basis or on individual columns of a
table. Although data auditing has been available since Release 1.4
of Oracle Applications, this paper will focus on Release 11 of the
Applications.
How to Setup AuditTrail
================
To setup AuditTrail in Oracle Applications Release 11, perform the
following steps:
1. Logon to the Applications as the sysadmin user and select the
System Administrator responsibility.
2. Make sure APPLSYS has select privileges on SYS.DBA_TABLES.
For example, if you are auditing the PA_PROJECTS_ALL table,
use the following SQL statement to verify this:
select TABLE_NAME
from SYS.DBA_TABLES
where TABLE_NAME like 'PA_PROJECTS_ALL';
3. Define auditgroups: It is required to group the tables that you
need to audit since auditing is enabled on an auditgroups basis.
Navigation: Security -> AuditTrail -> Groups
4. Define audit installations: In order to be able to audit across
application installations, you must specify the Oracle ID's whose
tables you wish to audit. At this point, you will need to specify
the list of columns from the table you want audited.
Navigation: Security -> AuditTrail -> Install
5. Run the "AuditTrail Update Tables" report from the submit request
form to enable auditing.
Navigation: Requests -> Run -> select a single request
What does the "AuditTrail Update Tables" report do?
===================================
1. Creates shadow tables with a name of the first 26 characters of
the audit table and a "_A" suffix. The columns will have the same
datatypes as those of the audited table. The program also creates
views _AC# and _AV# on the shadow table to facilitate the access of
data. The "#" in the view name represents a number, and can be any
number depending on how many views are needed to access the data.
The _AC# view contains the current state of the data at the time
of query from the join of the shadow table and the table audited.
The _AV# view contains the same data as the shadow table plus
the current value of the rows. Basically, the _AC# view contains
the current status of the audited table with the unchanged columns
filled-in with the unchanged values.
The shadow table has special columns in addition to the columns
you specified to be audited:
AUDIT_TIMESTAMP(DATE): Keeps track of date(HH:MI:SS) and time
when auditing was done.
AUDIT_TRANSACTION_TYPE (VARCHAR2(1)): This column has the
information of what type of transaction was conducted on the table,
I for "INSERT", U for "UPDATE", D for "DELETE" or C for "CURRENT".
AUDIT_USER_NAME (VARCHAR2(100)): The Oracle Application UserID if
changes are made from the forms or Oracle ID if changes are made
from SQL*PLUS.
AUDIT_TRUE_NULLS (VARCHAR2(250): Delimited list of column names
that were changed from NULL actually.
AUDIT_SESSION_ID(NUMBER): ID for the session.
AUDIT_COMMIT_ID (NUMBER): ID for the COMMIT.
PRIMARY KEY: The combined columns primary key value for the table
audited. This is not a special column but the actual value of the
primary key for the table.
2. Creates the after event, transaction level database triggers on
the tables in your audit group. What do we mean when we say after
event, transaction level database triggers? Database triggers can
be created based on timing events, such as "BEFORE" or "AFTER" a DATA
MANUPULATION LANGUAGE (DML) activity on the table. In other words,
it could be an "INSERT", "UPDATE" or "DELETE" activity on the table
that could be the triggering event that causes the creation of
a corresponding trigger. Triggers are created either at a "ROW" or
"TRANSACTION" level, raising the total number of triggers that can
be created on a table to 12 (3*2*2). It is this after event
transaction type triggers that gets created by the "AuditTrail Update
Table" report during this process. The names of these triggers
start with the first 26 characters of the name of the table being
audited, plus an _AI,_AU or _AD, which stand for after insert, after
update or after delete triggers respectively. These triggers call
the procedures _AIP,_AUP, or _AID which are also created by this
program. These procedures save the old data rows from the audited
table into the shadow table.
Examples to Help You Understand What Really Goes on Behind the Scenes
=================================================
Even though auditing can be done on insert, update, and delete actions,
these examples use the "update" for simplicity. Auditing is done on
the PA_PROJECTS_ALL table for a project 1118 data.
Status of Table Before Update:
-----------------------------
select PROJECT_ID, CLOSED_DATE, CREATED_BY, ORG_ID
from PA_PROJECTS_ALL
where PROJECT_ID='1118';
PROJECT_ID CLOSED_DA CREATED_BY ORG_ID
---------- --------- ---------- ----------
1118 1818 458
Status of Table After "habte1" Update:
-------------------------------------
1. update PA_PROJECTS_ALL
set CLOSED_DATE='13-JAN-99'
where NAME like 'habte1';
select PROJECT_ID, CLOSED_DATE, CREATED_BY, ORG_ID
from PA_PROJECTS_ALL
where PROJECT_ID='1118';
PROJECT_ID CLOSED_DA CREATED_BY ORG_ID
---------- --------- ---------- ----------
1118 13-jan-99 1818 458
select PROJECT_ID, AUDIT_TRANSACTION_TYPE type,
AUDIT_TIMESTAMP time, AUDIT_USER_NAME name,
AUDIT_TRUE_NULLS nulls, CLOSED_DATE date,
CREATED_BY, ORG_ID
from PA_PROJECTS_ALL_A
where PROJECT_ID='1118'; (shadow table)
PROJECT T TIME NAME NULLS DATE CREATED_BY ORG_ID
---------- - ---- ---- --------------- ---------- --------- ---------- ----------
1118 U 01:44:22 APPS
2. update PA_PROJECTS_ALL
set CLOSED_DATE=null
where NAME like 'habte1';
select PROJECT_ID, CLOSED_DATE, CREATED_BY, ORG_ID
from PA_PROJECTS_ALL
where PROJECT_ID='1118';
PROJECT_ID CLOSED_DA CREATED_BY ORG_ID
---------- --------- ---------- ----------
1118 1818 458
select PROJECT_ID, AUDIT_TRANSACTION_TYPE type,
to_char(AUDIT_TIMESTAMP,'HH:MI:SS') time,
AUDIT_USER_NAME name, AUDIT_TRUE_NULLS nulls,
CLOSED_DATE date, CREATED_BY, ORG_ID
from PA_PROJECTS_ALL_A
where PROJECT_ID='1118'
order by PROJECT_ID, AUDIT_TIMESTAMP;
PROJECT T TIME NAME NULLS DATE CREATED_BY ORG_ID
---------- - -------- --------------- ---------- --------- ---------- ----------
1118 U 01:44:22 APPS NYNNN
1118 U 01:45:23 APPS 13-JAN-99
Status After "habte" Update:
---------------------------
3. update PA_PROJECTS_ALL
set CLOSED_DATE='13-JAN-99'
where NAME like 'habte';
select PROJECT_ID, CLOSED_DATE, CREATED_BY, ORG_ID
from PA_PROJECTS_ALL
where PROJECT_ID='1118';
PROJECT_ID CLOSED_DA CREATED_BY ORG_ID
---------- --------- ---------- ----------
1118 13-JAN-99 1818 458
select PROJECT_ID, AUDIT_TRANSACTION_TYPE type,
to_char(AUDIT_TIMESTAMP,'HH:MI:SS') time,
AUDIT_USER_NAME name, AUDIT_TRUE_NULLS nulls,
CLOSED_DATE date, CREATED_BY, ORG_ID
from PA_PROJECTS_ALL_A
where PROJECT_ID='1118'
order by PROJECT_ID, AUDIT_TIMESTAMP;
PROJECT T TIME NAME NULLS DATE CREATED_BY ORG_ID
---------- - -------- --------------- ---------- --------- ---------- ----------
1118 U 01:44:22 APPS NYNNN
1118 U 01:45:23 APPS 13-JAN-99
1118 U 01:46:03 APPS NYNNN
Status After CLOSED_DATE, CREATED_BY and ORGID Update:
-----------------------------------------------------
4. update PA_PROJECTS_ALL
set CLOSED_DATE='15-MAY-99', CREATED_BY=300, ORG_ID=1;
select PROJECT_ID, CLOSED_DATE, CREATED_BY, ORG_ID
from PA_PROJECTS_ALL;
PROJECT_ID CLOSED_DA CREATED_BY ORG_ID
---------- --------- ---------- ----------
1118 15-MAY-99 300 1
select PROJECT_ID project,
to_char(AUDIT_TIMESTAMP,'HH:MI:SS') time,
AUDIT_TRANSACTION_TYPE type, AUDIT_USER_NAME name,
AUDIT_TRUE_NULLS, CLOSED_DATE date,
CREATED_BY, ORG_ID
from PA_PROJECTS_ALL_A
where PROJECT_ID='1118'
order by PROJECT_ID, AUDIT_TIMESTAMP;
PROJECT TIME T NAME NULLS DATE CREATED_BY ORG_ID
---------- -------- - --------------- ---------- --------- ---------- ----------
1118 01:44:22 U APPS NYNNN
1118 01:45:23 U APPS 13-JAN-99
1118 01:46:03 U APPS NYNNN
1118 02:36:21 U APPS 13-JAN-99 1818 458
Querying the _AC# and _AV# views will also enable you to monitor
who changed the data on the original table, by whom, and when the
changes were made. As you can see from the above examples, the
shadow table keeps track of the original data on the table prior to
the change. If a column is not changed, it leaves it as null,
otherwise, it keeps the original data for the column. On the
AUDIT_TRUE_NULLS special column, it keeps a concatenated value of
"Y"s and "N"s, "Y" for changed from null to some value, and "N" for
not changed. When retrieving data from the views, the
AUDIT_TRANSACTION_TYPE column may show a value of "C" for current
value.
In situations where you want to stop auditing, you must set the audit
group state to either "Disable-prepare for archive" or
"Disable-Interrupt Audit" and run the "AuditTrail Update Tables" report.
Disable-prepare for archive: Copies all the current values in the
audited table into the shadow table
and disables auditing triggers and thus
there is no more auditing.
Disable-Interrupt Audit: Modifies the triggers to save the final row
into the shadow table for each modified row
in the audited table. Further modification of
the same row in the audited table are not
recorded.
If you are interested in cleaning up the shadow table, set the audit
group to "Disable-Purge table" and run the "AuditTrail Update Table"
report to drop the auditing triggers and views and to delete the rows
in the shadow table.
Post a Comment