Happy (Audit) Trails to Oracle E-records Management : Page 5
Mandated compliance with various regulations has placed an emphasis on data protection, retention, and security lately. This guide gets past the legalese and tackles electronic records management in Oracle using audit trails.
by Shirish Joshi
Aug 9, 2004
Page 5 of 5
Deployment/Procedural Issue Checklist
This article has looked at some of the issues and precautions that must be taken to ensure that audit trails and data integrity lead to useful and usable data. The guidelines it has presented will help establish compliance with the various legal frameworks for Oracle-based database applications.
The following checklist helps address the deployment and procedural issues one might face when applying the guidelines:
A client/server or multi-tier application must always rely on the Database Server time as the time for the audit trail. The time across nodes and PCs also must be synchronized.
Physical access to the systems and the storage media where archives and backups are stored should be restricted.
Transfer of data to a third party, test system, or contractor should be done after certain obfuscation.
Maintenance of the system (either remotely or on-site) by application vendors should be performed under supervision and in the presence of the internal staff with complete records of activities.
The IT staff should play a minimal role in normal system operations. Most systems come with their own roles and security mechanisms. The IT staff should be granted only the minimal possible set of permissions for the application. They are the people who have superuser access to the OS and the database, and granting them rights to modify data in the application may lead to situations where system and data integrity can be compromised.
Regular database health-check scripts can prevent catastrophic errors that would result in data loss. DBAs usually configure periodic runs to check the parameters and also configure alerts to prevent situations such as disk space issues.
Alert logs and OS error logs should be periodically analyzed for errors like ORA-3113 and ORA-600. The possibilities of data loss or data inconsistency must be ruled out.
Often, administrators disable constraints and drop triggers before performing bulk data-transfer operations. The system designer should clearly mention policies to deal with such situations in the Administration guide.
IT staff and DBAs must recognize the importance of triggers and constraints in data integrity. As such, operations should not be permitted with invalid triggers and disabled constraints. The health-check scripts should also detect these conditions.
Utilities and reports must be developed to analyze the audit trail and, at times, formulate an automatic response (like an e-mail, a page, or an SMS alert).
Storage of audit trails should also be governed by a policy for longevity and active access.
The reserved Oracle users should have secure passwords, changed from the standard ones. Only the required users and roles must be enabled. (See CIRT.net's default passwords page for more details.)
Backups are usually the safest way to back out of failed or aborted Oracle or application migrations.
A provision for emergency access to the data is a must. Applications may come with 'become superuser' scripts (or executables) that would enable the IT staff to access the application with full rights. All runs of this 'become superuser' script should be logged.
IT staffnot system usersshould always carry out password management.
Password reset requests should not just change the password to a stock known password or a random system-generated password. They should also force the user to change his or her password during the next logon.