How to Encrypt DB2 Database

In blog DB2 Native Encryption we went through introduction to DB2 Native Encryption, license requirement, Data Encryption and Master Key, different Keystore supported by DB2, operation for which Keystore is access and performance impact of encrypting database. In this blog we will see step by step process to enable DB2 Native Encryption and Encrypt DB2 database.

How to Encrypt DB2 Database?

Step1: Planning

Before implementing DB2 Native Encryption and encrypting your database you should determine Keystore type i.e. Local KeyStore or Centralized KeyStore. Benchmark your database performance to determine variance in performance after enabling encryption. 

Your DB2 version should be DB2 10.5 FP 5 or above to support DB2 Native Encryption. If you are on DB2 10.5, depending on your DB2 edition you would need additional license of IBM DB2 Encryption Offering to use DB2 Native Encryption. From DB2 11.1 DB2 Native Encryption is available in all Db2 edition.

In this blog we will encrypt DB2 Database using Local Keystore

Check blog Encrypt DB2 Database using Centralized KeyStore for step by step process to enable DB2 Native Encryption using Centralized KeyStore

Step 2: Include gskit in LD_LIBRARY_PATH, LIBPATH, SHLIB_PATH environment variable.

Update LD_LIBRARY_PATHLIBPATHSHLIB_PATH to include both 32 and 64 bit gskit path. Update DB2 instance user profile file to include below

PATH=$HOME/sqllib/gskit/bin:$PATH
export PATH
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$HOME/sqllib/lib64/gskit
export LD_LIBRARY_PATH
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$HOME/sqllib/lib32/gskit
export LD_LIBRARY_PATH
LIBPATH=$LIBPATH:$HOME/sqllib/lib64/gskit
export LIBPATH
LIBPATH=$LIBPATH:$HOME/sqllib/lib32/gskit
export LIBPATH
SHLIB_PATH=$SHLIB_PATH:$HOME/sqllib/lib64/gskit
export SHLIB_PATH
SHLIB_PATH=$SHLIB_PATH:$HOME/sqllib/lib32/gskit
export SHLIB_PATH

DB2 Instance User profile file


















Step 3: Create Local KeyStore

Create a local Keystore of type PKCS12 using gsk8capicmd_64 command using stash option. 

gsk8capicmd_64 -keydb -create -db "DB2TEST1_DB2I1.ps12" -pw "P@ssw0rd_Db@i!" -type pkcs12 -stash

Create Local KeyStore using gsk8capicmd_64

















gsk8capicmd_64 will not give any output on completion, it will create 2 files in your current working directory. It is necessary to create Keystore with stash option otherwise for every Keystore access you need to provide Keystore password.

You should ensure that your Keystore and stash file is secure, to do this change your Keystore directory access to 700.

chmod 700 /home/db2i1/.KeyStore

Change Keystore directory Permission






Step 4: Update database manager configuration (DBM CFG) parameters

Update KEYSTORE_LOCATION  and KEYSTORE_TYPE DBM CFG parameters to value keystore path created in step 3 and PKCS12  respectively.

 db2 update dbm cfg using KEYSTORE_LOCATION <path>
 db2 update dbm cfg using KEYSTORE_TYPE PKCS12

Update KEYSTORE_LOCATION KEYSTORE_TYPE DBM parameter

















For both KEYSTORE_LOCATION  and KEYSTORE_TYPE parameters to take effect you should restart your DB2 instance. While updating KEYSTORE_LOCATION  and KEYSTORE_TYPE ensure you update KEYSTORE_LOCATION first and then KEYSTORE_PATH else you might receive error SQL6112N reason code 16.

db2stop force
db2start

db2stop db2start









Step 5: Encrypt Database

To encrypt existing database you need to take backup of existing database and restore it with encrypt option.

db2 backup db TESTDB compress
db2 drop db TESTDB
db2 restore db TESTDB taken at 20201005122701  ENCRYPT without rolling forward without prompting

Encrypt existing DB2 database

You can also create a new database with encryption enabled.

db2 create db ENCDB encrypt

Create new encrypted db2 database





In DB2 instance for which encryption is configure you can have both encrypted and unencrypted database.

Step 6: Verify Encryption enabled

You can either check Database Configuration (DB CFG)  parameter to check database encryption status or query SYSPROC.ADMIN_GET_ENCRYPTION_INFO() table function to get encryption details.

db2 get db cfg for TESTDB | grep -i ENCRYPT

Verify encyption enable for DB2 database






db2 "SELECT substr(master_key_label,1,40) as MasterKey, substr(KEYSTORE_NAME,1,30) as KeystoreName  FROM TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO())"

SYSPROC.ADMIN_GET_ENCRYPTION_INFO() Table Function












If you liked this blog and interested in knowing more about DB2, please subscribe by clicking on Subscribe to ChoudharySumit.com by Email.

14 comments:

  1. Sumit, Thanks for detailed info. I have the existing db DEV without encryption. Later I enabled the encryption to DEV db.
    I created TEST db without data and encrypted database.

    Can I take the DEV DB Backup and can I restore into TEST db (Redirect Restore).

    Bothe the databases are in same server but in 2 different file systems.

    Thanks in Advance.
    Srikanth Pentyala

    ReplyDelete
    Replies
    1. Hi Srikanth,

      If you are using same KeyStore for encrypting for both database it is possible else you might get error SQL2062N Reason Code 1.

      Since both your source and target database is encrypt do not specify ENCRYPT option with your restore command. Specifying ENCRYPT option will result into SQL1744N error.

      Delete
  2. Hi Sumit, Thanks!

    I have source server without encryption. In the Target server I don't have any database.

    Now I took Normal db backup (DB Size 13GB) from source server and want to restore in target server and want to enable the encryption.

    Guide me high level steps to achieve this.

    Thanks in advance.
    Srikanth Pentyala

    ReplyDelete
    Replies
    1. Follow Step 1 to Step 4 of this blog on your target server. For step 5, take your source database backup and restore it on target server with ENCRYPT option. This will create Encrypted database on your target server from backup of unecrypted database.

      Delete
  3. Hi Sumit
    Thanks for good article.
    Can we use MK for all the databases on the same instance?

    Thanks
    Venkat

    ReplyDelete
    Replies
    1. Hi Venkat,

      Yes you can use same MK (Master Key) to encrypt multiple database in same instance but its advised to use different MK.

      Delete
  4. After the data is encrypted, where will the data reside and in what form of encryption? thanks ad

    ReplyDelete
  5. What is the use of this encryption of DB ? Is it user can see every table data encrypted ?
    Please show with the example.

    ReplyDelete
    Replies
    1. DB2 Native Encryption, encrypt data at rest, that means database containers will be encrypted, database backup image, archive logs will be encrypted. Suppose database is not encrypted and unauthorized person got hold of database backup image, then they can restore it on another system and can access data. If database containers are not encrypted, then unauthorized users can use text sampling commands to looks for specific patterns like credit card number patterns or SSN etc.

      Delete
  6. How we can create encrypted DB database from Keystore created without stash option

    ReplyDelete
    Replies
    1. You should create keystore using stash option

      Delete
    2. Can we encrypt db without taking backup, droping and restoring beacause if DB is huge and already in use it is not possible to drop db

      Delete
    3. If your database is in HADR, you can use rolling mechanism to enable encryption. Check blog https://www.choudharysumit.com/2021/01/encrypt-db2-hadr-database-setup-without.html..if not then you can create encrypted blank database... push data to it using any replication method and then do a cutover

      Delete