Data Auditing in Oracle Applications - Audit Trail

Following my last post about Audit users in Oracle Applications, in this post I’m going to talk about the Audit Trail - the data auditing feature in Oracle Applications. The AuditTrail enable us to know who, when and what was changed on each table we would like to audit. For example: Some of the profiles in Oracle Applications are very critical and important for normal activity of the system (like MO: Operating Unit). We might be interested to know who changed the value of critical profiles and the Audit Trail certainly can help us. Now I’ll show a step by step demo how to start Audit Trail on a table, I will go on with my previous example on the profiles table. The table we would like to audit is FND_PROFILE_OPTION_VALUES. 1) Logon to system with System Administrator responsibility. 2) Make sure the profile “AuditTrail:Activate” is set to Yes 3) Check the audited table's owner select owner from dba_tables where table_name like 'FND_PROFILE_OPTION_VALUES'; The owner is APPLSYS. 4) Check that audit is enabled for APPLSYS user Go to: Security -> AuditTrail -> Install -> Query for APPLSYS 5) Define new Audit Group Since the audit enabled on groups of tables, we should define a new Audit Group which will contain the FND_PROFILE_OPTION_VALUES table. Find the table’s application name by this query: select fav.application_name from fnd_application_vl fav, fnd_tables ft where fav.application_id = ft.application_id and ft.table_name = 'FND_PROFILE_OPTION_VALUES'; The query result: Application Object Library Navigate to: Security -> AuditTrail -> Groups Application: Application Object Library Audit Group: AC FND Profile Values Audit Group State: Enable Requested At this level, the columns that will be audited are the columns of the primary key or the first unique index on the audited table. You can add columns to be audited as much as you want. 6) Check/Add column to audit Navigate to: Security -> AuditTrail -> Tables Query for FND_PROFILE_OPTION_VALUES table. Add column PROFILE_OPTION_VALUE to be audited. 7) Run the “AuditTrail Update Tables” concurrent to enable the audit Navigate to: Requests -> Run -> Single Request -> choose request name: “AuditTrail Update Tables” -> Submit All definitions we made until here will not take effect until we execute this concurrent. This concurrent will create all objects (triggers and procedures) required for audit, and views to retrieve the audited data. This request should be executed each time we make changes in audit definitions, to generate new audit objects. To make sure it works, we can check if all audit objects were created: All audit objects names will be the first 26 characters of the audied table + suffix (_A, _AC, _AD...). We can also run report “AuditTrail Report for Audit Group Validation” to make sure all audit objects created successfully. Now let’s make a test to see how it works: To test the audit on the profile's table, we will change a value for a profile (any profile) and check the shadow table (fnd_profile_option_value_a) for an audit inormation. Navigate to: Profile -> System , and update the profile “FND: Diagnostics” (just for testing... you can choose any profile) to Yes. To see the audited data of the last change we can run this query: The change was audited as expected... As you can see, the “U” in the audit_transaction_type column indicates an update and the profile_option_value contain the value before update. The audit_transaction_type could be: 1) U - update 2) D - delete 3) I - insert How to disable Audit Trail? The AuditTrail could be stopped by 3 ways: 1) Disable Prepare for Archive 2) Disable Interrupt Audit 3) Disable Purge Table I've tried the third only… It deletes the data from the shadow table and drops all the audit objects from database. 1) Login to application and choose the System Administrator responsibility. 2) Security -> AuditTrail -> Groups 3) Query for your group 4) Update the Group State field to: “Disable – Purge Table”. 5) Run the “AuditTrail Update Tables” concurrent to make the changes. That’s all about auditing… You can read more at "Oracle Applications System Administrator’s Guide - Security Release 11i" - Chapter 5 - User and Data Auditing To get my posts directly to your email, you can register for email subscription by using the box at the right side bar. You are welcome to leave a comment. Aviad

Audit users in Oracle Applications

We have 2 types of audit in Oracle Applications : 1) Audit users who sign on to Oracle Applications 2) Audit changed data in database In this post I'm going to write about the first audit and the next post will be on the second type. It's all begins with the "Sign-On:Audit Level" profile. This profile can be set to one from four possible values at site level, and this is all what you need to do in order to enable it: 1) NONE – no audit enabled (Default value) 2) USER – audit user login to system, the logon time and the logoff time. 3) RESPONSIBILITY – audit all the above + which responsibilities the user chose and how long he stayed in each responsibility. 4) FORM – audit all the above + which forms the user used and how long he stayed in each form. Each level populated an audit information tables: 1) USER – populates the FND_LOGINS table only. 2) RESPONSIBILITY – populates FND_LOGINS and FND_LOGIN_RESPONSIBILITIES tables. 3) FORM – populates FND_LOGINS, FND_LOGIN_RESPONSIBILITIES and FND_LOGIN_RESP_FORMS tables. About those tables: FND_LOGINS – holds information about users login to system, when and how long. This table holds one row for each login. FND_LOGIN_RESPONSIBILITIES – holds information about changes of responsibilities, when and how long being at each responsibility. For each change this table holds one row with values that identify the user's login session, the user's current responsibility, and when the user is in the responsibility FND_LOGIN_RESP_FORMS – holds information about using forms, when and how long. This table holds one row for each form used in the same session with values that identify the user's login session, current responsibility, when and how long using each form. In order to see the user audit information, there are 5 reports (concurrents) that you can use: 1) Signon Audit Concurrent Request Show concurrent requests audit information – the user who run this request, when, from which responsibility and form, for all concurrent requests run in the system. 2) Signon Audit Forms Show audit information about which user enter to which form, when and for how long. 3) Signon Audit Responsibilities Show audit information about which user choose which responsibility, when and how long he stayed in each responsibility. 4) Signon Audit Unsuccessful Logins Show audit information about unsuccessful logins to Oracle Applications. 5) Signon Audit Users Show audit information about who sign on, when and for how long. - In addition there is a monitor screen where you can see online data about users connected to system, which responsibility and form they are using and how long they are connected. You can use this monitor from: System Administrator responsibility -> security -> User -> Monitor. - To inform users about unsuccessful logins to their account, you can set the “Sign-On:Notification” profile to Yes. For more information about audit you can read "Oracle Applications System Administrator’s Guide - Security Release 11i" - Chapter 5 - User and Data Auditing You are welcome to leave a comment. Aviad