Application Design Issues
To get the most from audit trails, the following application design issues must be considered:
- The right username
- Comparing binary data
- Security features
- Archiving and restoring
Audit Trails with the Right Username
Typically, Oracle stored procedures that execute application logic and processing take an input parameter for the user name. This is mainly to tide over issues like INVOKER and DEFINER rights. The username passed as a parameter is recorded in the audit trail for operations.
When the computer directly captures data from a data source (like a log file) or from a sensor, the audit trail for the table must report a special user called, say, APPLICATION. The same applies for data that is imported. The data should correctly indicate that a user called IMPORT or APPLICATION is importing it.
When a job is triggered using DBMS_JOB or an OS-based scheduler, the audit trail should report these operations as being carried out by APPLICATION. However, when a specific user carries out the configuration of the job, and (from the system designer's viewpoint) the job runs on behalf of the user, the audit trail should report the user who configured the job.
A tricky situation arises when an automatic state change operation needs to be audited. Consider a case where a person accumulates some additional frequent flyer miles and the traveler is upgraded to the next level. The system automatically will carry out such a change. Again, in such cases, the audit trail should report the upgrade operation as being carried out by APPLICATION rather than the person at the check-in counter. The check-in transaction will continue to have the audit record with the name of the person at the check-in counter.
Comparing Binary Data in Audit Trails
When storing binary data, reporting what changed between versions of the record is a challenge. It is difficult for a user to visually compare them or for a reporting engine to point out changes. Often, third-party utilities or components are required to compare the differences between, say, two Excel sheets or two Visio diagrams. The same is true when encrypted data is involved.
Application Security Features
Applications can make use of Oracle resource profiles to ensure security features like idle connection time (disconnect after n seconds of inactivity) and password management features like grace time, reuse time, and lock time. This way, you can age passwords, mandate change, and enforce security.
Applications should provide for creating and assigning privileges such that a distinction is made between a data-entry operator, a reviewer, and an updater. USER_ROLES, USER_PRIVILEGE, and USER_GROUPS are typical application mechanisms that satisfy this requirement.
Every database application has certain utilities that typically bypass some aspect of system security. Running these applications should be governed by clear procedural guidelines.
Many applications work with a single database user. Access to this user's password should be restricted. Applications should evolve a mechanism that eliminates the requirement to hard-code this password, allowing it to be either a dynamic password (valid only for a time period) or a system-generated one based on some key that may be changed. The same applies to the reserved/system user within the application.
During the application logon process, the network traffic between the application and the database server should not be vulnerable to sniffing that would expose data.
Archiving and Restoring
Archiving and restoring are not just about audit trails, but they deal with the larger issue of electronic records. All archival utilities must be designed to archive metadata along with the data itself. Such utilities should also record which records were archived to validate all restoration operations. The archived data set should be accompanied by a checksum to ensure that it can be restored after a validity check. The checksum and the record of the archival operation can be used to determine whether the restore should be allowed or not. Checking against the record of archival operations may be relaxed if restoring is a means to perform the pre-loading of data after an install, but this is not a recommended practice.
A critical issue in restoring old data is backward compatibility. Archived data must be in a format that is guaranteed to be readable throughout the system's years of operation. This very definition precludes binary formats, as a number of issues can crop up (e.g., 16-bit, 32-bit, and 64-bit compatibility, endianness, etc). CSV and SYLK are two formats that have largely withstood the passage of time. XML is now emerging as the successor to these formats, especially in newer applications. In all cases, however, the format descriptions should be available and application vendors should be mandated to include backward compatibility checks in their testing cycles and validation processes.
Such a validation process is very important because de-support is a potent mechanism that software and hardware vendors use these days to force customers to upgrade. Under no circumstances should a customer be forced to install and use a specific version of software just as a go-between to read the archived data. Backward compatibility of archived data is a promise that should be adhered to across all versions of the software, not just supported versions.
Applications usually provide a special mode called repair/maintenance mode in which the IT staff may run an executable file (or a script) that places the database and the application in a repair mode. They then can perform some modifications to the data (again, by means of some scripts/repair utilities) and restore the mode to normal. All runs of such scripts/utilities should be recorded.
Application and Oracle migration should be carried out in a regulated manner with records maintained for all the actions scripts perform. The site DBA should formally review these scripts for intent, mistakes, and effects on data integrity.