Database field encryption and Decryption

[RP_wpb]

Hello Folks:
I have few question on database field level encryption and decryption.

Those of you who were using (or currently using) progress 9.x version, did you have a scenario where you store sensitive information (say SSN) value as encrypted in database but should be available for viewing (ie the original value not the encrypted one) to certain end users through the application. how did you achieve this? afaik, progress 9.x version has encoding function which is 'one-way' ie we cannot decode to get the original value for viewing. Did you use any custom encrypt/decrypt logic? or any 3rd party tool?

Appreciate if somebody can share their experience on this.

we currently use 9.1E , but going to get upgraded to OE 10.2B in 2-3 months.
I read that it offers encrypt and decrypt options.

Thanks,
Raghu

 

[TomBascom]

With v9 you have to roll your own using an external encryption routine.

I'd work on getting that upgrade completed ASAP.

 

[tamhas]

I would think very hard about your priorities. Anything you do on 9 will be kludgy and something that you would want to rip out and replace when you get to 10.2B. So, is the need urgent and it has to be done right now and there is no chance of advancing the conversion, then you have no choice but to do something kludgy. But, if you can delay or advance the conversion, it is clearly better to do it after.

 

[RP_wpb]

Tom / Thomas - Thanks for your feedback. couldnt revert earlier, sorry about that.

we have decided to try encryption/decryption with OE.
I am able to encrypt /decrypt within OE but when I export the encrypted content (into a file) and try to decrypt using Pl/sql or java, it is failing.
Details below:-
algorithim used : AES_CBC_128
just thought of a 16 byte char key -> converted this to raw -> set up the security-policy attributes -> encrypted the text - > encoded the encrypted text through base64encode -> exported the encoded text (through output to statement).
however, when we try to decrypt the value in pl/sql or java using the original key, we are not able to decrypt successfully to its original value.
have I overlooked anything while exporting the encoded content..?

thanks in advance for any feedback.
Regards,
Raghu.

 

[TomBascom]

You need to post some sample code.

 

[TomBascom]

/* crypt.p
 *
 */

define variable clearText   as character no-undo initial "Top Secret".
define variable secretKey   as character no-undo format "x(30)".
define variable pbeKey      as raw no-undo.
define variable cryptBin1   as memptr no-undo.
define variable cryptBin2   as memptr no-undo.
define variable decryptBin  as memptr no-undo.
define variable decryptText as character no-undo format "x(30)".

update secretKey.

pbeKey = generate-pbe-key( secretKey ).

cryptBin1 = encrypt( clearText, pbeKey ).

output to value( "secret.bin" ) binary no-convert.
export cryptBin1.
output close.

file-info:file-name = "secret.bin".
set-size( cryptBin2 ) = file-info:file-size.

input from value( "secret.bin" ) binary no-convert.
import unformatted cryptBin2.
input close.

decryptBin = decrypt( cryptBin2, pbeKey ).

decryptText = get-string( decryptBin, 1 ).

display decryptText.

return.

 

[RP_wpb]

Thanks Tom.
I had originally tried the code which is in KB P11878.
In your example, the key is being generated with PBE. In the KB it is through 'Generate-random-key'
Using both the options, I can encrypt and decrypt succcessfully within OE sessions. But still not able to do if I try to do with PL/SQL procedure or in JAVA, when I import the key and encrypted content (from OE session) and decrypt it.
I am going to take another look at it.
I did notice when I export the encrypted content (key being generated through PBE as in your example) the content is unreadable , however when i encrypt after generating the key through 'Generate-Random-key' or generating the key after converting the character key to raw , the exported encrypted content is readable eg - "020010M&UHmvr&WN6J6g0sCZUoXg==" . Has PBE got anything to do with this? in all cases, i used binary no-convert while exporting.

 

[TomBascom]

I cannot comment meaningfully upon your reported results without seeing your code.