Script using encrypt and decrypt.

[Indira Aramandla]

Hi,

I need your assistance in this.

We have a table with passwords that are encrypted.
Now I have to send this tbale values in an ascii file to a target location. As the source and target rdbms are different, I will have to write a script to decrypt the values that I extract then encrypt tehm again using encrypt function with a key and send the key to the target and they decrypt with decrypt function. I know using crypt, but I want a solution using encrypt and decrypt.


Thanks
I A

[Peter Godron]

Hi,
I have attached the function definitions we use. Please change the KEYPHRASE. You can modify to :
select decrypt(pwd_col) from pwd_table
Then use the cleartext to encrypt on the other side:
update pwd_table set pwd_col = encrypt(pwd);

Hope this helps as an oracle solution.

[Yogeeraj_1]

hi Indira,

can you please check this one?

create or replace function crypt( p_str in varchar2 )
return raw
as
l_data varchar2(255);
begin
l_data := rpad( p_str, (trunc(length(p_str)/8)+1)*8, chr(0) );
return dbms_obfuscation_toolkit.DESEncrypt
( input => utl_raw.cast_to_raw(l_data),
key => utl_raw.cast_to_raw('MagicKey-Yogeeraj') );
end;
/

create or replace function decrypt( p_str in raw ) return varchar2
as
begin
return utl_raw.cast_to_varchar2(
dbms_obfuscation_toolkit.DESdecrypt
( input => p_str,
key => utl_raw.cast_to_raw('MagicKey-Yogeeraj') ) );
end;
/

Below an example on how you should go about in retrieving the real data:

column data format a10

yd@MYDB.MU> column data format a10

yd@MYDB.MU> create table t ( data varchar2(9), data_enc raw(16) );

Table created.

Elapsed: 00:00:03.12
yd@MYDB.MU> yd@PFS.CMT.MU>
yd@MYDB.MU> insert into t values ( '012345678',crypt('012345678'));


1 row created.

Elapsed: 00:00:00.20
yd@MYDB.MU> commit;


Commit complete.

Elapsed: 00:00:01.50
yd@MYDB.MU> select data,
data_enc,
decrypt(data_enc) data
from t;
2 3 4
DATA DATA_ENC DATA
__________ ________________________________ __________
012345678 07CF8213EDC46C277C720164D915B70C 012345678

Elapsed: 00:00:00.07
yd@MYDB.MU>


hope this helps too!

kind regards
yogeeraj

[Indira Aramandla]

Hi Yogeeraj and Peter,

Thanks for your solutions. But I want a shell script or a C program or executable.

Because the target site does not use Oracle, their RDBMS is sybase.

I am sorry for not being clear earlier.

This is what we have. We have a table in Oracle with encrypted passwords. Now I have to extract the passwords, decrypt them into an ascii file. Then I need a C executable or a shell script to read this ascii file encrypt the password, and write back to the same file, then I will have to send the file and the executable / shell script to the target site and they will de-crypt and load into their database.


Thanks
IA

[Peter Godron]

Indira,
once you have the passwords extracted into the ascii file,what is the problem with the standard tools:
crypt,password protected zip and Public key ?

[Yogeeraj_1 · Accepted solution]

Hi Indira,

you may also look into HP-UX Secure Shell. The client-server architecture supports the SSH-1 and SSH-2 protocols and provides secured remote login, file transfer, and remote command execution.

http://h20293.www2.hp.com/cgi-bin/swdepot_parser.cgi/cgi/displayProductInfo.pl?productNumber=T1471AA

also have a look at Chris Vail's document "Using H/P's Secure Shell & Secure Copy" (attached)

kind regards
yogeeraj

[Indira Aramandla]

Peter,

When I extract the passwords into the ascii file, first I will decrypt the password from our encryption procedure, then before writing into the ascii file, I want to encrypt using a key and then write into the ascii file.

the I will gzip the file and use the UNXI crypt to encrypt the complete file.

At the target site, they will decrypt rthe file using the key we prove, then gunzip the file and when they read the contents of the file, they will use the same code as we did to encrypt the individual passwords, they will decrypt and then store into their database using their encryption rules.

This sound all too many encryptions and decryptions, but the two orginations that are in the process of mergining do not want to give their encryption/decryption procedures to either. Instead decrypt from the source, then use a common code that is applicable to source and target sites, encrypt at source end and they will decrypt at the target end and then apply their own encryption rules.


Indira A