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_PATH, LIBPATH, SHLIB_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
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
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
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
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
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
You can also create a new database with encryption enabled.
db2 create db ENCDB encrypt
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
db2 "SELECT substr(master_key_label,1,40) as MasterKey, substr(KEYSTORE_NAME,1,30) as KeystoreName FROM TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO())"
Sumit, Thanks for detailed info. I have the existing db DEV without encryption. Later I enabled the encryption to DEV db.
ReplyDeleteI 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
Hi Srikanth,
DeleteIf 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.
Hi Sumit, Thanks!
ReplyDeleteI 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
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.
DeleteHi Sumit
ReplyDeleteThanks for good article.
Can we use MK for all the databases on the same instance?
Thanks
Venkat
Hi Venkat,
DeleteYes you can use same MK (Master Key) to encrypt multiple database in same instance but its advised to use different MK.
After the data is encrypted, where will the data reside and in what form of encryption? thanks ad
ReplyDeletedata will reside in database only.
DeleteWhat is the use of this encryption of DB ? Is it user can see every table data encrypted ?
ReplyDeletePlease show with the example.
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.
DeleteHow we can create encrypted DB database from Keystore created without stash option
ReplyDeleteYou should create keystore using stash option
DeleteCan 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
DeleteIf 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