Computer Forensics: Pulling the Trigger on Fraud

Prevent potential fraudsters from siphoning corporate funds from your databases by installing "triggers": simple, low-cost, non-intrusive programs that will detect any financial movement -- just like a security camera.

A medium-size wholesaling business had contacted me with concerns that there was something wrong with their accounting data. The sales reports weren't reconciling to their invoice listing reports. Management had assumed that this was being caused by cut-off or timing issues. But a quick review of the electronic data in the accounting system revealed that a user appeared to be deleting the invoice detail from the system. This immediately signaled possible fraudulent activity; if a person was deleting the invoice detail, what was happening to the corresponding cash collection?

Fortunately, we were able to install a "trigger" in the database -- a small program that's activated by any user action -- to collect data like a security camera capturing video footage. We quickly found our fraudster -- a female staff member in one of the client's interstate offices. She was fraudulently deleting invoices and pocketing the money she collected -- almost $75,000 in six months.

*      *      *

Computers have revolutionized the accounting world with the introduction of integrated accounting systems and electronic banking. Unfortunately, fraudsters have capitalized on these innovations by using electronic means to enrich themselves. To apprehend these criminals, or better still, to prevent them in the first place from committing fraud, computers and electronic surveillance are now common tools for fighting fraud.

Trigger programs are one way that your company can ferret out electronic fraudsters -- internal and external -- who infiltrate corporate databases or prevent them from committing the fraud in the first place. You can program a trigger to silently capture data that has been inserted, modified, or deleted from a database without disrupting the business or alerting people to your investigation.

The business in the opening case had numerous offices; each was responsible for its own debtors and cash collection. Management was concerned about the cost of a traditional reactive investigation and, more importantly, what it would do to staff morale if it didn't catch the culprit quickly. (A reactive investigation would have required extensive staff interviews plus forensic imaging and analysis of numerous computers.)

The business was using Great Plains Accounting software as an Enterprise Resource Planning (ERP) tool. (ERPs integrate all data and processes of an organization into a unified system.) Fortunately, this software runs on a Microsoft Structured Query Language database (SQL). (SQL is the most popular computer language for creating, modifying, retrieving, and manipulating data from relational database management systems.) The SQL provides a computer forensics specialist with great flexibility in handling the data contained within. The MS SQL allows triggers to be installed.

So we programmed a trigger to capture this information about transactions being deleted from the invoice data tables:

• The user name that had logged into the network;
• The name of the workstation the user had used to log into the network;
• The user name that had logged into the database;
• The date and time the transaction had been deleted; and
• All the accounting information about the transaction (this was important for the business because they could use this information to restore the transaction detail).

The low-cost and non-intrusive surveillance we designed also included a feature that, as soon a transaction was deleted, would immediately send the above details, via e-mail, to the company's chief financial officer and us.

Thanks to the trigger, we quickly began to capture transactions that were being deleted from the accounting system and identified the staff member. The fraudster had managed to siphon almost $75,000 from the company's invoice system in a period of six months because the company's IT department incorrectly set up her security level and didn't have automated system monitoring.

This is an effective example of how computers can be used to perform non-invasive investigations for criminal activity.

A quick list of basic triggers includes:

• Posting transactions to specific general ledger accounts (this can be a dynamic criteria of accounts only created within 30 days)
• Transactions posted outside business hours, or into old accounting periods
• Amounts that are exceptions subject to Benford's Law

You have great flexibility to deal with the information gathered. You can store the details into another table in typical database fashion. You can write another trigger on that table based on specific requirements such as picking up a user's specific action more than three times in a 24-hour period. 

Alternatively, when a trigger is fired you could perform actions outside of the database such as:

• E-mailing details directly to the investigator and any others you authorize
• Running another program such as an intrusion detection or rootkit scanner
• Automatically locking down system security or preventing further transactions
• Sending a Short Message Service (SMS) alert to the mobile phones of appropriate people

An end user operating the application or one trying to manipulate data via custom programs or other back-door means won't know that the data is being captured or other actions being triggered. Even if the fraudster has a good technical understanding of computers, databases, and triggers, you can encrypt the trigger's content so it's not viewable.

DOWN TO THE SPECIFICS  

Let's create a trigger that will capture specific details when a customer order is deleted from the demonstration database. This trigger will also capture the log-in details of the user performing the action and then store the information in a separate and secure database table for reporting on at a later date.

We'll use Microsoft's SQL Database but all the major commercial database products, such as Oracle and "Open Source" database products like MySQL and PostgreSQL, have trigger functionality.

REQUIREMENTS 

You'll need a computer with the following software installed:

• Microsoft SQL server 2000 with the "Northwind" demonstration database installed
• Microsoft Access "NorthwindCS" database (make sure that you open the .adp version of the database, and not the .mdb -- normally found in "C:\Program Files\Microsoft Office\OFFICExx\SAMPLES\NorthwindCS.adp -- and then go into the correct version of Office).

Also ensure that you have sufficient security rights on the computer you're using. Ask your system administrator for assistance.

CREATING THE DEMONSTRATION DATABASE 

If you've not used the NorthwindCS Access database before, you'll need to open it first so it creates the required database in your SQL server.

[Figure 1 is no longer available. — Ed.] 

Once you've opened the database, it will have automatically created the database and tables inside your SQL server for you.

CREATING THE SECURE AUDIT DATABASE AND TRIGGER 

The first step is to create the database and tables in which the captured data will be stored. (You can access the script by e-mailing me at cmacaulay@ppbvic.com.au.) For your peace of mind, the script isn't encrypted so that you're able to read the source and verify exactly what it's doing.

To install the secure database (called NorthwindCS_Data_Audit), you'll need to run the downloaded script using Microsoft's SQL Server Query Analyzer (MS Query). When opening MS Query from your Start menu you'll be presented with a screen.

[Figure 2 is no longer available. — Ed.]

Before proceeding to execute the script, ensure that a database named "NorthwindCS" appears in the listing of databases in the left side of the screen.

If it does, select File then Open from the menu. Use the Open window to browse to the location that you downloaded the script to, select the script, and click on the Open button. Once the script is loaded it will be displayed in the Query Analyzer window shown in figure 3, below.  

[Figure 3 is no longer available. — Ed.] 

To execute the script, press the F5 key, or click on the green triangle Execute button in the toolbar. The script will create the database, tables, and triggers for this demonstration.

Once executed, this simple process will have enabled automatic logging of data about the user logged on, date/time, and details about stored inside the secure database for you to peruse at your leisure. But just as importantly, the user won't be aware that this audit trial has been captured.

DELETING AN ORDER 

To actually capture some data for you to look at, we need to delete a record from the NorthwindCS application. Open the application again (as you did in "Creating the Demonstration Database" above) and click on the Display Database Window button. This will take you to a window displaying the tables that hold all the data for the program. From here, double-click the Orders table so that the data within it is displayed.

[Figure 4 is no longer available. — Ed.]

Each line, or "record," represents an entire Order within the database. However, because of the way a relational database such as SQL works, you won't be able to delete an Order from this screen until you delete all the line records (such as the order detail) associated with that Order. To display the records so that they can be deleted, click on the + (plus) symbol next to the Order you wish to delete.

[Figure 5 is no longer available. — Ed.] 

Highlight each of the records in the newly expanded detail of the Order and press the Delete key on your keyboard. When prompted, press OK to delete the Order Detail records. Once you've deleted all the details from within an Order, write down the details of the Order, highlight the main Order record and press the Delete key on your keyboard.

EXAMINING THE RESULT 

Now that you've deleted an Order and caused the trigger to record the details of your actions, we can look at the results of our exercise. Go back to MS Query again. (You might need to open it via the Start menu if you closed it earlier.) Create a new query and type in (or copy) the following statement into a new query screen:

USE NORTHWINDCS_DATA_AUDIT
GO
SELECT * FROM NORTHWINDCS_DATA_AUDIT
GO
USE MASTER
GO

Press the F5 key on your keyboard or click on the green Execute button to run the script. MS Query will display the details captured into our secure database that were recorded when the main Order record was deleted.

[Figure 6 is no longer available. — Ed.] 

In this basic scenario, I've chosen not to capture information about each of the Order Detail records. However, it would be quite simple to do so by adding extra functionality to the trigger.

PULLING THE TRIGGER 

Prevent internal or external fraudsters from siphoning company frauds with this simple, low-cost non-intrusive surveillance program.

Craig Macaulay, CA, ACFE Associate Member, is the director of the IT Forensic Division in the Melbourne, Australia, office of PPB.  

The Association of Certified Fraud Examiners assumes sole copyright of any article published on www.Fraud-Magazine.com or ACFE.com. Permission of the publisher is required before an article can be copied or reproduced.