Tracking SQL: Tango/04 Keeps Watch for Malicious Queries

The power of SQL is a double-edged sword on the iSeries. Its ease of use and speed make SQL a lifesaver when DB2/400 data must be fixed quickly. But, in the wrong hands, SQL can be used to copy, change, or delete whole swaths of a database, which can be done in relative obscurity. Tango/04 Computing Group recently launched new SQL Monitor software designed to shine some light on SQL’s dark side.

Consider the potential uses of SQL on the iSeries, as related by Raul Cristian Aguirre, chief executive of Tango/04. “A well-known fraud scheme involved people from IT in combination with a truck driver: the IT guy doubled the number of goods to be delivered in certain orders just before shipping them, so the truck was loaded with more product than required. After getting the goods in the truck, the order numbers were reverted back to the original customer purchase, and the truck driver and the IT guy shared the profits of selling the extra products.”

In this scenario, it would be almost impossible to trace how the goods left the warehouse, since the changes to the database were not audited, Aguirre says. SQL could also help someone to gain access to personal information, such as a politician’s medical history or a celebrity’s phone number, which might be governed under new laws, such as the Sarbanes-Oxley Act or HIPAA. Or a disgruntled employee might use SQL to copy a master file, which could be sold to the competition.

On the iSeries, users are provided with interactive SQL access to the database through the Start Interactive SQL (STRSQL) command in OS/400, as well as through PC-based products, such as Microsoft Excel and Access, ODBC file transfer software, and many other reporting products. The STRSQL command is particularly dangerous, Aguirre notes, because it includes a wizard that guides users through the process of writing SQL queries.

So if SQL is so easy to use and available, yet so dangerous, Aguirre asks, why is it used on the iSeries in the first place? “The answer is obvious: it is fast, convenient, and powerful,” he says. “On many occasions companies need to fix data as fast as possible, and there is no time to create a compiled, auditable, secure RPG or COBOL program to fix the wrong tables. Many financial institutions will publicly deny such practices (using SQL for quick-and-dirty fixes), but what happens when they have, for instance, a bug or a failure in the middle of a process? If a portion of the data is in bad shape, they need tools to fix it immediately, so they do not have to shutdown operations.”

The bottom line is that “everybody fixes data with SQL or similar tools, but it is a nightmare for auditors and security officials to control this,” Aguirre says.

FINDING AN SQL SOLUTION

Aguirre says that Tango/04 has received many requests for products like the SQL monitors. There is nothing in OS/400 itself that can track interactive SQL statements. Similarly, today’s network security products for the iSeries aren’t able to track SQL statements in real time.

Tango/04’s new SQL monitors are designed to bring accountability and real-time visibility to SQL. The products, which are components of Tango/04’s VISUAL Security Suite and VISUAL Message Center suites, capture all SQL queries performed on the iSeries database and save the SQL statement data in an audit trail file. This data can be retrieved by the VISUAL Security Suite console to generate audit reports that can be analyzed by IT personnel, security officers, or internal or external auditors, the company says.

Tango/04 has introduced two SQL monitoring products. The Interactive iSeries SQL Monitor Agent keeps an eye on interactive SQL use supplied by STRSQL, while the iSeries SQL Monitor Agent captures all SQL statements, regardless of their source, including batch processes, SQL statements embedded in RPG and COBOL programs, ODBC clients, or any kind of program accessing DB2/400 with SQL.

Both monitors structure and enrich the data they collect with additional information, such as the IP address and the real user who performs a query through ODBC, to generate legible event messages that provide the job name, user name, group profile, and accounting code, along with the corresponding executed SQL statement in its entirety, the company says.

The new monitors also can be used with filters to target specific areas where SQL abuse is suspected. For example, the monitors could be set up to watch SQL statements dealing with specific tables, those entered during non-business hours, or those issued from people who shouldn’t be accessing that area of the database.

In addition to catching malicious SQL statements, or malevolent employees or other users, the new SQL monitors have other uses. For example, when someone makes an honest mistake when writing an SQL statement, the monitors can be used to help revert the changes and understand what caused them, Aguirre says. They can also bolster the case of honest IT personnel who might have received a black mark due to the malicious actions of their not-so-honest coworkers, he says.

The new SQL monitors are the latest enhancements to Tango/04’s VISUAL Security Suite and its flagship VISUAL Message Center. This spring it launched new software that monitors the logs of OS/400 applications (see “Tango/04 Launches New Message Log Monitor”).

The new SQL monitors are available now. Go to www.tango04.com for more information.