Harnessing Your ODBC Users with Exit Programs

Published: November 29, 2006

by Bob Butcher

I check my daily Web logs to see what intranet Web applications are being used. The log gets updated on the AS/400 and provides a great tool for our auditors. I came in one morning and the log was empty. I thought that was funny because we normally get 175 to 200 entries per day. I checked the log throughout the day on that particular day and there were many entries of usage. The next morning, it was empty again. This was happening every morning when I came in. Because we do a lot of client server applications using ODBC, I wondered if there was a way to track what was happening each morning around 7 a.m.

That is when I discovered exit programs on the AS/400 (which obviously also exist on the iSeries and the System i5, which both run i5/OS and OS/400, but we have an AS/400 where I work, so I call it that). An exit program can provide an extra layer of security, in addition to OS/400 security, that can help control user access to applications. When a user requests to use a particular server application, it exits first to the exit program, to see if they are authorized to proceed, or if they are rejected from continuing on to access data. The exit program that I used on the AS/400 to work with ODBC was QIBM_QZDA_INIT.

All ODBC traffic works it way through the QIBM_QZDA_INIT exit program. I decided to create my own exit program to monitor all of the traffic to try and find the person who might be affecting my Web logs. The program I created below extracts the profile name of the user who is trying to access the AS/400, and sends a message to my user profile. The message will have the time and date that it was sent. Below is the snippet of code on how this works:

PGM        PARM(&STATUS &REQUEST)  
                                                         
 DCL        VAR(&STATUS) TYPE(*CHAR) LEN(1) /* ACCEPT OR 
              REJECT THE USER TRYING TO ACCESS AS400 */ 
 
 DCL        VAR(&REQUEST) TYPE(*CHAR) LEN(34) /* + 
              PARAMETERS STRUCTURE */                    
                                                         
                      /* PARAMETERS DECLARES */  
                              
 DCL        VAR(&USER) TYPE(*CHAR) LEN(10) /* USER +     
              CALLING SERVER VIA ODBC */ 
                
 DCL        VAR(&SRVID) TYPE(*CHAR) LEN(10) /* DATABASE +
              SERVER VALUE - *SQL  */ 
                   
 DCL        VAR(&FORMAT) TYPE(*CHAR) LEN(8) /* FORMAT +  
              NAME - ZDAI0100 */ 
                        
 DCL        VAR(&FUNC) TYPE(*CHAR) LEN(4) /* FUNCTION +  
              BEING PERFORMED - 0 */                     
                                                         
    /* EXTRACT PARAMETERS FROM THE &REQUEST FIELD */    
    
 CHGVAR     VAR(&USER) VALUE(%SST(&REQUEST 1 10))  
 CHGVAR     VAR(&SRVID) VALUE(%SST(&REQUEST 11 10)) 
 CHGVAR     VAR(&FORMAT) VALUE(%SST(&REQUEST 21 8)) 
 CHGVAR     VAR(&FUNC) VALUE(%SST(&REQUEST 28 4))  
                                                           
 /* STATUS FIELD OF 1 WILL LET USER CONTINUE, CHANGE TO */ 
 /* 0 TO REJECT ACCESS TO AS400                         */  
     
 CHGVAR     VAR(&STATUS) VALUE('1')                    
                                     
 /* SEND MESSAGE TO ME WITH WHO HAS ACCESSED AS400 VIA ODBC */  
 
 SNDMSG     MSG(&USER *CAT ' has accessed the AS400 +  
              through ODBC.') TOUSR(BOB)                   
                                                           
 ENDPGM     

A couple of key points to make are that the parameters &STATUS and &REQUEST are required and need to be defined the way that they are in the declaration statements. More information regarding detail information about this exit point and the necessary parameters and exit program format details can be found on the IBM Web site. Note the value of the &STATUS variable. If this value is 1, the user can continue to the AS/400. If the &STATUS variable is 0, the user has been rejected and cannot continue to the AS/400. The &USER field is extracted from the &REQUEST field and this is the user profile of who is trying to get at the AS/400.

To get this program up and running on my system, I had to follow the following steps:

1. Compile the program
2. Type WRKREGINF (work with registration information) at a command line
3. Select option 8 to work with exit programs
4. Select option 1 to add the program that was just compiled

This exit program solved my problem. I had a co-worker from IT, who came in every morning, fired off a VB program at 7 a.m. and accidentally cleared the wrong file member. I sifted through the messages that were being sent to me in the morning, looked at the user profiles, and determined who to ask what they were running in the morning. Without this exit program in place, it would have been difficult to figure this out.

This sample is a very simple one. You could add more complexity to your program to accept/reject users. Not only do exit programs work for ODBC, but they work for other applications that request access to AS/400 information, such as FTP, DDM, and TELNET. Writing your own exit programs can get to be very complex and there are several great exit point software packages out there today. These companies have done all of the program creation for you and have the auditing tools built in their software already. This sample of code above should at least get your feet wet in understanding a little bit about exit programs.

Bob Butcher is a software consultant with over 19 years experience in the AS/400 world. He specializes in client/server applications that deal with the AS/400 and Active Server Pages or Visual Basic. He can be reached at bbutcher@samicsoft.com.