SQL Protection: Grief Relief from ProData

May 7, 2013 Dan Burger

More and more people are talking about using SQL. The number of classes at conferences continues to rise and the interest level remains on an upward swing. Along with that comes an interest in monitoring and evaluating SQL environments. ProData Computer Services recognized this need, and the result was a product called SQL Audit, which was announced at the COMMON Annual Meeting and Exposition last month in Austin, Texas.

SQL Audit tracks and monitors modifications made to any IBM i database, whether the modifications are made using ProData’s SQL/Pro or DBU, as well as IBM‘s standard SQL functions. All data inserts, changes, and deletes are tracked through journals. They are displayed and reported for viewing or reporting, which maintains the integrity of the audit files.

Enterprise-level systems in large companies typically journal all the time. Most small to midsize shops don’t journal at all because of the performance drain it causes on the system. However, SQL Audit can be applied in either circumstance.

Because of the system overhead associated with journaling, SQL Audit was designed to activate journaling only during the execution of each SQL statement or, if journaling is already activated, will use system journal files.

When used by shops that are not journaling files, SQL Audit will turn on the journals, execute the statement, and then turn off the journals. By doing so, it keeps the journal small and, therefore, the overhead small.

In environments where journaling is used, it works with the journals to display and extract the data from the receiver journals to build useful files. The journal reveals who made the change, when did it was changed, and what file was changed. Without SQL Audit, the journal provides data in one large field of data that is unintelligible. ProData has taken that data and put it in a file that makes sense. And, by the way, these are temporary files, place in a library, which can be deleted when you’re done with them.

When used in conjunctions with ProData’s SQL/Pro, the authority controls offer disciplined access of all files and tailoring of the functionality within SQL for each user and the tracking and reporting mechanisms necessary for IT audits. This combination offers security based on monitoring appropriate users.

The most likely users of SQL Audit will be ProData’s DBU customers. DBU is a popular utility that allows programmers, administrators, and users to view and access any file in DB2 for i database, without programming. The software is useful for a variety of tasks that require full access to field-level data within a database.

DBU provides the capability to view the data returned by SQL Audit and create reports on the data. Without DBU, users can display and print the data using utilities of their own choosing.

Allen Hartley, owner of ProData and a software engineer, applied technology from another ProData auditing product to the creation of SQL Audit. He gives a tip of the cap to a customer that prompted ProData to look into this.

“This product started with our DBU Audit product, which uses the IBM journaling function, but actually doesn’t write the typical journal receivers,” Hartley says. “We write our own receivers that put a lot of additional information in there. We took that same software and applied it to SQL because people have a fear of too much power with SQL. Some companies forbid their programmers from using SQL because it is so powerful, he adds.

Here’s a example of how SQL Audit might be used in the real world. Say an operator is updating files without narrowing the search enough, and therefore updates 150 records instead of the five that were intended. Without journaling the operator is at a loss to figure out which files were updated incorrectly. A good SQL person would extract the data and look at it before updating, but some are careless, some are under pressure to complete tasks in a hurry. Mistakes get made. That’s why you want to be able to fix them quickly and easily.

SQL Audit is available now. Pricing is a flat $1,295 per server. For $795 more, a customer can run SQL Audit on an unlimited number of LPARs. For more information or a free 30-day trial download, see www.prodatacomputer.com.