Restricting database connections using the CONNECT_PROC database configuration parameter in DB2 for Linux, Unix, and Windows

A technique for protecting data access

Comments

Problem description

The system administrator has a requirement to allow end users to connect to the database only from a specific set of hosts or IP addresses. This article shows how the CONNECT_PROC database configuration parameter and global variables CLIENT_HOST or CLIENT_IPADDR can be used to address this requirement. For illustration purposes, in our example, we will assume that the system administrator would like to ensure that all users can connect to the database only from host hote53 or IP 9.26.120.69.

CONNECT_PROC database configuration parameter

This database configuration parameter allows you to input a two-part connect procedure name that will implicitly be executed every time an application connects to the database. The procedure must not take any parameters. The update statement to set the connect procedure name takes immediate effect.

This database configuration parameter is available starting with DB2 V 9.7.3.

CLIENT_HOST global variable

This built-in global variable is system maintained and contains the host name of the current client, as returned by the operating system.

f the client connection originated from an application running on the local system, the value of the variable is NULL. DB2 obtains the client IP address from the network when the connection is accepted. The client host name is obtained from the client IP address by invoking the TCP/IP GetAddrInfo function. If the processes did not originate from a remote system using TCP/IP, the value of the variable is NULL.

This global variable is available starting with DB2 V10.1.

CLIENT_IPADDR global variable

This built-in global variable contains the IP address of the current client, as returned by the operating system. The value of the CLIENT_IPADDR global variable is NULL if the client did not connect by using the TCP/IP or SSL protocol. This global variable is available starting with DB2 V10.1

Example scenario

As an example, let's say you want to allow to connections to the TESTDB database on 9.26.120.69 (hotel53) only from IP 9.26.120.69 (hotel53). You would follow the steps below.

Step 1: Create the database

  1. Create the TESTDB database on 9.26.120.69 (hotel53) using the following commands:
    Listing 1. Create database
    db2start
    SQL1063N  DB2START processing was successful.
    
    db2 create database TESTDB
    DB20000I  The CREATE DATABASE command completed successfully.
  2. Verify that the database has been created:
    Listing 2. List database directory to see if the database was created
    db2 list db directory
    
     System Database Directory
    
     Number of entries in the directory = 1
    
    Database 1 entry:
    
     Database alias                       = TESTDB
     Database name                        = TESTDB
     Local database directory             = /home/hotel53/miacob
     Database release level               = 10.00
     Comment                              =
     Directory entry type                 = Indirect
     Catalog database partition number    = 0
     Alternate server hostname            =
     Alternate server port number         =

Step 2: Configure the communication protocol

Configure TCP/IP as follows:

  1. On server side 9.26.120.69 hotel53, run the following:
    Listing 3. Updating database environment variables and database manager configuration for TCP/IP
    db2set db2comm=TCPIP
    
    db2 update dbm cfg using SVCENAME xmiacob
    DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed 
    successfully.
    SQL1362W  One or more of the parameters submitted for immediate modification 
    were not changed dynamically. Client changes will not be effective until the 
    next time the application is started or the TERMINATE command has been issued. 
    Server changes will not be effective until the next DB2START command.
    
    db2stop
    SQL1064N  DB2STOP processing was successful.
    
    db2start
    SQL1063N  DB2START processing was successful.
  2. On client side 9.26.120.31 hotellnx96, run:
    Listing 4. Cataloging the node and the database from the client side
    db2 catalog tcpip node NT1 remote hotel53 server xmiacob
    DB20000I  The CATALOG TCPIP NODE command completed successfully.
    DB21056W  Directory changes may not be effective until the directory cache is 
    refreshed.
    
    db2 terminate
    DB20000I  The TERMINATE command completed successfully.
    
    db2 catalog database testdb at node nt1
    DB20000I  The CATALOG DATABASE command completed successfully.
    DB21056W  Directory changes may not be effective until the directory cache is 
    refreshed.
    
    db2 terminate
    DB20000I  The TERMINATE command completed successfully.
  3. Verify that user Einstein can connect to the TESTDB database.
    Listing 5. Connecting from 9.26.120.69 hotel53
              db2 connect to testdb user einstein using <password>
    
       Database Connection Information
    
     Database server        = DB2/LINUXX8664 10.1.0
     SQL authorization ID   = EINSTEIN
     Local database alias   = TESTDB
    Listing 6. Connecting from 9.26.120.31 hotellnx96
     db2 connect to testdb user einstein using <password>
    
       Database Connection Information
    
     Database server        = DB2/LINUXX8664 10.1.0
     SQL authorization ID   = EINSTEIN
     Local database alias   = TESTDB

    User einstein can connect to the TESTDB database from both IP addresses.

Step 3: Creating a procedure to allow restricted connections

The next step is to create a procedure to allow connections only from 9.26.120.69 hotel53. You have two options:

  • Option 1: Use the CLIENT_HOST global variable, as shown in the listing below:
    Listing 7. Creating a procedure with CLIENT_HOST
    db2 CREATE OR REPLACE PROCEDURE CP.CONNECT_PROCEDURE ()
    LANGUAGE SQL
    BEGIN
       IF ( CLIENT_HOST <> 'hotel53' )
       THEN
          SIGNAL SQLSTATE '42502' SET MESSAGE_TEXT='Connection refused !';
       END IF;
    END;
    DB20000I  The SQL command completed successfully.
  • Option 2: Use the CLIENT_IPADDR global variable, as shown in listing 8:
    Listing 8. Creating a procedure with CLIENT_IPADDR
    db2 CREATE OR REPLACE PROCEDURE CP.CONNECT_PROCEDURE ()
    LANGUAGE SQL
    BEGIN
       IF ( CLIENT_IPADDR <> '9.26.120.69' )
       THEN
          SIGNAL SQLSTATE '42502' SET MESSAGE_TEXT='Connection refused !';
       END IF;
    END;
    DB20000I  The SQL command completed successfully.

Step 4: Granting privileges on the procedure

Grant EXECUTE on the procedure to everyone and update the CONNECT_PROC database configuration parameter to use the CONNECT_PROCEDURE created above.

Listing 9. Granting privileges and updating the database configuration
db2 grant EXECUTE on procedure CP.CONNECT_PROCEDURE to PUBLIC
DB20000I  The SQL command completed successfully.

db2 update db cfg using CONNECT_PROC "CP.CONNECT_PROCEDURE"
DB20000I  The UPDATE DATABASE CONFIGURATION command completed 
successfully.

Step 5: Make sure connections are restricted as required

Verify that only connections from 9.26.120.69 hotel53 are allowed.

Listing 10. From 9.26.120.69 hotel53
db2 connect to testdb user einstein using <password>

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.1.0
 SQL authorization ID   = EINSTEIN
 Local database alias   = TESTDB
Listing 11. From 9.26.120.31 hotellnx96
db2 connect to testdb user einstein using <password>
SQL0438N  Application raised error or warning with diagnostic text: 
"Connection refused !".  SQLSTATE=42502

User einstein can connect to the TESTDB database only from IP address 9.26.120.69.

Note, DBADM will receive a SQL5187N return code if they try to drop, replace, or alter the connect procedure:

Listing 12. DBADM attempting to drop the procedure
db2 drop procedure CP.CONNECT_PROCEDURE
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL5187N  The "DROP PROCEDURE" operation is not allowed for procedure 
"CP.CONNECT_PROCEDURE" because a connection procedure with the same name 
is defined for the database.  SQLSTATE=429C8

Summary

By combining the capabilities of database configuration parameter CONNECT_PROC, and built-in global variables CLIENT_HOST or CLIENT_IPADDR we can control where users connect to the database from. Note that this solution does not apply to the following superusers: SYSADM, SYSCTRL, and SYSMAINT, because they have the authority to update the database configuration parameter CONNECT_PROC.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Security
ArticleID=926626
ArticleTitle=Restricting database connections using the CONNECT_PROC database configuration parameter in DB2 for Linux, Unix, and Windows
publish-date=05022013