In my previous blog entries I already showed you how to create an encrypted DB2 database using the new "native encryption" and how to rotate the master keys using built-in procedures. Next in my list to test was taking an encrypted database backup. It is pretty straight-forward, just call BACKUP DATABASE and add the ENCRYPT option:
[henrik@mymachine]$ db2 backup database enc1 encrypt
Backup successful. The timestamp for this backup image is : 20150504135739
There are more options available, especially if you want to deviate from standard procedures. Restoring an encrypted database is similarly simple if dealing with a standard case:
[henrik@mymachine]$ db2 restore database enc1
SQL2539W The specified name of the backup image to restore is the same as the
name of the target database. Restoring to an existing database that is the
same as the backup image database will cause the current database to be
overwritten by the backup version.
Do you want to continue ? (y/n)
Usually all the needed information to decrypt the backup image is contained in the image itself and is taken from the operating environment. The encryption/decryption library, the data encryption key and its metadata (DEK, typically generated just for the backup), the algorithm, and more is part of the image, but the important stuff is encrypted using a master key. Information about the master key can be obtained from the operating environment. In my case the password for the key store is stashed, i.e., it can be read by DB2 and then used to grab the master password from the keystore.
But what can be done if the backup is older and the keystore does not contain the information anymore? What if you don't remember anymore what the master key and its label looked like? That's what I wanted to test. RESTORE DATABASE has an option to dump the master key details. An example can be found in the documentation (Knowledge Center). It only needs someone who is more competent than I to apply it. Here is my first attempt:
[henrik@mymachine]$ db2 restore database enc1 encropts 'show master key details'
SQL0104N An unexpected token "master" was found following "SHOW". Expected
tokens may include: "NO". SQLSTATE=42601
I tried variations of that statement, but always failed. Then I tried the DB2 CLP in interactive mode, and - well - succeeded. That got me thinking about and, oh yes, always use quotes around DB2 statements when passing them into CLP:
[henrik@mymachine]$ db2 restore "database enc1 from /home/hloeser ENCROPTS 'show master key details'"
SQL2539W The specified name of the backup image to restore is the same as the
name of the target database. Restoring to an existing database that is the
same as the backup image database will cause the current database to be
overwritten by the backup version.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
The above does not restore the database but only processes the "show master key detail". It creates a special dump file in DB2's diagnostic directory. In there details about the master key label and how the backup image was encrypted can be found:
KeyStore Type: PKCS12
KeyStore Location: /home/hloeser/db2pwstore.p12
KeyStore Host Name: mymachine
KeyStore IP Address: 127.0.0.1
KeyStore IP Address Type: IPV4
Encryption Algorithm: AES
Encryption Algorithm Mode: CBC
Encryption Key Length: 256
Master Key Label: DB2_SYSGEN_hloeser_ENC1_2015-01-22-12.04.12
I finished my quick tests with two lessons learned: 1) Remember the quotes around DB2 statements when passing them in from the Unix shell. 2) The new DB2 security/encryption feature works well and as documented. It is possible to retrieve some hints about how a backup image was encrypted. This is needed in case you need to rebuild a keystore with older master keys.