10
Thu, Oct
2 New Articles

Microsoft Computing: ODBC Security

Microsoft
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.

http://www.mcpressonline.com/articles/images/2002/Microsoft-ODBC-2V3--03280500.png

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..

Chris Peters has 32 years of experience with 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 i5/OS and Microsoft Office Integration Handbook, AS/400 TCP/IP Handbook, AS/400 Client/Server Programming with Visual Basic, and Peer Networking on the AS/400. He is also a nationally recognized seminar instructor and a lecturer in the Computer Science department at Eastern Washington University. Chris can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..


MC Press books written by Chris Peters available now on the MC Press Bookstore.

i5/OS and Microsoft Office Integration Handbook i5/OS and Microsoft Office Integration Handbook
Harness the power of Office while exploiting the i5/iSeries database.
List Price $79.95

Now On Sale

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: