12 Implement Audit Reports
This chapter contains the following sections:
12.1 Introduction to Audit Reports
Audit Reports creates a history of the changes that have been made to data in Oracle Fusion Applications. Audit Reports includes information such as what operation was performed on an item, when it was performed, and how the value was changed. The audit information is logged without any interaction from the end user.
The auditing solution brings together multiple underlying auditing frameworks, such as Oracle Fusion Middleware auditing that is based on an Oracle Platform Security Services (OPSS) framework, and Oracle Fusion Applications auditing that is based on an Oracle Fusion Middleware Extensions for Applications framework. The OPSS framework captures audit information through events that applications raise during their processing. The Applications Core framework creates a "shadow" table to record all operations on the base table.
12.2 Implement Audit Reports in the Oracle Fusion Applications Framework
Database auditing involves observing a database to be aware of the actions of database users. Audit setup provides a user interface mechanism for administrators to choose and select the objects and the specific attributes that need to be audited. When an object has been set to be audited using the audit setup user interface, any Data Manipulation Language (DML) actions on this object, that is, its underlying database schema objects, are captured and stored for reporting purposes.
The Audit Setup UI page is a task flow that is made available through the Oracle Fusion Middleware Extensions for Applications library. This task flow is available in Functional Setup Manager and can be included in an application that is accessible by the administrator. The UI consists of an Applications Tree Table that holds the structure of the auditable application modules, the children application modules, if present, and all the auditable view objects and their children. The administrator selects the view object on which to start auditing and in the applications table components below the tree table, the administrator chooses to add the attributes that need to be audited.
You also can set up the Fusion Middleware auditing as described in How to Set Up Auditing.
Metadata
Two database schema tables, FND_AUDIT_WEBAPP_AM and FND_AUDIT_ATTRIBUTES, hold metadata information that is required for Auditing.
-
FND_AUDIT_WEBAPP_AM captures the list of application modules for an application.
WEBAPP APPLICATION_MODULE VARCHAR2(80)
VARCHAR2(240)
-
FND_AUDIT_ATTRIBUTES table data is populated during runtime when view objects are configured using the Audit Setup UI. This is an internal table and should not be changed.
WEBAPP VIEW_OBJECT VIEW_ATTRIBUTE ENABLED_FLAG TABLE_NAME COLUMN_NAME VARCHAR2(80)
VARCHAR2(240)
VARCHAR2(80)
VARCHAR2(1)
VARCHAR2(30)
VARCHAR2(30)
12.2.1 How to Set Metadata
This section covers the steps needed to populate and define metadata needed to run the Audit task flows.
To populate and define metadata:
- Enable Audit on the Base Table.
You need to create a shadow table for each base table that is being audited. To create the shadow table:
-
Open the .table file in JDeveloper.
-
Right-click this object and select Properties.
-
In the wizard that is displayed, select User Properties.
-
In the list that is displayed on the right, scroll down to select the property
Enable Auditand set its value toY, as shown in Figure 12-1.Figure 12-1 Setting Audit on a Table
-
Save your changes.
-
A new .table file will be created. This table will have an underscore (_) at the end of the name.
-
Check in this new file.
Note: If your base table is enabled for multi tenant, make the same changes for the shadow table.
-
12.2.2 How to Set Up Auditing
As with most Oracle Fusion applications, Audit setup is done from Functional Setup Manager. When you start the setup, search in Functional Setup Manager for "Audit." When it is located, select Audit Setup.
A list of Oracle Fusion applications is displayed, as shown in Figure 12-2.
Figure 12-2 Initial Audit Setup Display
You will notice that some have only an Audit Level drop-down list that lets you pick Low, Medium or High levels. These are Oracle Fusion Middleware products that generate event-based entries in the table that is read by the Audit system, and the user just sets the granularity that is desired. (Refer to the product documentation and to the online help for a description of how the levels work in each particular product.)
In the example figure, click the Configure Business Object Attributes button to display the Configure Business Objects Attributes dialog. When it first displays, the fields will be empty.
To populate the Objects list, open the Application drop-down list and select the name of the application you want to audit. The example shown in Figure 12-3 uses the Applications Core Setup application.
Figure 12-3 Selecting an Application to Audit
There are three buttons at the top of the Configure Business Object Attributes dialog:
-
Click Save to save your work and remain in the Audit Setup dialog.
-
Click Save and Close to save your work and exit the Audit Setup dialog.
-
Click Cancel to cancel any changes you have made since the last Save action and exit the Audit Setup dialog.
Objects List
The Objects list has three columns:
-
Audit
There is no limit to the number of levels that can be included in an audit.
You will notice that items with the same name are displayed and can be selected. The item at the top of the tree is the root item; the other listed items of the same name are reflections of the root item and are not separate. That is, if a view object appears multiple times in the Setup list, there really is only one view object. Selecting or deselecting any instance of that view object will select or deselect all instances.
Example:
You pick a view object and select five of its attributes to audit and save. If you then choose the same view object, but somewhere else in the setup UI, it will have those five attributes already selected. Now select two more, so that there are seven attributes selected. When you return to the first instance of the view object, it also will show seven attributes selected.
Similarly, if you delete one of the five selected attributes, all instances of the affected view object will show only four selected attributes.
Another way of saying this is "last edit wins."
Any item that is checked in the Audit column will have all its selected attributes audited. Unless the view objects' Attributes list has been edited, the attributes that will be audited are default attributes.
If you edit a view object to select specific attributes, the Audit box must still be checked to audit the selected attributes. (You can edit the attributes, but that, by itself, does not mean they will be included in an audit, unless the Audit box is checked.)
-
Name
The names of the application modules and view objects.
-
Description
A brief description of the associated application module or view object.
The Objects list has two menus.
-
Actions menu
The Actions menu provides the Synchronize option.
Attributes of objects that are being audited are tracked. On a patch or update, or if customized attributes, such as flexfield attributes, have been added, they must be tracked if they are meant to be included in the audit. That is, the attribute was marked as
<fnd:FND_AUDIT_ATTR_ENABLED Value="true"/>. Synchronize will do this.Note: Synchronization is carried out by product. That is, it will synchronize all objects in the current product shown in the UI, if those objects have been selected for audit at any time previously.
Flexfield and custom attributes are always included by default (
<fnd:FND_AUDIT_ATTR_ENABLED Value="true"/>), so with these you want to be sure to do a synchronization. -
View menu
Use the View menu to select the columns you want to display, and to expand and collapse entries in various combinations.
Audited Attributes List
When you click a view object entry in the Objects list, its attributes that will be included in an audit are displayed in the Audited Attributes fields. Note that the name of the selected object is added before the Audited Attributes heading; for example Document Entities: Audited Attributes.
Note:
The permitted limit on selected attributes is 20,000 bytes (num_attributes x attribute_size) for a given view object in the Audit Setup UI. This limit is checked during setup. Note that custom text attributes are pre-allocated with 1,500 chars per field. Therefore, it would only take 13 Text fields to consume the available character width.
These are not necessarily all the attributes of the selected view object; they either are the default attributes or the attributes selected during a previous edit. To see a list of all a view object's attributes, select Actions > Create, or click the Create icon. A dialog, similar to that shown in Figure 12-4, lists all the attributes and lets you shuttle them between the Available Attributes and the Selected Attributes.
Figure 12-4 Selecting Attributes to Audit
Click Save and Close to save your selections. Remember that your selections will not be included in an audit unless the related Audit checkbox also is selected.
12.3 Using Audit Reports
Open Audit Reports from the Navigator menu.
The Audit Reports Search page, shown in Figure 12-5, is displayed.
Figure 12-5 Default Audit Reports Display
To perform a search, a Date value and either a User or Product value are required. Use the Date picker to select the date, and the drop-down lists to select a User or a Product or both. The search results can be further fine-tuned by using the Event Type, Business Object and Description fields, and the Include child objects checkbox.
Note:
These parameters can be saved and recalled by selecting the user-supplied name in the Saved Search drop-down list. See also"If the search parameter set you are using is one that you expect to reuse, click Save to display the Create Saved Search dialog that has these options:"
12.3.1 How to Search in Audit Reports
To search for Audit Reports for an Oracle Fusion application, you must supply this information:
-
Date. Select an operator, such as Equals, Before, After or Between, and click the picker to display a calendar from which to choose a date.
-
User or Product or both.
When you use the drop-down to select a User, you will notice a Search link at the bottom of the list. Click it to access all the available Users. The dialog, shown in Figure 12-6, is displayed.
Figure 12-6 Searching for a User
You can enter a name or part of a name in the User field and click Search. You also can click Advanced to add a search operators drop-down list, as shown in Figure 12-7.
Figure 12-7 User Name Operators
To select a user, highlight the name in the search results, shown in Figure 12-8, and click OK.
Figure 12-8 Search Results for User
-
Event Type. Note that the first drop-down list always is set to Equals. Use the second drop-down list to select any or a combination of the available types. You also can select All.
For Oracle Fusion applications, the Types are Object Data Insert, Object Data Update, and Object Data Delete.
For Oracle Fusion Middleware, the Type selection is dynamic and depends on the selected Product.
-
Business Object Type. Note that this field applies only to Oracle Fusion applications and not to Oracle Fusion Middleware.
-
Description. You can further limit the search results display by entering a string in this field that matches a string in the Description column of the search results.
-
Include child objects. If there is a hierarchy of view objects, such as Opportunity > Revenue Line > Revenue Line Split, and the search is done on Opportunity, it only checks the Opportunity view object for changes. But if this option is checked, it will search all children. So if an Opportunity has five Revenue Lines and each has some Revenue Line Splits, then if anything in any of them changed, they also would be reported.
When the necessary fields are filled-in, click Search to display the results in the Search Results table.
Note:
When a search is performed for broad criteria, such as "Specific Product Before Today" or "All Products Before Today," Audit Report may take a long time to query Audit History.
To avoid intermittent connection reset errors, the search time is limited to 5 minutes. If the History search time exceeds 5 minutes, this message is displayed: "Query run time exceeded the permissible time limit and failed to display results. Refine the search criteria and try again."
Deleting an Object Referenced by a Foreign Key
In some instances, resolving a foreign key may fail when an audit row display name is trying to do a foreign key lookup of an object that has been deleted. It then will display the raw value or just leave it blank.
One example of this behavior involves trying to get a foreign key resolution from the same base object. If rows were deleted from an object, there will not be a row in the base table and foreign key resolution may fail.
Another example involves foreign key resolution with a parent table. For instance, given a person object, a person email object may use foreign key resolution to show a person's name instead of a person ID. If the person's name has been deleted in the base table, the foreign key resolution for the person email object may fail because there is no row in the corresponding base table.
Click Reset to clear all the fields so you can enter new search parameters.
If the search parameter set you are using is one that you expect to reuse, click Save to display the Create Saved Search dialog that has these options:
-
Name
This option is mandatory.
This field defaults to showing the name that is displayed in the Saved Search field. However, if the Saved Search displayed name is "My Recent Changes," the Name field here will show "My Recent Changes copy."
-
Set as Default
Select this option to designate this named set of search parameters as the default instead of My Recent Changes.
This option can be set and cleared by opening the Saved Search drop-down list and selecting Personalize.
-
Run Automatically
Select this option to run this named set of search parameters automatically.
This option can be set and cleared by opening the Saved Search drop-down list and selecting Personalize.
-
Show in Search List
This option is available only from the Saved Search Personalize option, and is active only for the My Recent Changes named search.
12.3.1.1 Using the Search Results Table
The Search Results table has these components:
-
Actions menu
-
Export to Excel icon that duplicates the function of the Actions menu option of the same name
-
Detach icon
-
Show Attribute Details checkbox. This option becomes active when the Business Object Type field is populated and a search has been performed.
When this option is selected, a dropdown list lets you select either All Attributes or Select Attribute.
Figure 12-9 Attribute Scope Selection
-
All Attributes
When this option is selected, three columns are added to the table: Attribute, Old Value and New Value. Attribute contains the attribute name. An Old Value will be shown if there was an old value. New Value shows the current value. A scroll bar will become available at the bottom of the display so you can view all the columns.
-
Select Attribute
Select this option when you want the information for only one specific attribute. When selected, a popup similar to this example is displayed.
Figure 12-10 Select a Specific Attribute
You must click the Business Object to populate the Select Attribute list.
Click OK to apply the change to the table.
The search results table now shows only results with the selected attribute, plus the Old Value and New Value columns that are shown when the All Attributes option is selected
-
- Show Extended Object Identifier Columns checkbox.
When this option is selected, three new pairs of columns are added to the Search Results table: Context Name1 with Context Value1 through Context Name3 with Context Value3. These are additional information to help identify the object. For instance, if you need a 4-part key to uniquely identify an object, the Description could be 1 and these 3 context values could be the remaining.
Note that when a search is conducted on a view object, the search result also will include rows from the child view objects.
Actions Menu
The Actions menu has the Export to Excel option that is duplicated with the Export to Excel icon. Select the menu option or click the icon to export all the entries in the Search Results list to an Excel XLS file. When this option is selected, the standard Save prompts will guide you through the process.