By definition, a technology like ODBC exists solely to provide access to data. As such, the issue of unauthorized data access will always exist.

ODBC provides an easy method to access iSeries data. It's true that ODBC's currently used security methods can present loopholes that allow users to update data when you might not want them to, but these loopholes can be closed.

You need to consider two issues when it comes to ODBC security:

• The necessity to ensure that authorized users use appropriate methods to modify data on the iSeries
• The capability to retrieve passwords using ODBC's trace functionality

iSeries ID Authority

ODBC can be configured to allow the update of iSeries data from a PC. This allows programs written to communicate through ODBC to modify iSeries data (which, in many cases, is desirable). For instance, if you have a client/server order-entry program that needs to save the orders on your iSeries, it will require read/write access to your iSeries order file. To make this program work, you need a DSN with update capability. One component of the DSN is a user ID with sufficient iSeries authority to update the data files.

A DSN with update capability shouldn't cause problems if your users use the intended program. But ODBC DSNs are not tied to a specific program. Because ODBC DSNs work with any ODBC-enabled PC program (such as Microsoft Access), users could employ the DSN to get to your iSeries data and have full authority to any files designated by the iSeries profile used. With Access allowing direct updates to your data, this could spell disaster.

On the iSeries, users can adopt the authority of a program they are running. A typical method of securing access to the database is to give users either no access or read-only access to data files and then give programs read/write capabilities.

When users run an iSeries program such as an order-entry program, they may adopt the authority of the program's owner. Adopted authority allows updates to the data as long as the user does so through the program. The program can contain all the business logic and validations required to keep your data as clean as possible. If a user tries to modify the file when not running the program (e.g., through a file utility), access is denied. This is a very effective way to ensure against inappropriate data updates.

You have to be careful when granting users access to data. They will have the authorities granted by the logon used through the ODBC driver. This can be an acceptable method to grant users read-only access to the data they want, and they will not be able to update the data.

If your users are allowed to modify data through a client/server program, they will need the capability to update files and, if you grant that authority through ODBC, your users can use any application on the PC to update the data.

So what can you do?

Stored Procedures with Adopted Authority

If your users need to update certain data on the iSeries through client/server programs, you can perform the updates through stored procedures. Stored procedures are iSeries programs that can be executed from ODBC and other places. Stored procedures can use iSeries adopted authority.

To secure your data and still allow updates, grant users the minimum authority necessary (read-only or no access) and then use adopted authority with stored procedures to facilitate client/server updates. In this way, all updates can go through the business logic present in the stored procedure, which helps ensure the integrity of your data. Other attempts to access iSeries data will be denied under the user's restricted profile.

Exit Programs

Exit programs are user-written processes designed for securing iSeries Access in several ways, and they're an effective method with ODBC. Exit programs are called from hooks built into the server programs supplied with iSeries/i5.

Each function of iSeries Access calls the appropriate server programs on the iSeries. For example, there are server programs for processing logons, database requests, and file-transfer requests. These server programs on the iSeries can call exit programs defined in what are called "exit points."

Theoretically, you could interpret each SQL statement sent by client programs to determine whether the actions are permissible. However, this method has several problems. One problem is that taking apart each SQL statement as it is executed requires considerable overhead and will slow performance noticeably. Also, the program required to interpret the SQL statements correctly would be quite complex and difficult to program. Further, there are no guarantees that third-party ODBC drivers call programs registered in the IBM exit points.

In fact, comments from IBM indicate that no third-party ODBC drivers honor the IBM exit-program methodology. This means that your data is secure only when people are using the iSeries Access ODBC drivers, and that isn't an acceptable solution. Therefore, while exit programs can be beneficial to gain another level of control over your security, you shouldn't use exit programs as a primary means of securing client/server access to the iSeries.

Password "Sniffing" Using Trace

ODBC provides a means for tracing the conversation taking place between the driver and the host database. Used by developers for testing purposes, the tracing feature is designed to help programmers find out exactly what is going on and to help fix problems. However, tracing (also called "sniffing") can be used by nefarious bad guys to retrieve user passwords.

When tracing is enabled, communications with the host are written to a file. This includes the user ID and password, which are captured in plain text. Turning on tracing can be as simple as checking an option under the ODBC administrator, as shown in Figure 1.

Figure 1: You can easily turn on ODBC tracing.
 

How big of a problem is the misuse of tracing? According to Microsoft, it's not too big of a deal. After all, turning on tracing drastically slows down the application. Therefore, users would notice and then call someone from IT to diagnose their troubles. Hardly a secure solution!

One option is to delete the DLL (ODBCTRAC.DLL) that provides the tracing functionality. This DLL resides in the Windows directory. This solution is limited, however, because any installations of new applications that use ODBC are likely to install that file again.

The only viable solution that works in all environments is for the application programmer to explicitly turn off ODBC tracing when logging on. This is done using ODBC programming calls. Once the logon is complete, tracing can be turned back on using those programming calls. In the real world, the problem with this method is that you cannot guarantee that third-party applications will do this.

Until turning off ODBC tracing becomes a standard practice or Microsoft somehow addresses it in a different manner, ODBC will continue to have holes in this area. Keep in mind, however, that a relatively specific set of circumstances has to be in place for a security violation to occur. The misuse of tracing is not something that is likely to be exploited by just anybody. The user would have to know exactly what to do and would require physical access to the machine on which he or she wanted to run the trace.

ISeries Journaling

Another tool available to the security-minded administrator is iSeries file journaling. Journaling is an internal iSeries mechanism in which before and after images of data records may be written automatically whenever a file is changed. While journaling does not prevent an unintended file update, it does provide a means of identifying and correcting the transgression.

Native iSeries Security

The bottom line is that all aspects of iSeries Access, including ODBC, by definition honor the iSeries security model. This means that if your users have access to objects through their logons, they most likely will be able to access them through iSeries Access. Conversely, if they don't have access to those objects through their iSeries logons, they won't be able to access the objects within iSeries Access. Therefore, the recommended method of securing your system is to use iSeries object-level authority.

ODBC is a powerful means of accessing your iSeries data. It is widely supported in Windows applications that use external data. It allows front-end applications such as Access and Query to access data, regardless of where it is stored. The iSeries Access ODBC driver is a capable driver that allows you to retrieve data from the iSeries for use in Office and other applications, but all that capability naturally will be accompanied by data security issues.

Chris Peters has 26 years of experience in the IBM midrange and PC platforms. Chris is president of Evergreen Interactive Systems, a software development firm and creators of the iSeries Report Downloader. Chris is the author of The OS/400 and Microsoft Office 2000 Integration Handbook, The AS/400 TCP/IP Handbook, AS/400 Client/Server Programming with Visual Basic, and Peer Networking on the AS/400 (MC Press). He is also a nationally recognized seminar instructor. Chris can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..