What is an AuditTrail?
An AuditTrail is one of functionality for retaining a history of changes to data. What ,who and when can be identified on a particular table or column if the functionality is enabled.
When you enter or update data in your forms, you change the database tables underlying those forms. An audit trail tracks which row in the database was updated at what time, and which user was logged in using the associated form(s).
If you are seeking auditing ability to track changes on a particular table of Oracle this post might helpful to you.
Enabling the Functionality of AuditTrail
You can turn AuditTrail on or off (Yes or No). Normally the default setting is No (Off). When you enter or update data in your forms, you change the database tables underlying the forms you see and use. AuditTrail tracks which rows in a database table(s) were updated at what time and which user was logged in using the form(s). Also..
- Several updates can be tracked, establishing a trail of audit data that documents the database table changes.
- AuditTrail is a feature enabled on a form-by-form basis by a developer using Oracle’s Application Object Library.
- All the forms that support AuditTrail are referred to as an audit set. You should also note not all forms may be enabled to support AuditTrail.
- To enable or disable AuditTrail for a particular form, you need access to Oracle Application Object Library’s Application Developer responsibility.
- Users cannot see nor change this profile option.
- This profile option is visible and updatable at the site and application levels.
The internal name for this profile option is AUDITTRAIL:ACTIVATE.
Setting Up AuditTrail(>11i )
You can choose to store and retrieve a history of all changes users make on a given table. Auditing is accomplished using audit groups, which functionally group tables to be audited. For a table to be audited, it must be included in an enabled audit group.
The steps for setting up AuditTrail include:
Yuu need to verify Select Privileges on SYS.DBA_TABLES
Have your database administrator grant SELECT privileges on SYS.DBA_TABLES to the APPLSYS account. Normally, this step would Normally taken care during the installation of Oracle.
Define Audit Groups
This is very very important.These are groups of tables and columns, where you do not necessarily need to include all the columns in a given table. You enable auditing for audit groups rather than for individual tables. You would typically group together those tables that belong to the same business process (for example, purchase order tables see at the end).
A given table can belong to more than one audit group. If so, the table is audited according to the highest “state” of enabling for any of its groups, where Enabled is the highest, followed by Disable Dump Data, Disable No Growth, and Disable Purge Table, in that order.
Navigation: Security -> AuditTrail -> Groups
Define Audit Installations
You choose the registered Oracle IDs at your site that you want to audit. This allows you to audit across multiple application installations. When a table is added to an audit group, auditing will automatically be enabled for all installations of the table for which audit is enabled.
Navigation: Security -> AuditTrail -> Install
Run the Audit Trail Update Tables Report to Enable Auditing
Your AuditTrail definitions (and auditing) do not take effect until you run the Audit Trail Update Tables Report. If you change any of your definitions later, you must rerun this program. You run the Audit Trail Update Tables Report from the standard submission (Submit Reports) form.
Audit Trail Update Tables Report
This program creates database triggers on the tables in your audit groups for your installations. It also creates shadow tables, one for each audited table, to contain the audit information. If you have changed your audit definitions or disabled auditing for an audit group, the program drops or modifies the auditing triggers and shadow tables appropriately.
The program also builds special views you can use to retrieve your audit data for reporting.
You can check SQL*Plus to see if the Shadow Tables have been created or not. Shadow Table name is the same 26 Characters of the Table being audited followed by a suffix of “_A” ,suffix of “_AI” for Insert Triggers, “_AU” for Update triggers , “_AD” for Delete Triggers,suffix of “_AIP” for Insert Procedures “_AUP” for Update Procedures and “_ADP” for Delete Procedures.
AuditTrail Limitations
These are limitation of AuditTrail:
- Your table should consist of maximum 240 columns
- You cann’t use the column for audit trail whose data type is LONG, RAW, or LONG RAW
- Your audit group must include all columns that make up the primary key for a table; these columns are added to your audit group automatically.
- Once you have added a column to an audit group, you cannot remove it.
- AuditTrail requires two database connections. If your operating platform does not automatically support two database connections
(e.g., VMS or MPE/XL), then add the environment variable ‘FDATDB=<database connect string>’ to your environment file.
- Because the structure of the audited table may change between product versions, AuditTrail does NOT support upgrading existing shadow tables or audited data. Before an upgrade, you should archive the shadow tables and perform all necessary reporting on the audited data.
- Oracle recommended Disabling AuditTrail feature Prior to Upgrade to higher version.
- If your plan is to use some auditTrail on table which name is bit longer(> 26 characters), you can’t achieve this. As there is Bug(#3872242
) reported by Oracle that restrict you from this, as AuditTrail Update Tables Errors out on audited tables that are > 26 characters.
Sometime back we found while enabling this on these tables.
- PO_REQUISITION_HEADERS_ALL
- PO_REQUISITION_LINES_ALL
- PO_REQ_DISTRIBUTIONS_ALL
What are the primary Auditing Tables?
- FND_AUDIT_COLUMNS
- FND_AUDIT_GROUPS
- FND_AUDIT_SCHEMAS
- FND_AUDIT_TABLES
Client Dilema : HOW TO ENABLE AUDITING AT “FORMS” AND “USER” LEVEL TOGETHER
Customer has set the profile “Sign-on: Audit Level” to FORMS to collect information about the forms sessions at a particular time .
By default the value for this profile was USER. Since he has set the value to Forms can he collect the User related information
Yes you can …
If the profile “Sign-on: Audit Level” is set to FORMS then it will collect Form sessions information in addition to user session information.
So you will get information related to User session as well as forms session. There is no harm in setting this profile value to FORMS.
Print This Post
|
Email This Post
Background
What is a S.W.I.F.T?
Steps by step: Enabling audit trial
Oracle Payable
Over the coming weeks , I will feature a series(SAP-ORCL) of posts on some functionality gap among two great ERP product: SAP and Oracle.The posts will include comparative study ,answers in two product dimensions,the functionality gap and ofcourse end user/FinanceIT/ISD/IT department frustration with Oracle EBS which was replaced with SAP, as whole in term of initial acceptance.
Is security defined by user, user group or a combination? Can this be set by Ledger/company/module or is it across the system?
In oracle security is defined by user - Responsibility -menus ,request set , which can be easily customized or tailored able and can be reused so can use include/exclude functions.Menu Exclusion is also embedded to exclude from standard menu list.