 |
| Some bits on encryption... |
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