topic Re: Changing database connectivity in batchs in Talend Studio

Changing database connectivity in batchs

Anonymous — Fri, 10 Jul 2015 09:43:02 GMT

Hi,

How can I change the encrypted database password in the property file associated with the batch file that runs a specific Job whenever the database password has changed. Do I have to regenerate the batch file again through open studio? is there a way that this can be done without the need of open studio since the batch is executed at my client environment and they don't have open studio.

Thanx.

Re: Changing database connectivity in batchs

Anonymous — Fri, 10 Jul 2015 10:09:29 GMT

Take a look at this tutorial (https://help.talend.com/search/all?query=tContextLoad). You should be using context variables in your job to provide information like passwords. You can get these context variable values at runtime from flat files or from a database. There is no need to keep recompiling jobs.

Re: Changing database connectivity in batchs

Anonymous — Fri, 10 Jul 2015 12:47:17 GMT

Thanx for the feedback.
This method will allow me change the connectiona variables dynamically whenever I need to change the database ones without the need to recompile the batches. However I have another issue related to security where those values are stored in clear text in the connection files. What is the solution for this securty vulnerability?

Re: Changing database connectivity in batchs

Anonymous — Mon, 13 Jul 2015 15:01:53 GMT

Using the context component I can store dabase connection parameters in default.properties files however for the database password if I specify the type of context parameter as password thne the password will be encrypted in the default.propreties file and i won't be able to change it from teh file itself. which forces me recompile the job again. Is there any external tool or an interface that i change my password and it will be stored as encrypted in the properties file?

Re: Changing database connectivity in batchs

Anonymous — Mon, 13 Jul 2015 15:32:49 GMT

Ah I see. There is a workaround to this. You can create a routine that you use for all of your jobs to encrypt/decrypt your passwords (or use an existing library). Keep the key in a context group that is used for all jobs. Ensure that it is set as a password. Now, you can use this functionality to decrypt all of your passwords within the jobs, using a key that will never need changing (unless it is lost or compromised). In which case a recompile will be necessary.

To encrypt your passwords you can create a job that uses this functionality and the hidden to key to encrypt the passwords which you can then store in flat files as encrypted text. People with access to this file will not be able to decrypt the passwords unless they have access to the function/algorithm and the encrypt/decryption password.

Although not a perfect solution, this does allow you to not have to recompile your jobs whenever a password expires.

Re: Changing database connectivity in batchs

Anonymous — Tue, 14 Jul 2015 07:27:18 GMT

Is there any tutorial or a sample code that demonstrates this solution?

Thanx

Re: Changing database connectivity in batchs

Anonymous — Tue, 14 Jul 2015 09:46:10 GMT

Not that I am aware of, although it is a good idea for one. However I feel by the time I get round to doing one it would be too late for you.
So, hopefully these steps will help you....

1) Create a routine (a Talend Java class) with two static methods; 1 to encrypt and 1 to decrypt. You can use libraries (Jar files) for this, but it must not be one way encryption.
2) Create a context group which contains your encryption/decryption key. Have this stored as type "password".
3) Create a job that takes a clear text/cipher text input and can convert it to cipher text/clear text using your encryption key. This can be used for when you need to update your passwords in your properties files.
4) In your Jobs that will use the encrypted passwords, use the routine to decrypt the context variable holding the encrypted password using the hidden encryption key.

As I said before, this is not fool proof. You will need to manage access to the Job used for encryption/decryption and it won't stop Talend developers from being able to code a solution to find the passwords if they really want to. However, that would be pretty hard to stop to be honest. But it will mean that you can store your encrypted passwords in a text file which other people can see, without the fear of them being able to use that data to access your databases.

Re: Changing database connectivity in batchs

Anonymous — Tue, 21 Jul 2015 09:13:45 GMT

Pls I need more help on steps 3 and 4:

In step 3, how to create this job what kind of components to be used and how to link them? will the clear password text be read from a file and how to write the encrypted text back in teh properties file?
In Step 4, where to call teh decryption method and how to pass parameters to it?

Thanx

Re: Changing database connectivity in batchs

Anonymous — Tue, 21 Jul 2015 09:28:40 GMT

Lets assume that you have a Java method which can be used to encrypt/decrypt your passwords with a signature like below...

public static String encryptDecryptWord(boolean encrypt, String word, String key){
      //Do stuff

       return value;
}

For Step 3 you create a job that receives a value via a context variable. This job will use the method above to encrypt the value and output it to the terminal or to a file, you choose. This value is what you store in your parameter file as the encrypted database password.

For Step 4, whenever you need to use that encrypted value this will be the process. The file holding your context values will be read into the job as normal. Any password values stored as context variables will then be processed by calling the method above with the encrypted value, the boolean set to false (to decrypt) and the key (stored in your context group as a hidden password that nobody knows). So for example, if you have a context variable called password, you do something like below....

context.password = routines.MyEncryptionStuff.encryptDecryptWord(false, context.password, context.encryption_key);

The "routines.MyEncryptionStuff" is the path to the method above assuming your routine is called "MyEncryptionStuff". The context.encryption_key is your context variable holding the hidden encryption key.

This will (for the lifetime of the job and only within the job) decrypt your encrypted password so that it can be used.

Re: Changing database connectivity in batchs

_AnonymousUser — Mon, 21 Mar 2016 08:25:19 GMT

In this way, if someone has the access of key file, so he can easily get the password. So what should we have to do for that. I am also trying the same thing. I am done with the coding of encryption/decryption, first time the DB Admin will run my utility jar to encrypt the password and then in Java Batch job I will include the code for decryption.

Re: Changing database connectivity in batchs

Anonymous — Mon, 21 Mar 2016 08:45:52 GMT

If someone has access to the key, the encryption/decryption algorithm and the context file containing the encrypted password, you have bigger issues with security than worrying about this.