Secure DB2 for i Database Server Access by IP Address
February 23, 2011 Hey, Mike
|
Note: The code accompanying this article is available for download here. I am trying to secure external access to our iSeries DB (ODBC, JDBC, et al). From a security point of view, I want to allow database access if it comes from specific IPs or servers. But I cannot find a way to identify the source (i.e., remote IP address) from the QIBM_QZDA_SQL2 exit point. I only find the user ID. Is there a way to obtain the source of the remote client attempting to connect to DB2? –Mike, Four Hundred Guru Reader Hi, Mike: You are correct. The i/OS database server exit points only supply the user ID to your exit program via an *ENTRY parameter. So in your scenario, it is not possible to determine if a connection should be accepted or rejected based on the information that i/OS gives to your exit program. However, there are other APIs that the exit program can use to determine the IP address of the caller and still make the decision as to whether the requested connection should be allowed. The required APIs for this task are:
These APIs can be pretty ugly to work with. Fortunately, it didn’t take me long to stumble on Carsten Flensburg’s sample program “Print TCP/IP Connection Status,” which can be found here. This program prints a list of all active TCP/IP connections on the IBM i using the List Network Connections API (QtocLstNetCnn). Additionally the List Network Connection Data API (QtocRtvNetCnnDta) is used to show what jobs, if any, are using a particular connection. This program produces a printout similar to the screen output shown by the NETSTAT command. Thanks to this program already doing the “heavy lifting” to use these APIs, I was able to easily incorporate this code into an RPG exit program called DBSRVEXTR. What’s an Exit Program? For those unfamiliar with i/OS exit programs, they’re basically “hook” programs that developers write to perform logic for a given event (also called an exit point). You can see a list of available events or “exit points” in IBM i/OS by issuing the Work with Registration Info (WRKREGINF) command. The exits are generally used for security purposes. For example, a developer can write an exit program to determine whether or not a particular user can connect to the FTP server, can make a database request, can make a particular SQL statement request, etc. A simple status parameter is passed from the exit program back to i/OS to indicate whether or not the request should be honored or denied. QIBM_QZDA_SQL2 is an exit point that is designed to let a user-defined exit program examine a SQL statement (submitted via JDBC, ODBC, OLE DB, .NET Managed Provider, etc., to a database server) and decide whether or not it should be allowed to run. While it is possible to accomplish this source IP address validation task using the QIBM_QZDA_SQL2 exit point, a better choice would be to use the QIBM_QZDA_INIT exit point. The reason is that the QIBM_QZDA_INIT exit is only run once when a client first establishes a connection to the host database server. In contrast, the QIBM_QZDA_SQL2 exit is executed whenever a SQL statement is submitted. You only need to check the client’s IP address when the connection is first requested, not every time a SQL request is submitted. (For more info on QIBM_QZDA_SQL2, see Get Creative Using the SQL Database Exit Point.) RPG Exit Program Sample DBSRVEXTR The parameter list coming in to the program is documented here on IBM’s Web site. There are two parameters:
The first thing the sample exit program does is call the QtocLstNetCnn API to retrieve the current active TCP/IP connections. This list will include the remote IP addresses of all the clients. Due to the time it takes to gather all of the connections, QtocLstNetCnn will retrieve only the subset of connections that are using local port 8471, the default port for the i/OS database servers. Once the program has a list of active TCP/IP connections that are communicating to a database server, it needs to find out which connection is the one associated with the database server job (QZDASOINIT) that is running the exit program. For each active network connection to port 8471 (collected in the prior step), the QtocRtvNetCnnDta API is called to get the associated i/OS job information. So the exit program will examine the job information for each connection sequentially until it comes across its own job. This is where things can get slow, depending on the number of active connections to the database servers. Once a match is found, the program will know which connection initiated the call to it and can then interrogate its IP address. If the IP address matches a known address, the “status” parameter is set to *ON (i.e. ‘1’) to let i/OS know that the remote user can continue. However, if the IP address is unrecognized, the status parameter will remain at *OFF (i.e. ‘0’), and the host server will reject the requested connection to the database server. In the sample code, I allow remote addresses beginning with 172 or 127 (local) to continue while all others are rejected. Registering the Exit Program Once the exit program is written, it needs to be registered with an exit point. This is easy to do using the Add Exit Program (ADDEXITPGM) command. Below is a sample that will instruct i/OS to run program DBSRVEXTR whenever a user attempts to connect to a database server.
ADDEXITPGM EXITPNT(QIBM_QZDA_INIT)
FORMAT(ZDAI0100)
PGMNBR(*LOW)
PGM(MYLIB/DBSRVEXTR)
TEXT('Database Server Init User Exit')
Be careful when doing this! Remember, this program will be invoked whenever a user attempts to connect to DB2 for i remotely using JDBC, ODBC, etc. Make sure your code is tested because buggy code can cause problems for everyone attempting to access the database remotely. You can remove the exit program with the Remove Exit Program (RMVEXITPGM) command. Adding and removing exit programs for a database server exit point may not take effect immediately for existing jobs. A Few Things to Note about the Program This sample program was written to check for IPv4 addresses, although the APIs are capable of providing IPv6 functionality. Further, if you want to create restrictions based on the machine name of your servers (instead of IP addresses), you can use the “gethostbyname” API to retrieve the host name for a given address. See Scott Klement’s great tutorial on host names and addresses using RPG. When the DBSRVEXTR program rejects a request, i/OS throws a PWS9801 error, which the client will receive. This solution doesn’t necessarily protect all possible client/server database interfaces. For example, the Microsoft DB2 OLEDB provider connects to DB2 for i using the DRDA server jobs instead of the “normal” database server jobs that IBM’s OLE DB providers (IBMDA400, IBMDASQL) use. Taking advantage of IBM i exit points is a great way to take some security concerns by the reigns and bring them under control. Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page. RELATED STORY Get Creative Using the SQL Database Exit Point
|
