Thursday, April 14, 2016

Audit Trail for Custom Table in Oracle Apps - Step By Step


Hi All ...

Here the step by step for Audit Trail for Custom Table in Oracle Apps

1. Register Custom Schema 
Navigate to System Administrator Menu/Security/ORACLE/Register

2. Ensure that Audit on the Application is Enabled
Navigate to System Administrator Menu Security/AuditTrail/Install

The owner of table XX_TABLE  is XX_SCHEMA. Hence query on 
XX_SCHEMA to ensure that Audit is enabled for this Application.


3. Register table,columns and primary key
Here the procedure to register the custom able,columns and primary key in Oracle Applications. Install the procedure on your DB and run the following:

begin
register_table('XX_TABLE','XX_SCHEMA'); /* (table name, table owner) */
commit;
end;

Procedure "register_table":

CREATE OR REPLACE PROCEDURE register_table (
table_name VARCHAR2,
application_short_name VARCHAR2
)
AS
status VARCHAR2 (10);

CURSOR c_columns ( p_table_name all_tab_columns.table_name%TYPE )
IS
SELECT column_name,
data_type,
data_length,
nullable,
ROWNUM,
data_precision,
data_scale
FROM all_tab_columns
WHERE table_name = p_table_name;

CURSOR c_constraints (p_table_name all_tab_columns.table_name%TYPE,
p_application_short_name all_tab_columns.owner%TYPE )
IS
SELECT constraint_name,
table_name,
status
FROM all_constraints
WHERE table_name = p_table_name AND owner = p_application_short_name AND constraint_type = 'P';

CURSOR c_constraint_columns (
p_table_name all_tab_columns.table_name%TYPE,
p_application_short_name all_tab_columns.owner%TYPE
)
IS
SELECT acc.constraint_name,
acc.column_name,
acc.POSITION
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.table_name = p_table_name
AND ac.constraint_type = 'P'
AND ac.owner = p_application_short_name;
BEGIN

DBMS_OUTPUT.put_line ('Registering Table '|| table_name ||'in application ' || application_short_name);

ad_dd.register_table (p_appl_short_name => application_short_name,
p_tab_name => table_name,
p_tab_type => 'T'
);

FOR r_columns IN c_columns (table_name)
LOOP

DBMS_OUTPUT.put_line ('Registering Column '|| r_columns.column_name);

ad_dd.register_column (p_appl_short_name => application_short_name,
p_tab_name => table_name,
p_col_name => r_columns.column_name,
p_col_seq => r_columns.ROWNUM,
p_col_type => r_columns.data_type,
p_col_width => r_columns.data_length,
p_nullable => r_columns.nullable,
p_translate => 'N',
p_precision => r_columns.data_precision,
p_scale => r_columns.data_scale
);

END LOOP;

FOR r_constraints IN c_constraints (table_name, application_short_name)
LOOP

DBMS_OUTPUT.put_line ('Creating Primary Key Constraint ' || r_constraints.constraint_name);

SELECT DECODE (r_constraints.status,
'ENABLED', 'Y',
'N'
)
INTO status
FROM DUAL;

ad_dd.register_primary_key (p_appl_short_name => application_short_name,
p_key_name => r_constraints.constraint_name,
p_tab_name => table_name,
p_description => 'Primary Key for Table '|| table_name,
p_key_type => 'D',
p_audit_flag => 'Y',
p_enabled_flag => status
);
END LOOP;

FOR r_constraint_columns IN c_constraint_columns (table_name, application_short_name)
LOOP

DBMS_OUTPUT.put_line ( 'Registering Primary Key Column '||
r_constraint_columns.column_name||
' for Constraint '||
r_constraint_columns.constraint_name);

ad_dd.register_primary_key_column (p_appl_short_name => application_short_name,
p_key_name => r_constraint_columns.constraint_name,
p_tab_name => table_name,
p_col_name => r_constraint_columns.column_name,
p_col_sequence => r_constraint_columns.POSITION
);
END LOOP;
END register_table;




4. Create Audit Group
Once you table registered,navigate to System Administrator Menu Security/AuditTrail/Groups 

Application Name: XX Custom Schema
Audit Group: XX Audit
Group State: Enabled

Now, add audit tables to this group[you can add as many tables]
User Table Name: XX_TABLE


5. Run Concurrent program “AuditTrail Update Tables”

This process can be run from System Administrator responsibility. It has no parameter. Running this process will create the Audit tables and the triggers that manage Audit data.


6. Ensure that Audit Tables have been created as expected
SELECT object_name, object_type
FROM all_objects
WHERE object_name LIKE 'XX_TABLE_A%'

OBJECT_NAME                            OBJECT_TYPE
--------------------------                      --------------------------
XX_TABLE_A                               TABLE
XX_TABLE_A                               SYNONYM
XX_TABLE_AC                            TRIGGER
XX_TABLE_AC1                          VIEW
XX_TABLE_AD                            TRIGGER
XX_TABLE_ADP                          PROCEDURE
XX_TABLE_AH                            TRIGGER
XX_TABLE_AI                              TRIGGER
XX_TABLE_AIP                            PROCEDURE
XX_TABLE_AT                             TRIGGER
XX_TABLE_AU                            TRIGGER
XX_TABLE_AUP                          PROCEDURE
XX_TABLE_AV1                           VIEW

Fine, this proves that the concurrent program in Step 5 did its job.
Optionally, you may run concurrent process “AuditTrail Report for Audit Group Validation” to validate the success of Audit Table/Trigger creation.

7. Add further columns for Audit Trail
By default Oracle will Audit Trail on all columns that are a part of first available Unique Index on XX_TABLE.
However further columns can be added to the Audit Trail. Lets say you wish to Audit Trail on Column Meaning too.
Navigate to System Administrator Menu Security/AuditTrail/Tables

You can add additional columns to audit trail and re-execute Step 5.
Please note that adding columns for Audit could have been done immediately after Step 4.

You are DONE...

Usefull notes:
How To Enable Auditing On A Table (Doc ID 1359749.1)
Unable to Enable Audit Trail for Custom Objects (Doc ID 433527.1)


Good Luck ...

9 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
    Replies
    1. Good that this is commented.
      Fro every article these people are advertising.

      Delete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. its is very helpfull
    http://quincetravels.com

    ReplyDelete
  4. Hi admin,Pls remove few more advertisements.

    ReplyDelete
  5. Nice blog. it is very useful for us, thanks for sharing such a informative blog. visit India VPS Hosting

    ReplyDelete
  6. Nice to read your article! I am looking forward to sharing your adventures and experiences. Best Instagram Story Apps

    ReplyDelete