DSXchange

Posted: **Mon Jun 07, 2010 1:01 pm**

Ah... tend to forget about them darn things, not having had much of a chance to play with them anywhere.

Posted: **Mon Jun 07, 2010 2:33 pm**

karry450 wrote: I want the first original password to be converted into encrypted and then use this encrypted as a password for the jobs for connecting to database by assigning to a parameter.

does this work If I use convert()?

Yes, but you can not provide already encrypted parameter values to a job - it must be decrypted before the job run request is issued.

Posted: **Tue Jun 08, 2010 12:35 am**

If you are loooking for some database encryption and decryption package then I may suggest you some clues,i think you can use this SP from backend and try to achieve what you are looking for

this SP is based on oracle database ,
Let me know which database you are looking for you may have to convert /construct my SP to it's equivalent databse which you are working

Should I provide my SP?

Posted: **Tue Jun 08, 2010 4:30 am**

asorrell wrote:Try using parameter sets.

HI Sorrell,

Is it possible to encrypt a password and use the encrypted password as input parameter for db connection without decrypting it in DS 8.1,

The scenario is our testing team should use the encrypted password only in the param file as input for the job for connecting to DB. I dont want them to know abt the original password of DB.

This is possible in DS 7.5 server when you double click on the job in director it straight away shows you the encrypted password which is not avaiable in DS 8.1 server.

Can anyone please help me on this.

Thanks

Posted: **Tue Jun 08, 2010 4:38 am**

qutesanju wrote:If you are loooking for some database encryption and decryption package then I may suggest you some clues,i think you can use this SP from backend and try to achieve what you are looking for

this SP is based on oracle database ,
Let me know which database you are looking for you may have to convert /construct my SP to it's equivalent databse which you are working

Should I provide my SP?

Hi Sanju,

That would be great If you can send me your SP and may be I can get anything out of it.

Thanks

Posted: **Wed Jun 09, 2010 8:17 am**

If you choose to employ your own method, make sure that you add a dash of salt.

For example, you could choose to input encrypted string pairs wherein for each encrypted string you pass in the encrypted string as just a string variable and you also pass the decrypted string as an encrypted string.

Here are some scripts and examples on how you could use such a process (for a limited number of characters since BASE 64 is employed, carriage returns you know):

Code: Select all

$ cat openssl_encrypt_string.ksh
#!/bin/ksh

## NOTE:: USE openssl TO ENCRYPT A SPECIFIED STRING
## Encrypt specified string 
echo "${1}\c" | /usr/linux/bin/openssl enc -a -aes-256-cbc -salt -pass pass:encryption

Code: Select all

$ cat openssl_decrypt_string.ksh
#!/bin/ksh

## NOTE:: USE openssl TO DECRYPT A SPECIFIED STRING
## Decrypt the specified string 
cat <&0 | /usr/linux/bin/openssl enc -d -aes-256-cbc -salt -a -pass pass:encryption

Code: Select all

$ cat stuff.txt
DB2_USERNAME=abc
DB2_PASSWORD=U2FsdGVkX19s8ik1Y+X6mXq30/PkJT8zOKBkhhf5MYs=
DECRYPT_DB2_PASSWORD=U2FsdGVkX19s8ik1Y+X6mXq30/PkJT8zOKBkhhf5MYs=
ORA_USERNAME=abc
ORA_PASSWORD2=U2FsdGVkX1+B5VwxLZAY7AKo59hlYnOFgvNumQ0A6K4=
DECRYPT_ORA_PASSWORD2=U2FsdGVkX1+B5VwxLZAY7AKo59hlYnOFgvNumQ0A6K4=

Code: Select all

$ cat stuff.ksh
#!/bin/ksh

trap "rm -f /tmp/*.$$ >/dev/null 2>&1" EXIT INT TERM QUIT

umask 077

## CREATE PARM FILE
PFILE="/tmp/parmsin.$$"
>${PFILE}

## TESTING OPENSSL CAPABILITIES
for VALUE in $(<stuff.txt)
do
   if [[ "${VALUE}" = DECRYPT* ]];then
      echo "${VALUE}" | awk '{print substr($0, 1+index($0, "="))}' | openssl_decrypt_string.ksh | read V_PASS 
      echo "${VALUE}" | awk -vV_PASS=${V_PASS} '{print substr($0, 1, index($0, "="))V_PASS}' >>${PFILE}
   else
      echo "${VALUE}" >>${PFILE}
   fi
done

## INVOKE dsjob here and pass the parameter file that which was built
cat ${PFILE}

exit 0

OUTPUT:

Code: Select all

$ stuff.ksh
DB2_USERNAME=abc
DB2_PASSWORD=U2FsdGVkX19s8ik1Y+X6mXq30/PkJT8zOKBkhhf5MYs=
DECRYPT_DB2_PASSWORD=123
ORA_USERNAME=abc
ORA_PASSWORD2=U2FsdGVkX1+B5VwxLZAY7AKo59hlYnOFgvNumQ0A6K4=
DECRYPT_ORA_PASSWORD2=abc

So, in the above example, DECRYPT_DB2_PASSWORD and DECRYPT_ORA_PASSWORD2 would be passed into the job as encrypted strings and DB2_PASSWORD and ORA_PASSWORD2 would be passed in as simply strings so that you could use them later on.

I am not sure why you would want to do such a thing when you can use parameter sets to do much of the same thing. Albeit through a change in process, that process is much more secure.

Thanks.

Posted: **Wed Jun 09, 2010 9:58 am**

Is it possible to encrypt a password and use the encrypted password as input parameter for db connection without decrypting it in DS 8.1?

Yes! That is the whole idea of using parameter sets with encrypted passwords in release 8!

The administrator sets up the parameter sets and inputs the encrypted passwords. Later, operators or testers just run the jobs/sequences and leave the connection information alone. It is submitted to the database at runtime and automatically decrypted.

Just make sure and setup different value files for each of your environments (Dev, Test, QA, Prod, etc.). The operators can select the correct one when they run or schedule the job.

I've not seen a need for any other type of encryption if it is done properly.

Posted: **Thu Jun 10, 2010 7:50 am**

If you have passwords stored in jobs, then my DataStage Analysis spreadsheet can pull them out of a DSX export. See the thread in the General forum, scroll to the last message for latest version link. If you find the right bit of VBA, you could use it as a generic password decoder. P.S. if you care about those passwords you posted, edit your post and remove them because anyone can decode them.

Posted: **Fri Feb 18, 2011 5:18 am**

I've been thinking about DataStage password encryption, and I've decided that it was a catastrophically bad idea and deserved to be cracked.

It's a bad idea because it gave the illusion of security. You put a password into a job, it gets masked as ******** in the user interface, and you look in the DSX, and it's scrambled. This leads you to think that you are safe to put your secret passwords into DataStage jobs. However, DataStage has all the information needed to decode that password with no other input required, even if it was public key encrypted then the DataStage software would have to hold the private key to decrypt it and that can be debugged and hacked. It's the same as DVD encryption - the consumer has everything needed to decrypt a DVD, it's all in that box under the telly, all you need to do is pull the box apart and see how it works.

So, I don't feel bad at all about my DataStage Analysis spreadsheet having the capability to decode passwords, although I will keep it as an option so that those fooling themselves can continue to do so.

I did a training course recently on Oracle Data Integrator. It has a similar mechanism, you put a password in and it gets encoded, but the ODI software can decode it which means anyone can decode it. I've searched around to see if anyone's cracked it already, but I can't find anything. I don't have a functioning installation right now so I can't really have a go myself. But I will not be happy until I know how to crack it, so that I can dispel the illusion of security.

*Update*: The actual reason I first wrote the code to decode encrypted strings was to count parameter usage. If your job has a $PASS parameter, and you put $PASS into a password input in a stage, then $PASS gets encoded. I wanted to count how many times $PASS was referenced in the job. I didn't actually want to decode passwords, I wanted to decode the expression to see if it contained the parameter.

Posted: **Fri Feb 18, 2011 10:30 am**

I am not following that logic. I think it should be removed.

Posted: **Mon Feb 21, 2011 3:42 am**

Ultramundane wrote:I am not following that logic. I think it should be removed.

You are welcome to remove it from your copy, it's all in the DSXDecode function. All you will lose is the ability to see in how many places your encrypted parameters are used in a job. But if you're worried that other people can now decode your passwords, well, they always could since it's trivial and the information was already out there on the net before I came along.

Posted: **Mon Jul 18, 2011 1:23 am**

ray.wurlod wrote:
Code: Select all
"ZZZYZXZWZV"[Convert("ABCDE","12345")*2-1,2]
would do the trick for two-character substitution.

But it throws the below syntax error:

Code: Select all

Compiling: Source = 'DSU_BP/DSU.Encrypt', Object = 'DSU_BP.O/DSU.Encrypt'

0002    Ans="ZZZYZXZWZV"[Convert("ABCDE","12345")*2-1,2]

                                              ^
')' unexpected, Was expecting: '!', ',', '=', "AND", "OR", "LT", "LE", 
"GT", "GE", "NE", "EQ", "MATCH"

1 Errors detected, No Object Code Produced.

Posted: **Mon Jul 18, 2011 4:15 am**

But i am not able to understand the purpose of the below:

Code: Select all

"ZZZYZXZWZV"[Convert("ABCDE","12345",Arg1)*2-1,2]

Probably if Arg1 is ABCDE the above code will return an empty string.
because 12345*2-1 will give 24689 which we can't find in ZZZYZXZWZV.

Can anyone explain this?

thanks

DSXchange

Password Encryption.