Data is an extremely crucial and sensitive resource for any enterprise that regularly stores classified, confidential business data and information such as customer records, credit card information, and financial details. At the same time, it is essential to understand that corrupt, erroneous, or compromised data is equivalent to lost revenue, lost time, unhappy customers, or reduced employee satisfaction. Data repositories on Oracle Database servers need a high-priority data security provision to safeguard from internal and external security breaches.
People and business processes commonly use multiple applications and technologies. Database auditing is vital to application logging and security monitoring as direct database access can be used to bypass application controls. By default, upon installation, Oracle E-Business Suite only audits and logs a limited set of information including:
- Basic user login information
- Unsuccessful password attempts
- Concurrent program execution
- Creation and last update information for most records
E-Business Suite Audit Trail
Any Oracle E-Business Suite table can be chosen to have row changes audited, and auditing can be performed at the row or column level. By default, tables are not audited. Auditing frequently accessed tables (specifically: transactional tables) may cause severe database performance issues; therefore, auditing must be meticulously designed. Audit Trail information is accumulated and stored in separate database tables detailing the user information and types of updates.
Steps to Configure E-Business Suite Audit Trail
Oracle E-Business Suite Audit Trail functionality only tracks inserts, updates, or deletes; whereas the database’s native auditing capability can also track selects on tables. Oracle E-Business Suite Audit Trail maintains a comprehensive history of changes made at the table and column level. Audit Trail is enabled by a shadow table (table name appended with “_A”) of the audited table and triggers on the audited columns. A concurrent program is used to create the shadow table and triggers.
Auditing database row changes is very resource intensive and can cause significant database performance problems. Prudent planning and reviews by a DBA must be completed before allowing any auditing. Only a minimal amount of auditing should be done, and it should be limited to non-transactional data. Any auditing on transactional data may cause substantial performance degradation of the entire application. Tables with more than a few changes an hour should not be considered for row-level auditing.
Exhibit 1: Steps to Configure E-Business Suite Audit Trail
You can review what has been set up for auditing. This functionality is accomplished by a navigation tree that starts with the industry template and drills down to groups, tables, and columns. With the navigator, you have a drill-down view of what columns are being audited. A search capability is provided for searching a specific table or column on the tree.
Exhibit 2: Definition of Roles and Responsibilities
Stopping the Audit Trail
It is required to set the audit group state to either “Disable-prepare for archive”, “Disable-Interrupt Audit”, or “Disable-Purge Table” and run the “Audit Trail Update Tables” report to stop auditing.
| Variable | Description |
| Disable – Prepare for Archive | Copies the current values of all rows in the audited table into the shadow table, and then disables the auditing triggers. This option requires the most space since there is at least one row in the shadow table for every row in the audited table (and another row in the shadow table for each transaction on the original row in the audited table). You should then archive the table before you empty the shadow table. |
| Disable – Interrupt Audit | Modifies the triggers to store one final row in the shadow table as the audited row is modified in the audit table (remember that a given row in the shadow table represents the data in the audited row before an update). Inserts or further changes are no longer audited. The shadow table then grows slowly, and the data may be accessed by the existing audit views. |
| Disable – Purge Table | Drops the auditing triggers and views and deletes all data from the shadow table. |
Exhibit 3: Variable Descriptions
The Audit Trail definitions (and auditing) do not go into effect until the Audit Trail Update Tables Report has been run. If any changes are made to the definitions later, then the program should be re-run. To submit the Audit Trail Update Tables concurrent request, it is suggested to use the standard submission (Submit Reports) form. Upon execution, the Audit Trail Update Table concurrent request performs the following tasks:
Exhibit 4: Tasks Performed After Concurrent Request
Sharing an Example – How to Enable Audit Trail on Oracle E-Business Suite
a) Set the Audit Trail Profile Option:
The System Profile Option “AuditTrail:Activate” must be set to Yes, the default value for “AuditTrail:Activate” is null (which equals No).
b) Ensure that Audit on the Application is Enabled:
Considering an example of the FND_USER table whose owner is APPLSYS, you should query on APPLSYS to ensure that Audit is enabled.
Navigate to the System Administrator Menu Security/AuditTrail/Install.
c) Define the Audit tables and add the desired columns on which you want to enable Audit Trail.
Navigate to the System Administrator Menu Security/AuditTrail/Groups.
d) Create a New Audit Group and Associated Tables:
Navigate to the System Administrator Menu Security/AuditTrail/Groups. Create the group and add the audit tables to the group [you can add more than one table if desired].
e) Run the concurrent program “AuditTrail Update Tables”:
Running this request will create the audit tables and the triggers that will manage the audit data.
f) Run the concurrent request “AuditTrail Report for Audit Group Validation” to validate the success of the Audit Table/Trigger creation.
g) Define an Industry Template containing the Audit Group:
Navigate to the System Administrator Menu Security/ Audit Trail/ Audit Trail Reporting/Audit Industry Template.
h) Create a new user and “end date” the user to test the Audit Trail by running the Audit Report.
Navigate to the System Administrator Menu Security/ Audit Trail/ Audit Trail Reporting/Audit Report.
It will submit the concurrent program “Audit Trail Report” whose output can be used for Audit reporting.
Devising the Right Auditing Strategy for Oracle E-Business Suite
Although auditing is relatively inexpensive, it is advised to restrict the number of audited events as much as possible. This helps to minimize the performance impact on the execution of audited statements and the size of the audit trail, making it easier to analyze, scrutinize, and understand the outcomes of the audit.
Use the following general guidelines when devising an auditing strategy:
- Have a clear understanding of the reasons and purpose to prevent unnecessary auditing.
- To have a clean audit, determine the types of activities and the amount of time necessary to gather the information.
- Only audit targeted database activities to reduce the Audit Trail administration.
- Archive audit records and purge the audit trail after the required information has been collected.
- Create one or more indexes of the shadow table to speed up report generation.
Note: Indexes decrease performance during transactions auditing, so indexes should be dropped from the shadow table when the reporting is done. Another alternative is to move audit data to an operational data store for reporting purposes.
Conclusion
Oracle E-Business Suite has an auditing mechanism called Audit Trail. Audit Trail allows administrators to manage and maintain a chronicle of changes: what changed, who changed it, and when it was changed. This amazing functionality holds a full history of modifications made at the table level. With Audit Trail, it can be effortlessly determined how a particular data row or element acquired its current value. Most of the field information; including character, number, and date fields; can also be tracked easily to get further insights that may hold significance for the business.
Syntax offers comprehensive consulting services for leading databases and ERP applications, enabling companies to leverage our in-depth knowledge to negate this significant threat to business operations. We assist companies in securing their largest and most important applications through detailed security audits and actionable recommendations.