75 ways to demystify DB2: #40: ExpertAdvice: Learn how security mechanism is implemented in IBM DB2 JCC type 4 driver using an example

Body

Interested to learn how security mechanism works in JCC type 4 driver using a sample java program?

Here is a demo showing comparison of JCC property securityMechanism= CLEAR_TEXT_PASSWORD_SECURITY (3) and ENCRYPTED_USER_PASSWORD_AND_DATA_SECURITY (13) with JCC trace buffers.

Notes: 

securityMechanism= ENCRYPTED_USER_PASSWORD_AND_DATA_SECURITY (13) - Encrypted user ID, encrypted password, and encrypted security-sensitive data
securityMechanism= CLEAR_TEXT_PASSWORD_SECURITY (3) - Clear text userid and password

Environment details for this test:

Local setup, application and database server on the same machine.
DB2 LUW 10.5 FP3 database server on Windows
JCC Driver: 3.67.27 (10.5 FP3)

Test 1: Using DB2 for Linux, UNIX, and Windows server authentication type SERVER (default) for  JCC driver securityMechanism setting: CLEAR_TEXT_PASSWORD_SECURITY (3)
 

Test 2: Using  DB2 for Linux, UNIX, and Windows server authentication type DATA_ENCRYPT for JCC driver securityMechanism setting: ENCRYPTED_USER_PASSWORD_AND_DATA_SECURITY (13)

C:\Program Files\IBM\SQLLIB\java\jdk\bin>db2 get dbm cfg | FIND "AUTHENTICATION"

 Database manager authentication        (AUTHENTICATION) = DATA_ENCRYPT

C:\Program Files\IBM\SQLLIB\java\jdk\bin>

Test 1 Sample code:
Note: Test 2 sample code was not included here since it is exactly the same code as Test1 except for securityMechanism JCC property value set to 13

/* Type 4 example */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.*;
import java.io.PrintWriter;

class T4test
{
        public static void main(String[] args)
        {
               if( args.length!= 5 )
            {
                System.out.println("Usage: java T4zostest hostname:port dbname userid password c:\\temp\\jcctrace.txt");
                System.out.println("Note: The traceoutput path will be different on Unix/Linux, specify /tmp/jcctrace");
                System.exit(1);
            }

               try
               {
                   
                   String hostname=args[0];

                   // Database name
                   String dbName=args[1];
                   String userID=args[2];
                   String passWord=args[3];
                   String traceOut=args[4];

                   Class.forName("com.ibm.db2.jcc.DB2Driver");
                                    String URL="jdbc:db2://" + hostname + "/" + dbName + ":securityMechanism=3;traceFile=" + traceOut + ";";

                   System.out.println("URL is: " +URL);

                   System.out.println("Trying to connect using JCC T4 driver");
                   Connection con = DriverManager.getConnection(URL, userID, password);
                   System.out.println("Connected to database\n");

                   Statement stmt = con.createStatement();
                   System.out.println("Executing query: SELECT * FROM SYSIBM.SYSDUMMY1");
                        ResultSet rs = stmt.executeQuery("SELECT * FROM SYSIBM.SYSDUMMY1");

                   if (rs == null )
                   {
                       System.out.println("Resultset is null\n");
                   }
                   else
                   {
                       System.out.println("Result set is not null, retrieving rows..\n");
                       while(rs.next())
                       {
                           System.out.println("Row: " + rs.getString(1));
                       }
                   }
                   System.out.println("Finished retrieving all result rows");

                   rs.close();

                   stmt.close();

            //cs.close();
                   con.close();
                   System.out.println("Disconnected from database\n");

               }

               catch (ClassNotFoundException cnfe)
               {
                cnfe.printStackTrace();
                }

               catch (SQLException sqle)
               {
                   sqle.printStackTrace();
               }

        } // end main

}  // end T4test

Compile: javac T4test.java
Execute: java T4test xxx:50000 <dbname> <userid> <password> C:\\temp\\jcctraceclear.out

jcctraceclear.out-> Test1

jcctrace.out -> Test2

db2admin -> OS Client Userid -> passed

During initial handshake, OS Client userid is passed - which is not of interest to us.

Notice, after security mechanism negotiation (SECCHK) in Test2, database userid login is encrypted (not readable), also, see User: **** on the right hand side while Test1 (left hand side) shows clear text.

==

======

References:

Security under the IBM Data Server Driver for JDBC and SQLJ
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_cjvjcsec.html

Encrypted password, user ID, or data security under the IBM Data Server Driver for JDBC and SQLJ:
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_cjvjcsen.html
 

Thanks for reading. Hope it helps! 
 

Please leave a comment if you have any questions or feedback.