Optimizing the MigrateEncryptedInfo utility

MigrateEncryptedInfo is a utility that is used for re-encrypting sensitive data that is stored in the database. It executes two tasks within this utility and each of the tasks is controlled by a separate configuration file.

The MigrateEncryptedInfo script is in the following directory:

utilities_root/bin/MigrateEncryptedInfo.sh

When you open up the MigrateEncryptedInfo script, you can see that there are two calls to MKChangeApp. Each of these calls represents a distinct task that the script performs.

Task one

The first task enforces that the sensitive data in the database is consistent with the PDIEncrypt flag specified in the instance configuration file. If this task detects that the data and the PDIEncrypt flag are out of sync, it migrates the data to the current merchant key. By default, this task is commented out.

If the PDIEncrypt flag value is changed, it means you want to encrypt the data from plain text or decrypt the data to plain text. You can manually enable this part in the MigrateEncryptedInfo script. The default value of PDIEncrypt flag is on.

For example, if the data in the database is in plain text, but you want it to be encrypted, change the PDIEncrypt setting in the instance configuration file to "on". Then, enable this part in MigrateEncrytedInfo script and run this task. Once the PDIEncrypt flag is enabled, any subsequent data that is entered into the database is also encrypted. Alternatively, if the data in the database is encrypted, but you want it to be in plain text, change the PDIEncrypt setting in the instance configuration file to "off". Then, enable this part in MigrateEncryptedInfo script, and run this task to decrypt the existing data. Any subsequent data that is entered into the database is also decrypted.

Linux The following sections of the MigrateEncryptedInfo script need to be enabled:

# echo "#######################" >> $WCLOGDIR/MigrateEncryptedInfoError.log
# echo "### CCInfoMigration ###" >> $WCLOGDIR/MigrateEncryptedInfoError.log
# echo "#######################" >> $WCLOGDIR/MigrateEncryptedInfoError.log
# cp -f CCInfoDBUpdate.$DBTYPEEXT.txt $WCTEMPDIR/DBUpdate.txt
# cd $WCTEMPDIR
# ${JAVA_EXE?} -Xmx512m -DnewKeyNeeded=true -DInstanceXMLFileAccess.debug=true -DReencryptionRequestDispatcher.debug=true -DReencryptionTask.debug=true -Dwclogdir=$WCLOGDIR -cp $CP com.ibm.commerce.dbupdatetool.MKChangeApp "action1_2" $WCINST $param3 $param4 $param5 $param6 $param7 $interactive_output > $WCLOGDIR/CCInfoMigration.log 2>> $WCLOGDIR/MigrateEncryptedInfoError.log
# if [ "$?" = "1" ]
# then
#     echo "Migration failed. Please check $WCLOGDIR/CCInfoMigration.log and $WCLOGDIR/MigrateEncryptedInfoError.log for details."
#     rm -f DBUpdate.txt
#    cd $WCS_HOME/bin
#     exit 1
# fi
# rm -f DBUpdate.txt
# cd $WCS_HOME/bin

This task is controlled by the following configuration file:

utilities_root/bin/CCInfoDBUpdate.DBTYPE.txt

This configuration file contains one action item, called SyncCreditCardsEncryption, which provides an implementation class name to process the data and the database tables that it runs this action against. In the configuration file, you can see that by default it checks the following tables to see whether encryption or decryption is required:

Task two

The second task that is performed by the MigrateEncryptedInfo utility re-encrypts sensitive data in the database with the new merchant key. This task is controlled by the following configuration file:

utilities_root/schema/DBTYPE/migration/DBUpdate.txt

When you examine this configuration file, it contains five action items:

ReEncryptUserPasswords: The first action item, ReEncryptUserPasswords, migrates user passwords (stored in the USERREG table) to the new password scheme (one way hashed), and to the new merchant key. Due to security improvements, if you are migrating the database from a version of HCL Commerce earlier than version 5.4, running the ReEncryptUserPasswords action is mandatory to encrypt the user passwords to the new format. This action also migrates user passwords to a new merchant key.

ReEncryptUserPasswordHistory: The next action item, ReEncryptUserPasswordHistory, migrates old user passwords (stored in the USERPWDHST table) to the new merchant key. If you configured a password policy to store previous passwords, you need to run this action.

ReEncryptCreditCards

The third item in the configuration, ReEncryptCreditCards, re-encrypts credit card data using a new merchant key. This action is required if the PDIEncrypt flag is "on" in the instance configuration file and credit card data needs to be re-encrypted with the new key. The data in the following tables are processed as part of this action:

ReEncryptGiftCenterUserPasswords: Migrates gift center user passwords (stored in the GRUSERAUTH table) to the new merchant key. If you are not using Gift Registry, you do not need to run this action item.

ReEncryptConnectorAttributes

The following attributes in the CSEDITATT table are re-encrypted:

password
AffiliateSiteId
SiteId
sterlingPassword
visualModelerPassword

The following attributes in ISEDITATT table are re-encrypted:

password
basicAuthenticationPassword

Minimizing the running time of the MigrateEncryptedInfo utility by eliminating unnecessary actions

The MigrateEncryptedInfo utility reads database data first before it checks whether re-encryption needs to be performed against the retrieved data for each action. If you know ahead of time certain tasks do not apply to your site configuration, you can customize the script such that these tasks are not performed. Customizing the script saves some time in reading and processing the data. This is especially valuable if you have a large data set.

The following sections describe situations where unnecessary actions can be removed from the utility script.

Note: Certain of these situations are not necessarily recommended (for example, we recommend the use of password history), but are included for completeness.

LDAP is in use as the user repository

If your site uses LDAP as the user repository, the user's current and previous passwords are not kept in the USERREG and USERPWDHST tables. This means that the action items that re-encrypt the user's current and previous passwords become unnecessary.

In this situation, the following action items can be removed from the configuration file of the second task, DBUpdate.txt:

[ActionItem]
ID=ReEncryptUserPasswords
DisplayID=Change Merchant Key
UpdateClassName=com.ibm.commerce.dbupdatetool.OneWayHashEncoder
[DBAccess]
DriverClassName=COM.ibm.db2.jdbc.app.DB2Driver
HostName=
HostJDBCPort=
ConnectionPrefix=jdbc:db2
DatabaseName=
DatabaseUserName=
DatabaseUserPassword=
[DBTable]
TableName=USERREG
TableColumns=USERS_ID,LOGONPASSWORD,SALT,CHALLENGEANSWER
UniqueColumns=USERS_ID
[Parameter]
ID=OldEncryptionKey
DisplayID=OldEncryptionKey
Value=
[Parameter]
ID=NewEncryptionKey
DisplayID=NewEncryptionKey
Value=

[ActionItem]
ID=ReEncryptUserPasswordHistory
DisplayID=Change User Password History
UpdateClassName=com.ibm.commerce.dbupdatetool.DataReEncrypter
[DBAccess]
DriverClassName=COM.ibm.db2.jdbc.app.DB2Driver
HostName=
HostJDBCPort=
ConnectionPrefix=jdbc:db2
DatabaseName=
DatabaseUserName=
DatabaseUserPassword=
[DBTable]
TableName=USERPWDHST
TableColumns=PREVLOGONPASSWORD
UniqueColumns=USERPWDHST_ID
[Parameter]
ID=OldEncryptionKey
DisplayID=OldEncryptionKey
Value=
[Parameter]
ID=NewEncryptionKey
DisplayID=NewEncryptionKey
Value=

Previous passwords (password history) are not stored in the database

If you do not configure your password policy to store the user's previous passwords, then you can disable the ReEncryptUserPasswordHistory action that processes the data in the USERPWDHST table.

Remove the following action item from the DBUpdate.txt configuration file:

[ActionItem]
ID=ReEncryptUserPasswordHistory
DisplayID=Change User Password History
UpdateClassName=com.ibm.commerce.dbupdatetool.DataReEncrypter
[DBAccess]
DriverClassName=COM.ibm.db2.jdbc.app.DB2Driver
HostName=
HostJDBCPort=
ConnectionPrefix=jdbc:db2
DatabaseName=
DatabaseUserName=
DatabaseUserPassword=
[DBTable]
TableName=USERPWDHST
TableColumns=PREVLOGONPASSWORD
UniqueColumns=USERPWDHST_ID
[Parameter]
ID=OldEncryptionKey
DisplayID=OldEncryptionKey
Value=
[Parameter]
ID=NewEncryptionKey
DisplayID=NewEncryptionKey
Value=

The PDIEncrypt flag is set to "off"

If credit card data is not encrypted or not stored in the HCL Commerce database, the highlighted action item that re-encrypts the data in PATTRVALUE, ORDPAYINFO and ORDPAYMTHD tables can be removed from the DBUpdate.txt configuration file.

[ActionItem]
ID=ReEncryptCreditCards
DisplayID=Re encrypt the credit cards
UpdateClassName=com.ibm.commerce.dbupdatetool.CCInfoReencoder
[DBAccess]
DriverClassName=COM.ibm.db2.jdbc.app.DB2Driver
HostName=
HostJDBCPort=
ConnectionPrefix=jdbc:db2
DatabaseName=
DatabaseUserName=
DatabaseUserPassword=
[DBTable]
TableName=PATTRVALUE
TableColumns=STRINGVALUE,ENCRYPTFLAG
UniqueColumns=PATTRVALUE_ID
[DBTable]
TableName=ORDPAYINFO
TableColumns=VALUE
UniqueColumns=ORDPAYINFO_ID
[DBTable]
TableName=ORDPAYMTHD
TableColumns=PAYDEVICE
UniqueColumns=ORDERS_ID,PAYMETHOD,PAYDEVICE,REFUNDNUMBER
[DBTable]
TableName=PPCEXTDATA
TableColumns=DATAVALUE,ENCRYPTED
UniqueColumns=PPCEXTDATA_ID
[DBTable]
TableName=PPCPAYINST
TableColumns=ACCOUNTNUMBER
UniqueColumns=PPCPAYINST_ID
[DBTable]
TableName=MERCHCONFINFO
TableColumns=PROPERTY_VALUE,ENCRYPTED
UniqueColumns=MERCHCONFINFO_ID
[Parameter]
ID=OldEncryptionKey
DisplayID=OldEncryptionKey
Value=
[Parameter]
ID=NewEncryptionKey
DisplayID=NewEncryptionKey
Value=