Encrypt column/s before loading to redshift

[desanip]

Good Morning everyone,

I have a use case where i want to encrypt my database column value before loading into my target database. Does talend have any component/s that support this functionality? Did anyone do this in talend before? I have been reading about ROT 13 algorithm. But it looks like its not secure enough. Can anyone help me with how i can achieve this using Talend?

[TRF]

This may helps https://community.talend.com/t5/Design-and-Development/Encryption-decryption-using-Talend/td-p/89195.

Depending on the engine you should consider using the encryption capacity of your database especially if you need to query these informations later on with filtering. If you encrypt informations before they are inserted in the database it could be quite difficult or impossible to access them from outside your jobs.

[Anonymous]

@desanip 

First you need to create your routine. An example of a routine for encryption can be seen below 

package routines;
import java.security.InvalidKeyException;
import java.security.Key;
import java.security.NoSuchAlgorithmException;
import java.security.SignatureException;
import java.util.Formatter;
import javax.crypto.Mac;
import javax.crypto.spec.SecretKeySpec;

public class EncryptionUtilities {
	
	private static final String HMAC_SHA1_ALGORITHM = "HmacSHA1";
	 
	private static String toHexString(byte[] bytes) {
		Formatter formatter = new Formatter();
		
		for (byte b : bytes) {
			formatter.format("%02x", b);
		}
 
		return formatter.toString();
	}
 
	public static String calculateRFC2104HMAC(String data, String key)
		throws SignatureException, NoSuchAlgorithmException, InvalidKeyException
	{
		SecretKeySpec signingKey = new SecretKeySpec(key.getBytes(), HMAC_SHA1_ALGORITHM);
		Mac mac = Mac.getInstance(HMAC_SHA1_ALGORITHM);
		mac.init(signingKey);
		return toHexString(mac.doFinal(data.getBytes()));
	}
 
}

To call this in the tMap you simply use the code.....

routines.EncryptionUtilities.calculateRFC2104HMAC(row1.data, "myKey")

....in the column you want to encrypt.

Just an example of how to do this. Change accordingly.

Thanks,

Prabuj

/ Don't forget to give kudos /

[TRF · Accepted solution]

I didn't saw your db name when I posted my 1rst answer.

Anyway, you should definitely consider encryption on AWS/Redshift side: 

"Ensure that your Amazon Redshift clusters are encrypted in order to meet security and compliance requirements. The Redshift clusters data encryption/decryption is handled transparently by AWS and does not require any additional action from you or your application."

This way you will be able to continue to query informations stored in the database with standard functionalities where it will not be possible anymore if you proceed to encryption by yourself on Talend side.

[desanip]

Thanks for the insights everyone.