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

A technique for protecting data access

System administrators are responsible for, among other things, protecting a database against unauthorized access or misuse by authorized database users (for example, inappropriate access to sensitive information within a database). A common requirement to mitigate such risks is ensuring that users are allowed to connect to the database only from a list of trusted hosts or IP addresses that are known to be secure. This article gives a practical example of how such a requirement can be put in practice by making use of the CONNECT_PROC database configuration parameter of IBM® DB2® for Linux®, UNIX®, and Windows®. This is an alternative to the solution presented by the article Restricting database connections using trusted contexts in DB2 for Linux, UNIX, and Windows.

Mihai Iacob (miacob@ca.ibm.com), Software Developer, IBM

Author photo of Mihai IacobMihai Iacob has been working as a software developer at the IBM Toronto Lab for the past seven years as part of the DB2 for Linux, UNIX, and Windows Security Development team.



Walid Rjaibi (wrjaibi@ca.ibm.com), Chief Security Architect for DB2 LUW, IBM

Walid Rjaibi author photo

Walid Rjaibi is an IBM Senior Technical Staff Member and member of IBM's Security Architecture Board Steering Committee. He is the Chief Security Architect for DB2 for Linux, UNIX, and Windows (LUW) and has direct technical and management oversight over the architecture, design, development, and common criteria certification of all security capabilities in DB2 LUW. This includes identification and authentication, authorization, access control, data masking, auditing, and encryption.

Prior to his current role, Walid was a Research Staff Member at the IBM Zurich Research Lab in Switzerland where he established and led a new research program focused on database security and privacy. Walid's research results were the foundation for key security enhancements in DB2 LUW and for which he led the actual development efforts upon his return to DB2 LUW development in Toronto.

Walid's key database security achievements include leading the research and development of label-based access control, role-based access control, trusted contexts, separation of duties, and fine-grained access control. His work resulted in 20 patents and several publications in the proceedings of leading scientific conferences, such as the International Conference on Very Large Databases (VLDB), the International Conference on Data Engineering (ICDE), and the International Conference on Security and Cryptography (SECRYPT).



02 May 2013

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.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


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