Friday, July 17, 2015

DB2 Encryption: Some rotation before the weekend

Some bits on encryption...
Some while ago I wrote about DB2 encryption and how to rotate the master key. Well, today I rotated the master key again and looked at the metadata before and after the key rotation. I started with an encrypted database that I created some months ago.

The first step was to obtain the encryption information, i.e., metadata about the encrypted database. This can be done in two ways: Either using the utility db2pd or by calling the table function ADMIN_GET_ENCRYPTION_INFO.

Here is what db2pd shows of the database:



[hloeser@bodensee ~]$ db2pd -db enc2 -encryptioninfo

Database Member 0 -- Database ENC2 -- Active -- Up 0 days 00:30:31 -- Date 2015-07-17-15.14.31.704653

Encryption Info:
   Object Name:               ENC2   
   Object Type:               DATABASE
   Encyrption Key Info:      
          Encryption Algorithm: AES
     Encryption Algorithm Mode: CBC
         Encryption Key Length: 256
              Master Key Label: DB2_SYSGEN_hloeser_ENC2_2015-02-11-12.33.43
 Master Key Rotation Timestamp: 2015-02-11-13.33.43.000000
   Master Key Rotation Appl ID: *LOCAL.hloeser.150211113343
   Master Key Rotation Auth ID: HLOESER
     Previous Master Key Label: DB2_SYSGEN_hloeser_ENC2_2015-02-11-12.33.43
   KeyStore Info:            
                 KeyStore Type: PKCS12
             KeyStore Location: /home/hloeser/db2pwstore.p12
            KeyStore Host Name: bodensee
           KeyStore IP Address: 127.0.0.1
      KeyStore IP Address Type: IPV4


It prints out that the encrypted object is a database, which algorithm is used and how, some information about the current and the previous master key, and, last but not least, about the keystore. The administrative table function returns the same information:

[hloeser@bodensee ~]$ db2 "select * from table(sysproc.admin_get_encryption_info())"
 
 OBJECT_NAME                                                           OBJECT_TYPE     ALGORITHM    ALGORITHM_MODE  KEY_LENGTH MASTER_KEY_LABEL                                                                                                                        KEYSTORE_NAME                                                                                                                          KEYSTORE_TYPE    KEYSTORE_HOST                                                                                                                          KEYSTORE_IP               KEYSTORE_IP_TYPE PREVIOUS_MASTER_KEY_LABEL                                                                                                                    AUTH_ID                                                             APPL_ID                                                             ROTATION_TIME         
 -------------------------------------------------------------------------------------------------------------------------------- -------------------- ---------------- ---------------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------  
 ENC2                                                               DATABASE       AES       CBC           256 DB2_SYSGEN_hloeser_ENC2_2015-02-11-12.33.43                                                                                                           /home/hloeser/db2pwstore.p12                                                                                                                  PKCS12        oc6772581815.ibm.com                                                                                                                      127.0.0.1                IPV4       DB2_SYSGEN_hloeser_ENC2_2015-02-11-12.33.43                                                                                                           HLOESER                                                             *LOCAL.hloeser.150211113343                                                   2015-02-11-12.33.43.000000  
  1 record(s) selected.  


After obtaining the metadata, I rotated the master key by simply calling the procedure ADMIN_ROTATE_MASTER_KEY.
[hloeser@bodensee ~]$ db2 "CALL SYSPROC.ADMIN_ROTATE_MASTER_KEY (NULL)"

  Value of output parameters
  --------------------------
  Parameter Name  : LABEL
  Parameter Value : DB2_SYSGEN_hloeser_ENC2_2015-07-17-15.16.45

  Return Status = 0

Thereafter the metadata shows that and when the key was rotated (coloring by me). What I learned is how I can find out when the master key was rotated. If the DB2-generated key is used, then the previous master key label (see below) includes a timestamp which I could use to determine when the previous rotation was performed.



[hloeser@bodensee ~]$ db2pd -db enc2 -encryptioninfo

Database Member 0 -- Database ENC2 -- Active -- Up 0 days 00:32:50 -- Date 2015-07-17-15.16.50.463167

Encryption Info:
   Object Name:               ENC2   
   Object Type:               DATABASE
   Encyrption Key Info:      
          Encryption Algorithm: AES
     Encryption Algorithm Mode: CBC
         Encryption Key Length: 256
              Master Key Label: DB2_SYSGEN_hloeser_ENC2_2015-07-17-15.16.45
 Master Key Rotation Timestamp: 2015-07-17-15.16.46.000000
   Master Key Rotation Appl ID: *LOCAL.hloeser.150717124400
   Master Key Rotation Auth ID: HLOESER
     Previous Master Key Label: DB2_SYSGEN_hloeser_ENC2_2015-02-11-12.33.43
   KeyStore Info:            
                 KeyStore Type: PKCS12
             KeyStore Location: /home/hloeser/db2pwstore.p12
            KeyStore Host Name: bodensee
           KeyStore IP Address: 127.0.0.1
      KeyStore IP Address Type: IPV4