Encrypting your database with DB2 10.5

- David Sciaraffa, IBM IM & Analytics Accelerated Value Leader (Premium Support Manager).

Data security has become one of the hottest topics of 2015, and rightfully so. In light of recent high-profile thefts of sensitive data at iconic corporate and federal entities, CSO's are adopting stricter security and compliance requirements (such as PCI DSS, HIPPA) which require encryption of all data at rest.

In DB2/LUW v10.5 fixpack 5, full support for native database encryption is added. It is simple to enable, and completely transparent to applications using the database.  In this post I provide some examples of how to encrypt new or existing databases, and manage encryption keys within a key-store.

---

When a DB2 database is encrypted using this feature, all tablespace containers, backup images, recovery log files, Load copy image and staging files, and diagnostic raw dump files (.bin) will be encrypted on disk.
This solution does not encrypt data over the wire (ie. network data communication between the database server and the remotely connected application).
Data on disk is encrypted by DB2 using a symmetric data encryption key (DEKey) which is internally managed by DB2, and the DEKey is encrypted using a master key (MstrKey) which is externally managed by the user within a key-store. Thus, this solution requires the creation of a key-store for storage of master keys, which is quite simple.
 

First Step - Create a key-store

Before we begin encrypting databases, we need to create a key-store.
The gsk8capicmd(_64) program is used for creation and maintenance of the key-store.  This program is packaged with DB2 and located within the db2 instance directory (~/sqllib/gskit/bin/).

First, I update the PATH and  LIBPATH or LD_LIBRARY_PATH environment variables to include the gskit folder included within the Db2 instance directory:

AIX environments --
$ export PATH=$HOME/sqllib/gskit/bin:$PATH
$ export LIBPATH=$HOME/sqllib/lib64/gskit:$LIBPATH

Linux environments --
$ export LD_LIBRARY_PATH=$HOME/sqllib/lib64/gskit:$LIBPATH
$ export PATH=$HOME/sqllib/gskit/bin:$PATH

(see this technote for more details on setting these paths: http://www-01.ibm.com/support/docview.wss?uid=swg21650305)

Here I create a key-store with filename '/home/db2inst8/mykeystore1.db':

$ ~/sqllib/gskit/bin/gsk8capicmd_64 -keydb -create -db /home/db2inst8/mykeystore1.db -type pkcs12 -pw "PearlJam5Against1" -strong -stash

--- my key-store resides within the file '/home/db2inst8/mykeystore1.db'.
--- my key-store password "PearlJam5Against1" would normally be required later to retrieve and manage the master keys within the key-store. However:
--- the -stash option allows us to store this password into a separate stash file (with extension .sth), and avoid having to specify the password or being prompted for it. While this is slightly less secure, in the real world it is useful if the database is started by an automated script or tooling, since it avoids prompting for the key-store password which requires manual input. The password is obfuscated within the stash file, however it is not encrypted. OS file permissions must be enforced to protect this stash file, it should only be readable/writeable by the db2 instance owner id (db2inst8 in my example):

$ ls -l mykeystore1*

-rw------- 1 db2inst8 db2inst8 0 Mar 11 14:37 mykeystore1.db
-rw------- 1 db2inst8 db2inst8 129 Mar 11 14:37 mykeystore1.sth

Next, I update my database-instance configuration with the location of my key-store:

$ db2 update dbm config using KEYSTORE_LOCATION /home/db2inst8/mykeystore1.db KEYSTORE_TYPE PKCS12
$ db2stop

When restarting the database-instance, I provide the key-store password in this example,  however it's not actually necessary since my password is stashed, so a simple 'db2start' would suffice too.

$ db2start open key-store using "PearlJam5Against1"

 

Example 1 – Create a new database, and let DB2 create the master-key.

I simply create a database using the ENCRYPT option.
By default, DB2 will generate an AES 256bit (32byte) master key and inserted into the key-store.

$ db2 create db mydb1 encrypt

I can see the master key listed within my key-store. The automatically generated master key has a label like "DB2_SYSGEN_[instance]_[database]_[timestamp]":

$ ~/sqllib/gskit/bin/gsk8capicmd_64 -cert -list -db /home/db2inst8/mykeystore1.db -stashed

Certificates found
* default, - personal, ! trusted, # secret key
# DB2_SYSGEN_db2inst8_MYDB1_2018-03-11-23.22.45

I use the ADMIN_GET_ENCRYPTION_INFO() table function to confirm that our database is encrypted:

$ db2 connect to mydb1$ db2 "select MASTER_KEY_LABEL from TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO()) where OBJECT_NAME='MYDB1' and OBJECT_TYPE='DATABASE'"

MASTER_KEY_LABEL
------------------------------------------------
DB2_SYSGEN_db2inst8_MYDB1_2018-03-11-23.22.45

 

Example 2: Create a new database and use my own master key.

First, I generate a 256bit (32byte) key and insert the key into my key-store using the –create option, and provide an informative label that includes the name of my database-instance and database that I intend to encrypt with this master key, as well as a timestamp:

$ ~/sqllib/gskit/bin/gsk8capicmd_64 -secretkey –create size 32 -db /home/db2inst8/mykeystore1.db -label DB2_MstrKey_INST_db2inst8_DB_mydb2_TS_$(date "+%Y%m%d_%H%M%S") -stashed -file tmpKeyData.raw

I can now see the master key listed within my key-store:

$ ~/sqllib/gskit/bin/gsk8capicmd_64 -cert -list -db /home/db2inst8/mykeystore1.db -stashed

Certificates found
* default, - personal, ! trusted, # secret key
#      DB2_MstrKey_INST_db2inst8_DB_mydb2_TS_20180311_224423

Next, I create the database using our master key:

$ db2 create db mydb2 encrypt master key label DB2_MstrKey_INST_db2inst8_DB_mydb2_TS_20180311_224423

I use the ADMIN_GET_ENCRYPTION_INFO() table function to confirm that our database is encrypted:

$ db2 connect to mydb2$ db2 "select MASTER_KEY_LABEL from TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO()) where OBJECT_NAME='MYDB2' and OBJECT_TYPE='DATABASE'"

MASTER_KEY_LABEL
------------------------------------------------------
DB2_MstrKey_INST_db2inst8_DB_mydb2_TS_20180311_224423

 

Example 3: Rotating the master key.

NEVER delete the old master keys from the key-store! They will be required if the database is ever Restored.

I create and insert a new 32 byte random key into my key-store using the “-create -size 32” option, and provide an informative label that includes the name of the instance and database that I intend to encrypt with this master key, as well as a timestamp:

$ ~/sqllib/gskit/bin/gsk8capicmd_64 -secretkey –create -size 32 -db /home/db2inst8/mykeystore1.db -label DB2_MstrKey_INST_db2inst8_DB_mydb2_TS_$(date "+%Y%m%d_%H%M%S") -stashed -file tmpKeyData2.raw

I can see the new master key listed within my key-store:

$  ~/sqllib/gskit/bin/gsk8capicmd_64 -cert -list -db /home/db2inst8/mykeystore1.db -stashed

Certificates found
* default, - personal, ! trusted, # secret key
# DB2_MstrKey_INST_db2inst8_DB_mydb2_TS_20180311_224423
# DB2_MstrKey_INST_db2inst8_DB_mydb2_TS_20180312_100537

I use the ADMIN_ROTATE_MASTER_KEY() table function to use the new master key.

$ db2 "CALL SYSPROC.ADMIN_ROTATE_MASTER_KEY('DB2_MstrKey_INST_db2inst8_DB_mydb2_TS_20180312_100537')"

I use the ADMIN_GET_ENCRYPTION_INFO() table function to confirm that my database is using the new master key.

$ db2 connect to mydb2
$ db2 "select MASTER_KEY_LABEL  from TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO()) where OBJECT_NAME='MYDB2' and OBJECT_TYPE='DATABASE'"

MASTER_KEY_LABEL
-------------------------------------------------------
DB2_MstrKey_INST_db2inst8_DB_mydb2_TS_20180312_100537

 

Example 4: Encrypt an existing database.

This is accomplished by Restoring a backup image of the database and specifying the ENCRYPT option.
For this example, I take a backup of a non-encrypted database (I use an offline backup, but online backups are supported too):

$ db2 create db mydb3

$ db2 backup db mydb3 to /home/db2inst8/backup_path/

I restore the non-encrypted backup and specify the ENCRYPT option.

$ db2 drop db mydb3

$ db2 restore db mydb3 from /home/db2inst8/backup_path/ encrypt
$ db2 rollforward db mydb3 to end of logs and complete

I use the ADMIN_GET_ENCRYPTION_INFO() table function to confirm that my database is encrypted.

$ db2 "select MASTER_KEY_LABEL from TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO()) where OBJECT_NAME='MYDB3' and OBJECT_TYPE='DATABASE'"

MASTER_KEY_LABEL
------------------------------------------------------
DB2_SYSGEN_db2inst8_MYDB3_2018-03-12-14.11.41

I can also see this new master key listed within my key-store:

$ ~/sqllib/gskit/bin/gsk8capicmd_64 -cert -list -db /home/db2inst8/mykeystore1.db -stashed

Certificates found
* default, - personal, ! trusted, # secret key
# DB2_SYSGEN_db2inst8_MYDB1_2018-03-11-23.22.45
# DB2_MstrKey_INST_db2inst8_DB_mydb2_TS_20180311_224423
# DB2_MstrKey_INST_db2inst8_DB_mydb2_TS_20180312_100537
# DB2_SYSGEN_db2inst8_MYDB3_2018-03-12-14.11.41

 

Example 5:  Restore an encrypted database into a secondary environment

This is accomplished by either moving the key-store database to the secondary environment (simply copying the file will suffice), or if a key-store already exists in the secondary environment we can export/import the key-store contents from the source into the secondary environment, followed by the Restore operation.

In my source environment:
I take a backup of an encrypted database from my db2inst8 instance.

$ db2 create db mydb4 encrypt
$ db2 backup db mydb4 to /home/db2inst8/backup_path/ encrypt

I determine the master key for my db:

$ db2 "select MASTER_KEY_LABEL from TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO()) where OBJECT_NAME='MYDB4' and OBJECT_TYPE='DATABASE'"

MASTER_KEY_LABEL
-----------------------------------------------------
DB2_SYSGEN_db2inst8_MYDB4_2018-03-12-14.28.30

I export this master key:

$ ~/sqllib/gskit/bin/gsk8capicmd_64 -cert -export -db ~/mykeystore1.db -stashed -label DB2_SYSGEN_db2inst8_MYDB4_2018-03-12-14.28.30 -target /home/db2inst8/tmpKeyData3.raw -target_type pkcs12 -target_pw "SmashingPumpkins1979"

$ ls -l /home/db2inst8/tmpKeyData3*

-rw------- 1 db2inst8 db2inst8 80 Mar 12 14:40 tmpKeyData3.crl
-rw------- 1 db2inst8 db2inst8 5080 Mar 12 14:40 tmpKeyData3.raw
-rw------- 1 db2inst8 db2inst8 80 Mar 12 14:40 tmpKeyData3.rdb

I then transfer these master key export files and DB2 backup image to my secondary environment.

In my secondary environment,
If one doesn't already exist, I create a key-store with 32 byte random key, and arbitrary filename 'mykeystore2.db':

$ ~/sqllib/gskit/bin/gsk8capicmd_64 -keydb –create –size 32 -db /home/db2inst9/mykeystore2.db -type pkcs12 -pw "Alice1In2Chains" -strong -stash

If needed, I update the database instance configuration with the location of my key-store:

$ db2 update dbm config using KEYSTORE_LOCATION /home/db2inst9/mykeystore2.db KEYSTORE_TYPE PKCS12

$ db2start  
(since my key-store password is stashed, I don't need to specify the key-store password in my db2start command)

I import this master key into the key-store in my secondary environment.

$ ~/sqllib/gskit/bin/gsk8capicmd_64 -cert -import -db /path/tmpKeyData3.raw -pw "SmashingPumpkins1979" -stashed -target /home/db2inst9/mykeystore2.db -target_type pkcs12

$~/sqllib/gskit/bin/gsk8capicmd_64 -cert -list -db /home/db2inst9/mykeystore2.db -stashed

Certificates found
* default, - personal, ! trusted, # secret key
#       DB2_SYSGEN_db2inst8_MYDB4_2018-03-12-14.28.30

I can now Restore the database image into my secondary instance.

$ db2 restore db mydb4 from /path/
$ db2 rollforward db mydb4 to end of logs and complete
$ db2 "select MASTER_KEY_LABEL from TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO()) where OBJECT_NAME='MYDB4' and OBJECT_TYPE='DATABASE'"

MASTER_KEY_LABEL
------------------------------------------------------
DB2_SYSGEN_db2inst8_MYDB4_2018-03-12-14.28.30

 

Example 6: Encrypting an existing hadr database pair.

This is accomplished on the Primary by Restoring a backup image of the database and specifying the ENCRYPT option. Copying the key-store from the Primary to Standby, and then taking a backup of the newly encrypted Primary database and Restoring it on the standby.

First, let's setup a non-encrypted pair of HADR databases to play with:

In my example, for simplicity, I use two different database instances (db2inst8 and db2ins10) on the same host (hobbits).

hostA (db2inst8@hobbits.torolab.ibm.com):
$ db2 create db mydb5 on /home/db2inst8/
$ mkdir /home/db2inst8/archive_log_mydb5    (make a directory for db2 recovery log files)
$ db2 update db cfg for mydb5 using logarchmeth1 disk:/home/db2inst8/archive_log_mydb5/
$ db2 backup db mydb5 to /dev/null
(Find an unused port number which is neither in "netstat -a", nor in /etc/services. I chose port 60512).
$ db2 update db cfg for mydb5 using HADR_LOCAL_HOST hobbits
$ db2 update db cfg for mydb5 using HADR_LOCAL_SVC 60512
$ db2 backup db mydb5
hostB (db2ins10@hobbits.torolab.ibm.com):
$ mkdir /home/db2ins10/archive_log_mydb5  (make a directory for db2 recovery log files)
$ db2 restore db mydb5 from /path_to_backup_image/ on /home/db2ins10/
$ db2 update db config for mydb5 using logarchmeth1 DISK:/home/db2ins10/archive_log_mydb5/
(Find an unused port number which is neither in "netstat -a", nor in /etc/services. I chose port 60513).
$ db2 update db cfg for mydb5 using HADR_LOCAL_HOST hobbits
$ db2 update db cfg for mydb5 using HADR_LOCAL_SVC 60513
$ db2 update db cfg for mydb5 using HADR_REMOTE_HOST hobbits
$ db2 update db cfg for mydb5 using HADR_REMOTE_SVC 60512
$ db2 update db cfg for mydb5 using HADR_REMOTE_INST db2inst8
hostA (db2inst8@hobbits.torolab.ibm.com):
$ db2 update db cfg for mydb5 using HADR_REMOTE_HOST hobbits
$ db2 update db cfg for mydb5 using HADR_REMOTE_SVC 60513
$ db2 update db cfg for mydb5 using HADR_REMOTE_INST db2ins10
hostB (db2ins10@hobbits.torolab.ibm.com):
$ db2 start hadr on database mydb5 as standby
hostA (db2inst8@hobbits.torolab.ibm.com):
$ db2 start hadr on database mydb5 as primary
$ db2pd -db mydb5 -hadr    (verify that HADR is in Peer state)

Let's encrypt our existing Primary side database:

First, since I don't already have a key-store on this instance, I created one with filename 'mykeystore3.db':

hostA (db2inst8@hobbits.torolab.ibm.com):
$ ~/sqllib/gskit/bin/gsk8capicmd_64 -keydb -create -db /home/db2inst8/mykeystore3.db -type pkcs12 -pw "PlatinumBlonde85" -strong -stash

$ db2 update dbm config using KEYSTORE_LOCATION /home/db2inst8/mykeystore3.db KEYSTORE_TYPE PKCS12

I backup my Primary database (if I don't already have a backup available):

hostA (db2inst8@hobbits.torolab.ibm.com):
$ db2 backup db mydb5 online

I deactivate and stop my Standby database:

hostB (db2ins10@hobbits.torolab.ibm.com):
$ db2 deactivate db mydb5
$ db2stop

I deactivate and drop my Primary database, and Restore the backup image using the ENCRYPT option:

hostA (db2inst8@hobbits.torolab.ibm.com):
$ db2 deactivate db mydb5
$ db2 restore db mydb5 on /home/db2inst8/ ENCRYPT
$ db2 rollforward db mydb5 to end of logs and complete
$ db2 "select MASTER_KEY_LABEL from TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO()) where OBJECT_NAME='MYDB5' and OBJECT_TYPE='DATABASE'"

MASTER_KEY_LABEL
----------------------------------------------
DB2_SYSGEN_db2inst8_MYDB5_2018-03-13-15.39.35

Next, backup the encrypted database:

hostA (db2inst8@hobbits.torolab.ibm.com):
$ db2 backup db mydb5 online

Export this master key from the key store:

hostA (db2inst8@hobbits.torolab.ibm.com):
$ ~/sqllib/gskit/bin/gsk8capicmd_64 -cert -export -db /home/db2inst8/mykeystore3.db -stashed -label DB2_SYSGEN_db2inst8_MYDB5_2018-03-13-15.39.35 -target /home/db2inst8/tmpKeyData4.raw -target_type pkcs12 -target_pw "Metallica1Enter2Sandman"

$ ls -l /home/db2inst8/tmpKeyData4*

-rw------- 1 db2inst8 db2inst8   80 Mar 13 15:44 tmpKeyData4.crl
-rw------- 1 db2inst8 db2inst8 5080 Mar 13 15:45 tmpKeyData4.raw
-rw------- 1 db2inst8 db2inst8   80 Mar 13 15:44 tmpKeyData4.rdb

Import this key into the key-store on the Standby instance  (create the key-store if you don't already have one, or simply copy whole key-store file from Primary to Standby host).

hostB (db2ins10@hobbits.torolab.ibm.com):
$ ~/sqllib/gskit/bin/gsk8capicmd_64 -cert -import -db /home/db2ins10/tmpKeyData4.raw -pw "Metallica1Enter2Sandman" -stashed -target /home/db2ins10/mykeystore3.db -target_type pkcs12

Update our Standby host to point to the key-store (if needed):

hostB (db2ins10@hobbits.torolab.ibm.com):
$ db2 update dbm config using KEYSTORE_LOCATION /home/db2ins10/mykeystore3.db KEYSTORE_TYPE PKCS12

Restore the backup image on our Standby host:

hostB (db2ins10@hobbits.torolab.ibm.com):
$ db2 drop db mydb5

(Confirm the master key within the backup image. This command generates a clear text file within the sqllib/db2dump/ folder called  "[dbname].0.[instance].[dbptn].[timestamp].masterKeyDetails” ).
$ db2 "restore database mydb5 on /home/db2ins10/ ENCROPTS 'show master key details' ENCRYPT"
$ cat ~/sqllib/db2dump/MYDB5.0.db2inst8.DBPART000.20180313154122.masterKeyDetails | grep “Label”        
Master Key Label: DB2_SYSGEN_db2inst8_MYDB5_2018-03-13-15.39.35

$ db2 "restore database mydb5 on /home/db2ins10/ ENCRYPT"

Reestablish HADR:

hostB (db2ins10@hobbits.torolab.ibm.com):
$ db2 update db config for mydb5 using logarchmeth1 DISK:/home/db2ins10/archive_log_mydb5/
$ db2 update db cfg for mydb5 using HADR_LOCAL_HOST hobbits
$ db2 update db cfg for mydb5 using HADR_LOCAL_SVC 60513
$ db2 update db cfg for mydb5 using HADR_REMOTE_HOST hobbits
$ db2 update db cfg for mydb5 using HADR_REMOTE_SVC 60512
$ db2 update db cfg for mydb5 using HADR_REMOTE_INST db2inst8
$ db2 start hadr on database mydb5 as standby

hostA (db2inst8@hobbits.torolab.ibm.com):
$ db2 start hadr on database mydb5 as standby

---

You can find an excellent video presentation about Database Encryption here:
An Overview of the New DB2 Native Encryption Capability: https://www.brighttalk.com/webcast/7637/143131

Also, be aware of a memory leak apar involving Native Encryption that exists in this fixpack5:
IT08289: PRIVATE MEMORY LEAK DUE TO NATIVE ENCRYPTION.

---

I hope this information is useful. If you have any thoughts or questions, feel free to submit a comment below.