Tuesday, January 18, 2011

Audit Trail Introduction - Part1


Oracle Database offers several different ways to Audit data. But a new feature has been added to Audit in the V1 release. This feature is different from other Audit providers mainly by:

1. It displays new and old values for every row changed
2. Auditing can be enabled based on View Objects rather than database tables. Removing the need for prior knowledge on what tables a specific view is based on.

I will introduce Audit and how to use it and uptake it in this blog. I might not be able to fit in all information in one blog so I am planning on dividing it into parts. I do not want to clutter information and also want to maintain a clean separation between each feature we delve into.

This first part gives a high level view of how the Audit Trail UI and also a small part on the architecture.

Audit Introduction:

Audit Trail is a history of the changes that have been made to data in the Fusion applications. Audit Trail includes information such as who has accessed a business object (view object), what operation was performed on it, when it was performed, and how the value was changed. The audit information is logged without any interaction from the end user.

Fusion Application administrators can configure which business objects, attributes & actions are to be audited. In Fusion V1, audit trail is enabled only at the data level. Only Create, Read, Update and Delete (CRUD) operations for the objects are tracked. Audit will work only on business objects that are based on entity objects and will not work with query based business objects.

For V1
  • Only Out of the box objects and Custom objects will be auditable. Custom Objects are VOs generated by JEDI.
  • Support to audit Flex generated VOs will be in a later release. Note that an admin, MUST go in and turn on auditing for each attribute.
  • The seeding only makes the objects appear in the setup UI.
  • Custom Objects are identified by the property "ExtnCustom" set on them by JEDI.
  • All taskflows, both setup and reporting, are constrained to the current webApp. This is since the AMs and VOs involved must be able to be read at runtime. So in general, it is expected to have setup/reporting per webApp.
Audit Trail UI consists of two parts,
1.Audit Setup and
2. Audit Reporting. Audit reporting is further divided into
    2a. Object Specific Reporting
    2b. Admin Reporting

Audit Setup UI

  • Audit Setup is a taskFlow.
  • It shows the structure of View Objects under a Application Module that has been marked as legible for auditing. It is shown in a Tree Structure - Objects.
  • The Tree consists of the structure as well as description and whether the View Object is currently been setup to be audited. That is admin has enabled audited on this View Object. Please do not confuse between an object being legible for auditing and when audit is enabled. When I say object is legible for auditing I mean that at Design Time the right property was set and data was seeded to make this object legible for auditing. Only those objects that are legible for auditing are displayed in the tree. Whereas if an object that is shown in this tree of legible objects is enabled for auditing by following the steps described next, the last column will show Enabled as 'Y'.
  • The detail table below allows an admin to enable auditing. An object is selected from the tree and the New button has to be clicked. This brings up a popup with a shuttle filled with attributes. Select the attributes you want to audit and click on Save and Close. This will close the popup and show the selected attributes in the table.

  • Now the enabled flag in the tree column is changed to Y.
  • Ways to stop auditing an object
          1. By selecting the desired object and clicking on "Stop Audit" button in the toolbar of the tree.
          2. By selecting the desired object and then selecting the attribute (you can delete all of them one by one) and clicking on delete button in table tool bar. This asks for confirmation.
          3. By de selecting values in the shuttle that comes up on clicking 'New' in table toolbar and then clicking Save and Close button on the popup.

Audit Reporting UI

Now let us have a look at the reporting UI. We will go through both the Admin and Object specific reporting UI. Thought they both similar in most terms, the only difference is that Object specific UI is for the sepcific object for which it has been set for. i.e. The taskflow for Object Specific reporting UI takes the object name, primary key values. For example the report can be specifically for changes that took place in department 40.

Admin Reporting UI

  • The admin specific Audit UI opens the above UI . Only on clicking search does the report display data, this is done for performance reasons so that users can narrow down their search as much as possible for better performance.

  • The object name is a required value and it can be selected from the drop down list. You can then see the results based on the from and to date provided. By default this is set to one week apart but can be changed as needed.

  • The operations is a drop down list which allows you to narrow down searching on Create, Delete, Update statements or All statements.

  • Include child objects shows related child object changes in the results. For example if this report was showing audit trail for department object, it will also include changes for the employee object that is associated with view link.

The results shown in the table are:

  1. Date - The date on which this change occurred in the database
  2. Object Name - The name of the attribute prefixed with the primary key value.
  3. Object - The view object that contains this attribute
  4. Related Object - Parent Object if this Object was a child object associated with view link
  5. User - Logged in user by whom this change was made
  6. Attribute - The name of the column in database table changed
  7. Operation - The operation that took place on this database table row. (Insert, Delete, Update)
  8. Old Value - What was the old value before this change (is empty for insert operation)
  9. New Value - What is the old value changed to (is empty for delete operation)

Object Specific Reporting UI

The object specific task flow is used when users want to know the Audit trail for a specific object while browsing through the application without the need to go look for the Admin Report. The advantages of object specific reporting are
1. Can be embedded in another page that contains object specific data for easy viewing
2. Can restrict access to which Audit data can be viewed for specific page/navigation
3. Provides narrowed down data without needing the user to narrow it down

  • The object specific reporting UI is similar to admin UI except that you cannot select the object or give the object name in the UI. These are passed as taskFlow parameters. Along with these two you can also pass in to date and from date.

  • The header for the Object Specific UI page shows the Object and the Object Name for which this page is shown. Need to click on search to see data.

Important Points

1.   Audit Trail setup and reporting currently only works with one primary key
2.   It is expected that users will use discretion and narrows down results for better performance
3.   Audit Setup and Audit Reporting UI's can be exported to excel sheets. In setup, only the tree data is exported and does not contain information on the attributes being audited.
4.   The user list is from PER_USERS table
5.   Audit setup handles custom objects and also allows search on a specific user key defined by the developer. More to come on this in next parts.
6.   The object name you search on in the reporting UI should be a part of the object that is selected from the drop down. It does not consider if the object name is from the parent, while the selected object is a child        object.
7.   queryPanelExpanded parameter can be passed to Object Specific taskflow. If set to false, then query panel will be collapsed when taskflow is loaded.
8.   View Objects configured for specific WebApp should only be shown in LOV.
9.   Enterprise Id search - Report shows data only specific to the Enterprise id.
10. Data Security - If Data Security is enabled on object, then the report shows only Data Security enabled rows, otherwise it will show all rows.
11. Only columns which are enabled through Audit Setup will be shown in Audit Report.
12.  Primary Key column may be enabled for auditing, but will not be shown in report.

Architecture of Audit Trail

The Audit Trail is based on Oracle Audit Vault. A product made available by Oracle for audit purposes. You can read more about it here. The Audit Vault consists of the agent and the server. Agent is responsible for collecting data and the server is responsible for maintaining this collected data.

Oracle Audit Vault was used for this approach since this was the only product with collectors that collected old and new values. It maintains a history of the values changed.

For an application the source DB is the ApplicationDB which has the collectors on them once the agent is installed. These collectors are then invoked when a capture rule is set on a table. This is done from the Admin Setup UI for Audit.

The data that is collected is stored in the Audit Vault Server, which is a separate database. The Audit Report UI connects to the Audit Vault Server to retrieve information to be shown in the report.

A dblink needs to exist between the source DB and the Audit DB.

In part 2 we will continue with steps for Audit Uptake.

Creating Pages with a UIShell template

UIShell template was created to maintain a consistent look and feel across all Oracle Fusion Apps. Here is an excerpt from the documentation on UIShell:

The UI Shell is a page template containing default information, such as a logo, menus and facets. To supplement the UIShell template, there also is a UIShellMainArea template. Because you can load information into dynamic tabs, the Main area cannot be a part of the page itself since it is loaded dynamically. The UIShellMainArea template helps you create the flows that run within the tabs.
The UI Shell design supports task-based and user-based navigation and way-finding, and organizes screen real estate more effectively by collating tasks, providing dedicated spaces for primary-task supporting information, and maintains general order and appropriate hierarchy between various elements on the screen.
The UI Shell for Applications User Experience (Applications UX) patterns provides a system of containers that fulfill common layout and navigational requirements in a structured, consistent manner. The UI Shell focuses on providing detailed design for defining and organizing various types of navigation and other functionality such as search and auxiliary information for Oracle Fusion alone.
In particular, the UI Shell template supports
  • Global Search
  • Navigation menus
  • Cross-application navigation

The UIShell consists of four regions Global Area, Regional Area, Contextual Area and Local Area. Check figure for regions:

The steps to create a simple UIShell page with a 'Hello World' displayed in local area are

1. Create a new fusion web application
2. In the Model project add the Applications Core library
3. In the View Controller project add the Applications Core(View Controller) library under JSP Tag libraries
4. Create a JSFF page (JSF Page Fragment) with UIShellMainArea as the template:
5. Add an output text in the local area facet of this JSFF
6. Create a taskFlow and drag and drop this JSFF onto it.
7. Create a JSPX page (JSF Page) extending the UIShell template
8. Right click on this JSPX page and select 'Create Applications Menu', this creates a XML file with the name of the JSPX page appended with _taskmenu. It is created under the ViewController/public_html/WEB-INF/menus directory. The menu file is currently empty with only template code.
9. In the structure window for this menu file, right click on item node and select -- insert inside item node -> item node. This will expct you to give an ID and a focusViewId. Id is the id of this item node and can be anything as long as it is unique in this menu file. The focusViewId is the ID of the page which we created in step 7. (Will explain further below).

10. In the property inspector for the newly created item node add
Label : Hello World Page
Task Type : dynamicMain
Task Flow Id : Choose the id of the taskFlow created in step 6.

<?xml version="1.0" encoding="UTF-8" ?>
<menu xmlns="http://myfaces.apache.org/trinidad/menu">
  <itemNode id="_pgNode_UIShellMainPage" label="UIShellMainPage"
            focusViewId="/UIShellMainPage" isDynamicTabNavigation="true">
    <itemNode id="HelloWorldId" focusViewId="/UIShellMainPage"

11.  Right click on the jspx page and run it.

Further Explanation:

We give the focusViewId to let the menu know that when the page fragment is displayed, it needs to be displayed in the local area of the given UIShell template based page. In this case the UIShellMainPage.jspx. The created pages are already available and need to be just selected.

Task Type can be either defaultMain, dynamicMain, defaultRegional or taskCategory.A default main task comes up by default once the page in run. i.e. the JSFF is displayed in local area by default and no action from user is necessary.

A dynamic main task needs another attribute defined in the property inspector - label. Since a dynamic main comes up as a list in the tasks part of regional area it needs a label and also needs the user to click on this link for the page to be displayed. A dymanicMain task type also requires another itemNode defined for displaying the task menu.

<?xml version="1.0" encoding="UTF-8" ?>
<menu xmlns="http://myfaces.apache.org/trinidad/menu">
  <itemNode id="_pgNode_UIShellMainPage" label="UIShell Main Page"
            focusViewId="/UIShellMainPage" isDynamicTabNavigation="true">
    <itemNode id="__TasklistTests_itemNode__SRDefault"
              label="#{applcoreBundle.TASKS}" taskType="defaultRegional"
    <itemNode id="HelloWorldId" focusViewId="/UIShellMainPage"
              label="Hello World Page"/>

A default regional task type displays the jsff in the regional area under the listed tasks.

isDynamicTabNavigation provides an option to suppress dynamic tab navigation and just display one main area at a time. Other menu metadata stay the same. Tasks List will continue to render. Clicking a Tasks Link will replace the current main area task flow with the new one. Multiple defaultMain definitions are allowed and will open multiple tabs on page load. The first one with disclosed="true" will be the tab in focus. If the property value is not defined, it defaults to true.