JOnline: The Need for and Implementation of Audit Trails 

Download Article

Millions of transactions pour into an organization’s online system every week. All government agencies and their commercial contractors with access to classified intelligence information are legally required to secure their systems to ensure the integrity, availability and confidentiality of data. The need is equally critical at corporate and business organizations. While most of the efforts are made to prevent unauthorized access from outsiders, statistics reveal that insider attacks have caused equally severe damage to organizations’ data and assets.1

Some of the legislation issued in the US to reduce the security risks related to sensitive information include:

  • National Industrial Security Protection Operating Manual (NISPOM), developed by the US Department of Defense (DoD)
  • Federal Information Security Act
  • Department of Defense Instruction 8500.2
  • Director of Central Intelligence Directive 6/3 (DCID 6/3)
  • Committee on National Security Systems (CNSS) Instruction No. 4016, National Information Assurance Training Standard for Risk Analysts

All these directives emphasize keeping user activity logs and audit trails. Based on the needs and legislative requirements, many organizations worldwide have taken initiatives to define best practices and frameworks to plan and implement IT solutions and, ultimately, protect the information assets. For instance, the Control Objectives for Information and related Technology (COBIT) framework, published by the IT Governance Institute (ITGI), specifies in its control objective DS5 the use of a violation and security activity report and security surveillance as key areas to be addressed. It also emphasizes the need to review the design of audit trails while identifying the automated solutions under control objective AI1.10.

Similarly, International Organization for Standardization (ISO) 17799, the international security management standard, also addresses this issue in clause 12.3, System Audit Consideration; 10.2, Security of Application Systems; and 9.7, Monitoring System Access and Use. It also emphasizes response to security incidents and malfunctions in clause 6.3.

The implementation of most of these frameworks calls for the design and implementation of effective logging and the traceability of events taking place in the information system.

The practice of concurrent auditing is not new. Various concurrent auditing tools and techniques, such as Integrated Test Facility (ITF), System Control Audit Review File (SCARF) and Continuous and Intermittent Simulation (CIS), evolved a long time back. However, their implementation was not widespread in their initial years because the tools needed modifications in the application system software. Over time, with the evolution of sophisticated database management systems that are capable of throwing exceptions on the desired events, the implementation of these techniques has become relatively easy.

However, the application of audit trails in most organizations is limited to an evidence provider after the security incident takes place and is reported. Even in organizations where audit trails are analyzed periodically, audit reduction tools have to be applied to search for relevant evidence. The exponential growth of transaction volumes handled by an enterprise, as well as the opening up of organizations’ boundaries, has posed a fresh challenge to auditors to have another look at the judicious application of this powerful tool.

Applications of Audit Trails

NISPOM and other regulations recommend logging and analyzing the following activities:

  • Successful and unsuccessful logons
  • Successful and unsuccessful accesses to security-relevant objects and directories, including creation, open, close, modification and deletion
  • Changes in user authenticators
  • The blocking or blacklisting of a user ID, terminal or access port, and the reason for the action
  • Denial of access resulting from an excessive number of unsuccessful logon attempts
  • Enough information to determine the date and time of action, the system locale, the system entity, the resources involved and the action involved

While the audit trail of user logons provides traces of system access attempts, it is the last category that needs more focus to guard against security incidents, especially those arising from insider attacks. Auditors need to know the data resources accessed and the action done on these resources to forewarn against a possible tampering of data.

The first and foremost question is on which tables of information and which events the audit trail should be applied. Figures 1 and 2 show the possible sequence of events taking place on master and transaction tables in a typical data store.


Figure 1 illustrates that, in the case of master data, an audit log is required only for event number 4. The previous three events’ stamps are in the main record itself. The possible events taking place on a typical transaction table undergoing online posting or batch process are shown in Figure 2.


It is important to keep the size of the audit log to the minimum possible. For this reason, normal events such as insertion and review of records are not recommended for an audit logging. In special cases, where one needs to audit log the read attempts on classified information, one may maintain a separate log for such attempts with the identification of the person and a date/time stamp.

Design of Audit Logs

As explained previously, an audit log is required only in cases where the locked master or transaction data are being modified. Since such modification may take place from any of the client end processors, one should not rely on application-based audit logs.

Trigger-based Audit Logs

Instead, the database trigger-based audit logs are the safest. Normally, good databases like Oracle or SQL Server provide trigger-based programming events. These events may be before or after the following events of a row (or group of rows):

  • Insert
  • Update
  • Delete

The “before” event may be trapped for prevention. For example, an update of masters may be prohibited on weekends or holidays. In some cases, the insertion of a back-dated transaction may be prohibited.

The “after” event can be utilized for an audit log. As explained previously, trapping the insert event is not of interest, as it will unnecessarily inflate the size of the audit log. Nor should the modifications be trapped while the record is under review. However, any updates and deletions after the row has been locked/processed are cause for worry and should be logged.

Horizontal and Vertical Audit Logs

While logging the modifications, there can be two modes:

  • Horizontal logging—Log the entire row image as available before update.
  • Vertical logging—Log only the columns that have changed during the operations.

Both have their advantages and disadvantages.

As evident in "Figure 3, horizontal logging may be convenient for delete logging; however, for modifications, vertical logging has numerous advantages.


Statistical alerts are a special advantage of vertical logging. The person authorized to make a change may misuse his/her powers to set a change temporarily, carry out the fraud and then reset the record. Such events may get noticed only after a detailed analysis of a conventional audit trails. However, in the case of vertical audit trails, one can build alerts on sensitive changes. The abnormal frequency of such changes may alert the audit system for advance actions.

Audit of Audit Logs

The following measures should be taken to secure the audit logs against tampering:

  • The access rights to audit logs should not be with the operations department or the supervisor/administrator. They should be accessible only to the internal/external auditors.
  • The audit log table should have an audit log on itself to record any tampering.

The overall structure of the audit log setup is shown in figure 4.


Conclusion and Future Road Map

The important conclusions that can be drawn from this discussion are:

  • The need for traceability of data changes has increased with the increase in the volume of transactions and the opening up of organizations’ boundaries.
  • The increased volume of transactions has further posed a challenge for the selective logging of possible security events. Therefore, the design of audit trails needs to be seen in conjunction with the overall design of the system and important control points.
  • The audit logging needs to be more innovative in design and substance, so that it is easy for the auditor to analyze. It should give proactive signals and help prevent security incidents rather than throw a heap of information on auditors/security administrators.

Future information systems are likely to face even bigger challenges in terms of sanctity and security of information assets. The audit logs should no longer be offline analysis tools. These logs may feed to a business intelligence (BI) tool/expert system to identify the behavioral patterns, thus helping to prevent a security incident.


1 Skoudis, Ed; “Tips for Dealing With Insider Security Threats,”


CSI/FBI, Computer Crime and Security Survey, 2005

D.K. Agarwal, CIQA
has been a director at Prosix Softron Pvt. Ltd. in India for the past 16 years. He has been instrumental in designing and implementing software solutions across various online applications in various technologies. Most of these solutions were designed around a relational database management system (RDBMS), and were designed for the security and audit requirements of the customers.

Information Systems Control Journal, formerly the IS Audit & Control Journal, is published by the ISACA. Membership in the association, a voluntary organization of persons interested in information systems (IS) auditing, control and security, entitles one to receive an annual subscription to the Information Systems Control Journal.

Opinions expressed in the Information Systems Control Journal represent the views of the authors and advertisers. They may differ from policies and official statements of the Information Systems Audit and Control Association and/or the IT Governance Institute® and their committees, and from opinions endorsed by authors' employers, or the editors of this Journal. Information Systems Control Journal does not attest to the originality of authors' content.

Instructors are permitted to photocopy isolated articles for noncommercial classroom use without fee. For other copying, reprint or republication, permission must be obtained in writing from the association. Where necessary, permission is granted by the copyright owners for those registered with the Copyright Clearance Center (CCC), 27 Congress St., Salem, Mass. 01970, to photocopy articles owned by the Information Systems Audit and Control Association Inc., for a flat fee of US $2.50 per article plus 25¢ per page. Send payment to the CCC stating the ISSN (1526-7407), date, volume, and first and last page number of each article. Copying for other than personal use or internal reference, or of articles or columns not owned by the association without express permission of the association or the copyright owner is expressly prohibited.