March 2006

Automated Data Auditing: The Silver Lining of Regulatory Compliance

Automated data auditing helps organizations expedite the compliance process and simplify the work of internal auditors.

Govind Seshadri
Vice President of IT
HSBC

Ashok Swaminathan
Director of Product Management
Sybase Inc.

Simplicity in data management is a thing of the past. Government and industry regulations — such as the U.S. Sarbanes-Oxley Act of 2002, Japan's HPB 517, Basel II, and the International Organization for Standardization's 27001 standard — have created challenges for internal auditors and data managers that seem insurmountable at times. These regulations require, among other things, the implementation and testing of organizational controls to ensure the accuracy and integrity of financial statements and other corporate data.

To enhance and expedite the compliance process, many organizations are automating their data gathering activities. Although automation simplifies the work of internal auditors, many organizations are reluctant to implement automated processes due to their high costs. However, automated data auditing has a silver lining that can help organizations improve their controls and overall IT infrastructure, as well as fulfill strategic objectives and ensure ongoing compliance with regulatory requirements. Internal auditors who wish to enhance their data reviews should become familiar with the benefits of automated data auditing and how to choose effective solutions that meet their organization's compliance needs.

AUTOMATED DATA AUDITING

Prior to regulatory mandates, many IT departments secured corporate data by restricting access to enterprise information to a few privileged users, such as systems and database administrators. However, due to the increasing amounts of data that organizations must manage and protect daily, role-based access alone does not help ensure the security of confidential information. Furthermore, because employees may use corporate information in malicious ways, tracking the activities of all users is vital for effective compliance. As a result, many companies are incorporating automated data audit processes.

Simply put, automated data auditing is the process of continuously monitoring and reporting database activity, thus creating a permanent record of who did what to which data, when. An automated data audit solution provides invaluable insight into how enterprise information is used, enabling organizations to:

• Ensure the end-to-end integrity of data activities by identifying when modifications are made.
• Detect and analyze intentional and accidental breaches in user and application behavior.
• Monitor and provide alerts on the database activities of privileged users that occur outside the application's controls and security measures.
• Validate policies and controls to protect sensitive data, while monitoring the effectiveness of these polices and controls continually.
• Keep track of changes and updates within the firewall.

Automated data auditing is essential for regulatory compliance, because it provides a continuous and permanent audit trail of data access and changes, while storing this information in a centralized repository that can be archived easily for long-term retention. The information gathered by a data audit solution can help improve an organization's operational performance by identifying data-use patterns leading to increased IT efficiency and the fine-tuning of existing business processes.

EFFECTIVE AUTOMATED DATA AUDITING

Automated data audit solutions provide the most convenient and comprehensive way to track modifications by recording all changes and access activities at the database level. Before purchasing an automated data audit solution, organizations need to determine whether the solution has a centralized audit data repository, impacts the performance of operational systems, produces detailed reports and queries, and has drill-down capability for use during forensic investigations.

Automated data audit solutions must also maintain information integrity to reduce organizational risk and ensure regulatory compliance. To do this, organizations need to implement the following best practices as part of any automated data audit effort:

• Documenting audit logs to monitor operational data activities.
• Segregating the duties of the compliance officer or internal auditor from the personnel managing the data to ensure that audit data is not manipulated by those with privileged access to data sources.
• Separating the audit solution from all operational systems to protect audit data from outages or other misuse, and locating collected audit data on a separate server to preserve the data integrity of the audit repository.
• Auditing all privileged users to track data access and the activities of those with extended data manipulation capabilities.
• Keeping a comprehensive record of who accessed what data, and when. This audit repository is collected and stored as a physically separate database that is accessible for queries and reports.

However, the most important activity an automated data audit solution must do is enable auditors to capture data definition language (DDL) and data manipulation language (DML) changes, as well as Select statements:

• DDL Changes. A database management system's DDL allows the user to create new databases, as well as specify how many attributes the database will have, what lengths or numerical ranges each attribute will consist of, and how much editing the user is allowed to do. Audits of DDL changes should track information on schema changes (i.e., database structure changes, including tables, columns, and their interrelationships); new permissions; successful and failed login attempts; any new data or transaction log devices created; backup executions; and other activity a privileged user conducts on the database.
• DML Changes. DML allows users to insert, update, or query information from a database and contains features that ease report generation, including the ability to perform simple arithmetic, financial, and statistical calculations. Automated audit solutions should capture all DML changes, including the before and after values of typical DML statements such as insert, update, and delete. Automated solutions also should capture, where appropriate, the value of the row before the data change, the value after the data change, the identity of the individual who executed the change, the date and time of the activity, and the user login values.
• Select Statements. These structural query language statements allow users to make database searches that follow user-defined parameters. For example, a Select statement may read: "Find all green cars manufactured between 2002 and 2003." Audit data regarding Select statements should include the type of statement executed and the parameters and values encompassed by the specific Select statement, such as metrics about how many records were selected. When Select statements are executed by the database, they return a result set that matches the criteria. Results are of interest to auditors, because the information contained may be regulated by access limitations.

Once collected, organizations can store audit information in a relational database with a well-defined schema. Typically, automated audit data should be kept online for several months, after which it is stored in flat files or an offline storage facility for long-term archiving. To access audit data, organizations should write reports on top of the audit repository or make the data directly available to auditors depending on their internal control processes.

IMPROVING BUSINESS EFFECTIVENESS

Enterprises performing DDL, DML, and Select statement auditing collect rich and detailed information that can be used to improve overall business effectiveness. As a result, internal auditors can contribute to compliance efforts and impact the organization's IT efficiency through the use of automated data audit solutions that help to:

• Correct user errors easily. One of the biggest challenges facing organizations is fixing user errors after a transaction is made. Most applications don't have a way to undo transactions, unless this capability is built into the application. However, organizations reviewing DML changes will have the before and after values of all changed data, as well as information on who conducted the change. Leveraging this audit data enables the organization to correct the erroneous transaction without modifying the application. This is accomplished by using the information in the audit repository, searching for the erroneous transaction, and undoing the operation. That is, each insert, update, or delete statement can be substituted by an opposing operation, such as delete with insert or insert with delete, to reserve the statement's values.
• Enhance recoverability. DML and DDL audit data can be used to recreate databases to specific points in time. This limits the database's downtime, because audit data contain the information required to reconstitute the database. In addition, DML records can be mined from the audit repository if only data values need to be recreated. Without this audit data, the database administrator would have to load the database(s) and apply the transaction log to the specific point in time, which might have the unintended and undesirable effect of corrupting valid transactions.
• Identify patterns of user access and behavior. Organizations can establish normal access patterns by using login information from the DML audit data, which is particularly important when dealing with highly sensitive corporate information. This information can be used to monitor specific users at a more granular level or to prevent access depending on the user's role and responsibilities. In addition, user behavior data may help auditors identify any access requirements not considered previously, implement best practices to ensure compliance with role-based access, and suggest possible additional efforts.
• Enrich application performance. DML data can be used to analyze the most frequently updated tables to see if database design assumptions are still valid given current use patterns. For example, DML transactions can be used to determine whether the organization is providing fast customer service or if the anticipated problem-tracking application was the most commonly used. DML information also highlights possible performance enhancements or corrective actions, such as creating indexes or placing frequently accessed tables on different devices.
• Improve total ownership costs. With the ability to look at the DML statements performed against a database server, database administrators are able to calculate queries, central processing unit use, and memory management costs. Furthermore, by correlating system information with each query (i.e., disk use and input and output use), database administrators can optimize the database for peak performance, leaving idle cycles available for other systems. Using audit data to manage these costs and maximize system resources provides immediate and long-term database efficiency gains.

All of the benefits listed above not only help improve the speed of compliance efforts, but they far outweigh the initial costs of implementing an automated data audit solution.

LOOKING FORWARD

The demands of compliance have a silver lining: They maximize the value of database audit data to deliver peak operational efficiency. Using an automated data audit solution not only provides rich and detailed audit reports, but helps improve an organization's overall IT efficiency and maximize data security efforts.

There is a real and attainable benefit to adopting rigorous audit controls — enhanced process efficiency. Comprehensive data auditing with a centralized repository delivers DDL, DML, and Select statement information across the enterprise. Information on user access, behavior patterns, and actual data changes provide unique and useful insights into organizationwide data access and management activities.

Collected audit data can be leveraged to correct transaction errors, enhance recoverability, track user access patterns for control and efficiency, improve overall application performance, and lower total ownership costs. Once gathered, this data is extremely useful for organizations that wish to improve their business operations and efficiency, while satisfying regulatory and internal compliance requirements simultaneously.

Govind Seshadri is vice president, database administration at HSBC and has been in the financial services industry for more than 10 years. Currently, he leads new technology projects related to database tools and applications in areas such as fixed income, foreign exchange, and derivatives trading. Prior to HSBC, he worked in the global custody area of Banker's Trust/Deutsche Bank implementing database solutions.