Technical Blog Post
Abstract
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.
UID
ibm11141042