Databases can contain sensitive information; these days, the main concern is related to the security of data stored in tables as well as those sent over the network. One method of securing network communication between server and client is SSL, which is actually an abbreviation for Secure Socket Layer. We do not delve further into too much theory. Mainly, SSL addresses the following important security considerations: authentication, confidentiality, and integrity. Mainly SSL encryption and other network communication or also named data in transit encryption methods protects against unauthorized packet interception and analysis performed by an interposed person between a client and a server, also known as eavesdropping.
The DB2 instance has built-in support for SSL. DB2 relies on Global Security Kit for implementing SSL. GSKit is included in the IBM DB2 ESE software installation kit or is downloadable for free from IBM's website. Next, we'll show how to implement a secure connection between a DB2 server and a DB2 client.
For the next recipe, we will use nodedb21 (db2inst1 instance) as server and nodedb22 (db2clnt1 instance) as client, where we have installed DB2 Client in previous recipes. You need to ensure that you have GSKit libraries in LD_LIBRARY_PATH. In our case, the libraries that are located in /home/db2inst1/sqllib/lib64 are pointing to the /opt/ibm/db2/V9.7/lib64 location.
The first step is to add the
gsk8capicmd_64executable in ourPATH.Include the following in
.bash_profile:PATH=$PATH:$HOME/bin:$HOME/sqllib/gskit/binExecute
source .bash_profileto reinitialize the user environment.To create a key database on the server, execute the following (for more information about
gsk8capicmd_64, executegsk8capicmd_64 –help):[db2inst1@nodedb21 ~]$ gsk8capicmd_64 -keydb -create -db "/home/db2inst1/keystoredb2inst1.kdb" -pw "db2cookbook" -stash [db2inst1@nodedb21 ~]$
Create a self-signature and self-sign the key database on the server:
[db2inst1@nodedb21 ~]$ gsk8capicmd_64 -cert -create -db "/home/db2inst1/keystoredb2inst1.kdb" -pw "db2cookbook" -label "db2cookbooksignature" -dn "CN=www.packtpub.com,O=Packt Publishing,OU=Packt Publishing" [db2inst1@nodedb21 ~]$
Extract the signature for signing the client key database:
[db2inst1@nodedb21 ~]$ gsk8capicmd_64 -cert -extract -db "/home/db2inst1/keystoredb2inst1.kdb" -label "db2cookbooksignature" -target "/home/db2inst1/db2cookbook.arm" -format ascii -fips -pw "db2cookbook" [db2inst1@nodedb21 ~]$
Next, create the client key database:
[db2inst1@nodedb21 ~]$ gsk8capicmd_64 -keydb -create -db "/home/db2inst1/keystoreclientdb2inst1.kdb" -pw "db2ckbk" –stash [db2inst1@nodedb21 ~]$
Import the self-signed certificate into the client key database:
[db2inst1@nodedb21 ~]$ gsk8capicmd_64 -cert -add -db "/home/db2inst1/keystoreclientdb2inst.kdb" -pw "db2ckbk" -label "db2cookbooksignature" -file "/home/db2inst1/db2cookbook.arm" -format ascii –fips [db2inst1@nodedb21 ~]$
To enable SSL as communication protocol on
nodedb21, execute the following:[db2inst1@nodedb21 ~]$ db2set DB2COMM=tcpip,ssl –i [db2inst1@nodedb21 ~]$
Enable SSL as communication protocol also on the client side:
[db2clnt1@nodedb21 ~]$ db2set DB2COMM=tcpip,ssl –i [db2clnt1@nodedb21 ~]$
Next, on
nodedb21, set SSL-related parameters on the server instance; then, stop and start the instance:[db2inst1@nodedb21 ~]$ db2 "update dbm cfg using ssl_svr_keydb /home/db2inst/keystoredb2inst1.kdb" DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. [db2inst1@nodedb21 ~]$ db2 "update dbm cfg using ssl_svr_stash /home/db2inst/keystoredb2inst1.sth" DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. [db2inst1@nodedb21 ~]$ db2 "update dbm cfg using ssl_svr_label db2cookbooksignature" DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. [db2inst1@nodedb21 ~]$ db2 "update dbm cfg using ssl_svcename 50004" DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. [db2inst1@nodedb21 ~]$ db2stop 06/09/2011 19:08:39 0 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. [db2inst1@nodedb21 ~]$ db2start 06/09/2011 19:08:45 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful.
Note
Description of SSL-related parameters used on the server side:
SSL_SVR_KEYDBspecifies a fully qualified filepath of the key file to be used for SSL setup at server sideSSL_SVR_STASH—specifies a fully qualified filepath of the stash file to be used for SSL setup at server sideSSL_SVR_LABEL—specifies a label of the personal certificate of the server in the key databaseSSL_SVCENAME—specifies the name of the port that a database server uses to await communications from remote client nodes using SSL protocolBe careful to set the correct paths, otherwise SSL won't work.
Copy
/home/db2inst1/keystoreinstclient.kdband/home/db2clnt1/keystoreinstclient.sthtonodedb22.On
nodedb22, set SSL DB2 client instance-related parameters:[db2clnt1@nodedb22 ~]$ db2 "update dbm cfg using SSL_CLNT_KEYDB /home/db2clnt1/keystoreclientdb2inst.kdb" DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. [db2clnt1@nodedb22 ~]$ db2 "update dbm cfg using SSL_CLNT_STASH /home/db2clnt1/keystoreclientdb2inst.sth" DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.
Note
Description of SSL-related parameters on the client side:
SSL_CLNT_KEYDBspecifies the fully qualified filepath of the key file to be used for SSL connection at the client sideSSL_CLNT_STASHspecifies the fully qualified filepath of the stash file to be used for SSL connections at the client sideNext, copy GSKit libraries to the client's
DB2HOME/lib64directory:[root@nodedb22 ~]# cp /opt/ibm/db2/V9.7_part/lib64/libgsk8* /opt/ibm/db2/V9.7/lib64/ [root@nodedb22 ~]#
SSL establishes the connection between client and server using a mechanism called handshake. There is a lot of information on the Internet about SSL and its working. Briefly, these are the steps for SSL handshake:
The client requests an SSL connection, listing its SSL version and supported cipher suites.
The server responds with a selected cipher suite.
The server sends its digital certificate to the client.
The client verifies the validity of the server's certificate (server authentication).
Client and server securely negotiate a session key.
Client and server securely exchange information using the key selected previously.
In this recipe, we used a self signed certificate, which is fine for testing or internal use. For production environments, you should use trusted certificates signed by a third-party certification authority.
Other methods for encrypting data in transit can be implemented by using DATA_ENCRYPT and DATA_ENCRYPT_CMP as authentication methods. Also using port forwarding with SSH tunnels is a good option.
Chapter 10, DB2 Security