Solved

iSeries ODBC Exit Point

Posted on 2015-02-04
11
365 Views
Last Modified: 2015-02-15
Is it possible to extract the requesting IP address in an ODBC Exit Point?

Currently I am pulling the following:

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


Is the IP address stored anywhere?

We would like to somehow get the IP address of someone attempting to connect to our iSeries via ODBC.

Any help would be much appreciated.

Thanks,
0
Comment
Question by:Matthew Roessner
  • 5
  • 5
11 Comments
 
LVL 34

Assisted Solution

by:Gary Patterson
Gary Patterson earned 500 total points
ID: 40589247
All the exit point parameters are documented in the IBM Knowledge Center.  For example, here is the entry for  QIBM_QZDA_SQL2 format ZDAQ0200, which is used for CONNECT requests.

https://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzaik/exitpointtable1.htm?lang=en

As you can see, the IP address is not one of the supplied parameters.  It is possible to figure out the IP address associated with a given connection, but the coding is relatively complex.  This article may give you some hints:

http://www.itjungle.com/fhg/fhg022311-printer01.html

I haven't used this specific code, but from looking at it, be aware that the technique used in this article could have a significant impact on performance in a high-volume connection environment.

Some thoughts:

If this happens frequently, and you're just trying to track down one or a small number of users, you may be able to just use NETSTAT option 3 to manually inspect ODBC connections to the system.  You can see the source IP address for each connection, so a simple exit program that just notifies you when the user in question connects would be all you need.

If your IBM i is behind a firewall, have your firewall administrator log connections to your IBM i on TCP 8471/9471 - those are the ports used for ODBC (unencrypted/SSL).   That will give you the info you need without any additional burden on you IBM i - again, logs could get large in a high-connection environment.

You could also use a network sniffer to monitor traffic destined for these ports on your IBM i.  If you have managed switches, you set up a PC with WireShark, for example, and create a filter for the traffic you're interested in.  Then you configure the switch the IBM i is attached to to mirror the IBM i port traffic to the port the sniffer is attached to.  This puts some additional load on the switch, of course.

Post back with some details if you can, and I may be able to give more specific advice.

- Gary








- Gary Patterson
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 40589325
Another alternative, and this is a pretty good one, is to use the IP Packet Filter policies functionality of the operating system.  This is a built-in IBM i firewall capability that is capable of logging.

Using IBM i Navigator, navigate to your system, then Network, IP Policies, Packet Rules.

Then you create packet rules that log ODBC connections:

FILTER SET Set4InternalInterface    ACTION = PERMIT    DIRECTION = INBOUND    SRCADDR = *    DSTADDR = MyInternalIPAddress    PROTOCOL = TCP    DSTPORT = 8471   SRCPORT = *    JRN = FULL

FILTER SET Set4InternalInterface    ACTION = PERMIT    DIRECTION = INBOUND    SRCADDR = *    DSTADDR = MyInternalIPAddress    PROTOCOL = TCP    DSTPORT = 9471   SRCPORT = *    JRN = FULL

Once you enable IP filtering enabled, these rules will cause each TCP connection to the specified ports to be logged to journal QIPFILTER (created automatically by the system).  As you can imagine, this can generate a lot of traffic on a busy system, so use it with care - journal receivers can get very large, and journaling a lot of traffic can impact system performance.

Here's the IBM i IP Filtering manual:

http://www-01.ibm.com/support/knowledgecenter/api/content/nl/en-us/ssw_ibm_i_72/rzajb/rzajbpdf.pdf
0
 
LVL 1

Author Comment

by:Matthew Roessner
ID: 40589469
Would it be possible to use the following API's to get the IP address information:

List Network Connections (QtocLstNetCnn)
List Network Connection Data (QtocRtvNetCnnDta)

Unfortunately I haven't had much success in getting these API's to work from a CL. Perhaps I am not compiling things right though...

Any experience with these API's ?
0
 
LVL 1

Author Comment

by:Matthew Roessner
ID: 40589497
I attempted to create the following in a CL - but when I try to run this - I get the following error:

Application error.  TCP84C5 unmonitored by TSTLSTCNN at statement  
  0000004300, instruction X'0000'.                                
 
(Line 4300 is this line for the callprc    'QtocLstNetCnn')                
------

pgm    ( +
       )

/*  CRTBNDCL   PGM(your-lib/TSTLSTCNN)          +
              SRCFILE(your-lib/QCLSRC)         +
              SRCMBR(TSTLSTCNN)           */

   dcl   &qusrspc     *char     20
   dcl   &usrspc      *char     10     value( 'LSTNETCNN' )
   dcl   &usrspclib   *char     10     value( 'QTEMP' )
   dcl   &qcnnlst     *char   1024

   dcl   &NetCnnTyp   *char     10     value( '*TCP' )
   dcl   &LstReqTyp   *char     10     value( '*ALL' )
   dcl   &reserved    *char     12     value( x'000000000000000000000000' )
   dcl   &LclLwrIP    *char      4     value( x'00000000' )
   dcl   &LclUprIP    *char      4     value( x'00000000' )
   dcl   &LclLwrPrt   *char      4     value( x'00000000' )
   dcl   &LclUprPrt   *char      4     value( x'00000000' )
   dcl   &RmtLwrIP    *char      4     value( x'00000000' )
   dcl   &RmtUprIP    *char      4     value( x'00000000' )
   dcl   &RmtLwrPrt   *char      4     value( x'00000000' )
   dcl   &RmtUprPrt   *char      4     value( x'00000000' )


   chgvar            &qusrspc       ( +
                                      &usrspc           *cat  +
                                      &usrspclib              +
                                    )

   chgvar            &qcnnlst       ( +
                                      &NetCnnTyp        *cat  +
                                      &LstReqTyp        *cat  +
                                      &reserved         *cat  +
                                      &LclLwrIP         *cat  +
                                      &LclUprIP         *cat  +
                                      &LclLwrPrt        *cat  +
                                      &LclUprPrt        *cat  +
                                      &RmtLwrIP         *cat  +
                                      &RmtUprIP         *cat  +
                                      &RmtLwrPrt        *cat  +
                                      &RmtUprPrt              +
                                    )

   callprc    'QtocLstNetCnn'       ( +
                                      &qusrspc       +
                                      'NCNN0100'     +
                                      &qcnnlst       +
                                      x'00000040'    +
                                      'NCLQ0100'     +
                                      x'00000000'    +
                                    )

dmpclpgm

  end:
      endpgm
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 40589697
I supplied you with a link to a nice working ILE RPG program that uses these APIs above - the IT Jungle article - just to save you this kind of effort.

Yes, I've used them - but I just don't call complex APIs from CL.  I always use RPG or C, for a variety of reasons:

1) Documentation, IBM supplied examples, and online code almost always uses RPG or C for all but the least complex API calls.
2) IBM supplies useful API header files with common structures declared for you in RPG and C, but not CL.
3) CL doesn't support all the data types needed, so you have to fake it.

Your CL looks close.  Two notes:

1) Pass parms shown as (*) in documentation *BYREF, and parms shown with explicit sizes *BYVAL.
2) I don't see where you are creating the user space you reference.  It must exist before you can use it.

- Gary
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:Matthew Roessner
ID: 40591691
Gary:

Thanks for the information. Unfortunately, I don't know RPG (I know...I know) so I am trying to get things working via CL...

I wasn't creating the user space - so that was obviously my problem. Thanks for the point in the right direction.

I have everything written to a user space now... Just not real sure what to do with the information to glean the IP address of the calling program.

If you have any more direction - that would be great. Otherwise I will keep playing with it.

Thanks again for all your help

Matt
0
 
LVL 34

Accepted Solution

by:
Gary Patterson earned 500 total points
ID: 40592043
Now you have to process the data returned to the user space - use QUSPTRUS to get a pointer to the user space, and then iterate through it until you've processed all the data returned.

IPV4 data is populated in the user space in the NCNN0100 format documented here:

http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/apis/qtoclstnetcnn.htm

You'll get one entry in the user space for each connection that you selected on your call to the API.  You're interested in the Remote Address - the first 15 bytes of each entry.  So, for example if you received information about 4 connections, you'd get 400 bytes returned, and the IP addresses would appear at offset 0-14, 100-114, 200-214, and 300-314.

Even if you don't know RPG, you can probably follow the program well enough to use as a general guide to help you create a CL that works.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 40600108
The CL above looks essentially exactly like one of a series of test functions I wrote almost ten years ago for connections, interfaces, routes, ARP table entries, etc.. It's incomplete because all of them were intended to provide quick test details for other programming. The non-existence of the *USRSPC and the DMPCLPGM are from the "test" nature of the functions.

But access to the remote IP address is fairly easy in the various exit points such as QIBM_QZDA_INIT that don't provide it in the exit parameters. Nearly all such exit points invoke exit programs after the server job has established the socket connection. As such, CL could access IP address by using code something like this:


   dcl   &ipAddr      *char     15
   dcl   &lIP         *int

   dcl   &rcvVar      *char    322     value( ' ' )
   dcl   &szRcvVar    *int             value( 322 )
   dcl   &x00         *char      1     value( x'00' )


/* --------------------------------------------------------------------- */

   call  QUSRJOBI      ( +
                         &rcvVar             +
                         &szRcvVar           +
                         'JOBI0600'          +
                         '*'                 +
                         '                '  +
                       )

   chgvar       %sst( &ipAddr 1 15 ) ( %sst( &rcvVar    308  15 ) *cat &x00 )

   callprc  'triml'  ( +
                       &ipAddr      +
                       ( ' '   *byval ) +
                     ) +
               rtnval( &lIP )

   if ( &lIP *ne 0  *and  +
        %sst( &ipAddr 1 15 )  *ne ' ' )  do
      sndmsg   ( 'IP:' *bcat &ipAddr )  tousr( myMsgQ )
   enddo
   else  do
      sndmsg   ( 'No IP' )  tousr( myMsgQ )
   enddo

Open in new window

In that code, any IP address is simply extracted and sent as an impromptu message to message queue 'MYMSGQ'. You'd want to do something a little more involved, I assume. But that's just a matter of more programming. The example code can be inserted into almost any related ILE CL.

It might look a little strange; but it, too, was pulled from some other test function. (I do a lot of basic "prototyping" in CL just because it can be done quickly.)

Tom
0
 
LVL 1

Assisted Solution

by:Matthew Roessner
Matthew Roessner earned 0 total points
ID: 40600606
I actually went about pulling the IP address from the job information.  Here is a full copy of the Exit Point that I went with. This is working, as is. It checks to see if a user is a member of a group. If they are - it grants or denies access to ODBC.  The result is then logged in an IFS file which is date stamped.

----------------------------------------------
PGM        PARM(&STATUS &REQUEST)                              
                                                               
DCL        VAR(&STATUS) TYPE(*CHAR) LEN(1)                      
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 */                                  
DCL        VAR(&HOUR) TYPE(*CHAR) LEN(6)                        
DCL        VAR(&MIN) TYPE(*CHAR) LEN(6)                        
DCL        VAR(&SEC) TYPE(*CHAR) LEN(6)                        
DCL        VAR(&DAY) TYPE(*CHAR) LEN(6)                        
DCL        VAR(&MON) TYPE(*CHAR) LEN(6)                    
DCL        VAR(&YEAR) TYPE(*CHAR) LEN(6)                  
DCL        VAR(&CTIME) TYPE(*CHAR) LEN(20) /*CHAR TIME*/  
DCL        VAR(&CDATE) TYPE(*CHAR) LEN(20) /*CHAR DATE*/  
DCL        VAR(&DDATE) TYPE(*CHAR) LEN(20) /*CHAR DATE*/  
DCL        VAR(&ATIME) TYPE(*CHAR) LEN(8)                  
DCL        VAR(&ADATE) TYPE(*CHAR) LEN(6)                  
DCL        VAR(&USER) TYPE(*CHAR) LEN(10)                  
DCL        VAR(&PRIGRP) TYPE(*CHAR) LEN(10)                
DCL        VAR(&SUPGRP) TYPE(*CHAR) LEN(150)              
DCL        VAR(&TARGET) TYPE(*CHAR) LEN(100)                
DCL        VAR(&CMD) TYPE(*CHAR) LEN(200)                  
DCL        VAR(&RECORD) TYPE(*CHAR) LEN(100)                
DCL        VAR(&JOBNAM) TYPE(*CHAR) LEN(10)                
DCL        VAR(&JOBUSR) TYPE(*CHAR) LEN(10)                
DCL        VAR(&JOBNUM) TYPE(*CHAR) LEN(6)                  
DCL        VAR(&IPV4ADDR) TYPE(*CHAR) LEN(15) VALUE(' ')    
DCL        VAR(&JOBI) TYPE(*CHAR) LEN(700) VALUE(' ')      
DCL        VAR(&JOBILEN) TYPE(*DEC) LEN(4 0) VALUE(700)    
DCL        VAR(&FMTNAME) TYPE(*CHAR) LEN(8) VALUE(JOBI0600)
DCL        VAR(&QUALJN) TYPE(*CHAR) LEN(26)            
DCL        VAR(&INJOB) TYPE(*CHAR) LEN(16) +          
             VALUE('                ')                
DCL        VAR(&TEMP) TYPE(*CHAR) LEN(15)              
DCL        VAR(&NULL) TYPE(*CHAR) LEN(1) VALUE(X'00')  
DCL        VAR(&LEN) TYPE(*INT) LEN(2) VALUE(15)      
                                                       
/* Group to Search for */                              
DCL        &GROUP *CHAR 10 value('GROUPNAME')          
                                                               
RTVSYSVAL  SYSVAL(QTIME) RTNVAR(&ATIME)                        
RTVSYSVAL  SYSVAL(QDATE) RTNVAR(&ADATE)                        
RTVJOBA    JOB(&JOBNAM) USER(&JOBUSR) NBR(&JOBNUM)              
                                                               
/* CONVERT TIME            */                                  
CHGVAR     VAR(&HOUR)  VALUE(%SST(&ATIME 1 2))                  
CHGVAR     VAR(&MIN)   VALUE(%SST(&ATIME 3 2))                  
CHGVAR     VAR(&SEC)   VALUE(%SST(&ATIME 5 2))                  
CHGVAR     VAR(&CTIME) VALUE(&HOUR *TCAT ':' *TCAT &MIN *TCAT +
             ':' *TCAT &SEC)                                    
                                                                           
            /* CONVERT DATE            */                                  
            CHGVAR     VAR(&MON)  VALUE(%SST(&ADATE 1 2))                  
            CHGVAR     VAR(&DAY)   VALUE(%SST(&ADATE 3 2))                
            CHGVAR     VAR(&YEAR)  VALUE(%SST(&ADATE 5 2))                
            CHGVAR     VAR(&CDATE) VALUE(&MON *TCAT '/' *TCAT &DAY *TCAT +
                         '/' *TCAT &YEAR)                                  
                                                                           
  /* Get IP address Information */                                        

           CHGVAR     VAR(&QUALJN) VALUE(&JOBNAM *CAT &JOBUSR *CAT +    
             &JOBNUM)                                        
                                                             
/* Call QUSRJOBI to retrieve job information */              
CALL       PGM(QSYS/QUSRJOBI) PARM(&JOBI &JOBILEN &FMTNAME +  
             &QUALJN &INJOB)                                  
                                                             
MONMSG     MSGID(CPF3C53) EXEC(DO)                            
                                                             
SNDPGMMSG  MSG('Job ' *CAT &JOBNUM *TCAT '/' *TCAT +          

ENDDO                                                      
                                                           
MONMSG     MSGID(CPF3C57) EXEC(DO)                        
SNDPGMMSG  MSGID(CPF3C57) MSGF(QCPFMSG) MSGTYPE(*ESCAPE)  
                                                           
ENDDO                                                      
MONMSG     MSGID(CPF3C58) EXEC(DO)                        
SNDPGMMSG  MSGID(CPF3C58) MSGF(QCPFMSG) MSGTYPE(*ESCAPE)  
ENDDO                                                            
                                                                 
/* Extract data from the output of QUSRJOBI */                  
CHGVAR     VAR(&IPV4ADDR) VALUE(%SST(&JOBI 308 15))              
                                                                 
/* Strip trailing nulls from the address so it looks better */  
CHGVAR     VAR(&TEMP) VALUE(&IPV4ADDR)                          
                                                                 
STRIPNULLS: IF COND(%SST(&TEMP &LEN 1) *EQ &NULL) THEN(DO)                
               CHGVAR     VAR(&LEN) VALUE(&LEN - 1)                      
               CHGVAR     VAR(&TEMP) VALUE(%SST(&TEMP 1 &LEN) *CAT ' ')  
               GOTO       CMDLBL(STRIPNULLS)                              
            ENDDO                                                        
                                                                         
            CHGVAR     VAR(&IPV4ADDR) VALUE(&TEMP)                        
/* End IP Address Information */

CHGVAR     VAR(&TARGET) VALUE('/admin/odbc_' *CAT &ADATE *CAT '.txt')
                                                                     
   /* 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))          
                                                                     
    /* Get Group Information for the User  */                        
         RTVUSRPRF  USRPRF(&USER) GRPPRF(&PRIGRP)  SUPGRPPRF(&SUPGRP)  
                                                                       
 /* Check to see if the user is a member of the group */              
                                                                       
         IF         COND((&PRIGRP *EQ &GROUP) *OR (%SCAN(&GROUP +      
                      &SUPGRP) *NE 0)) THEN(DO)                        
                                                                       
         CHGVAR     VAR(&RECORD) VALUE(&CDATE *BCAT ' ' *BCAT +        
                      &CTIME *BCAT &USER *BCAT 'has accessed +        
                      the AS400 via ODBC. (' *CAT +                    
               %TRIMR(&IPV4ADDR) *CAT ')')                      
                                                                 
      /* STATUS FIELD OF 1 WILL LET USER CONTINUE, CHANGE TO */  
      /* 0 TO REJECT ACCESS TO AS400                         */  
     CHGVAR     VAR(&STATUS) VALUE('1')                          
                                                                 
  ENDDO                                                          
  ELSE       DO                                                  
                                                                 
  CHGVAR     VAR(&RECORD) VALUE('***' *BCAT &CDATE *BCAT +      
              ' ' *BCAT &CTIME *BCAT &USER *BCAT 'WAS +        
              DENIED ACCESS TO THE AS400 VIA ODBC. (' +        
              *CAT %TRIMR(&IPV4ADDR) *CAT ') ***')              
                                                               
     /* STATUS FIELD OF 1 WILL LET USER CONTINUE, CHANGE TO */  
     /* 0 TO REJECT ACCESS TO AS400                         */  
    CHGVAR     VAR(&STATUS) VALUE('0')                          
                                                               
 ENDDO                                                          

/* SEND MESSAGE TO ME WITH WHO HAS ACCESSED AS400 VIA ODBC */          
                                                                       
            CHGVAR     VAR(&CMD) VALUE('echo "' *TCAT &RECORD *TCAT +  
                         '" >> ' *CAT &TARGET)                          
                                                                       
            QSH        CMD(&CMD)                                        
                                                                       
            ENDPGM                                                      

-----------------------------------------------

Thanks for all the help!
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 40600728
Thanks for sharing your final solution.
0
 
LVL 1

Author Closing Comment

by:Matthew Roessner
ID: 40610620
Thanks for your help Gary
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A Short Story about the Best File Recovery Software – Acronis True Image 2017
Is your company's data protection keeping pace with virtualization? Here are 7 dynamic ways to adapt to rapid breakthroughs in technology.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now