Tuesday, January 18, 2011

Audit Trail Introduction - Part1

Auditing

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.

2 comments:

  1. Can you please share the navigation for the Audit Setup link on Fusion. Is this within any application

    ReplyDelete
  2. Can you share how the delete operations are handled and how the user detail is set if the user who created insert entry is different then one who did delete entry

    ReplyDelete