5 replies to this topic

[Ross Bevin]

Hi everyone,

Does anyone have a solution for handling encrypted SQL Server data e.g. a credit card number? I already have a solution to encrypt it within ProIV but our CIO wants me to find him a purely SQL solution as well. The encryption has to be independent of ProIV so that non ProIV applications have access to the encrypted data as well.

E.g. the data is encrypted in the SQL table. I read that data into ProIV and some way decrypt it for use in my function. When I write the credit card number back to SQL, SQL encrypts it.


Thanks

Ross

[mikelandl]

I've never done this and it might not be the most elegant solution but could you write an SSO where you pass in the connection information to the database and the SQL statement. The SSO would then connect to the database, issue the SQL statement, retrieve the results, decrypt the the results and return them back to ProIV?

[Ross Bevin]

Hi Mike,

Yes, I think an SSO is the way to go but bypassing SQL all together. Wim Soutendijk responded to a post on the ProIV Forum website. He has developed an SSO that encrypts/decrypts a string based on a fixed encryption key. The encrypted data is written to SQL as a varchar. I'm currently working with a Java developer to enhance it so that instead of the fixed encryption key, we pass it the key. This way users have complete control over their encrypted data with the developer not having access. I will let you know how I make out with this solution.

Regards

Ross

[Lewis Mccabe]

Hi Ross,

I might have a need for that SSO as well.

Lew

[DARREN]

We did something similar with passwords where we had to store them as MD5 hashed values. Our DBA's created us a SQL function where we pass it a value and it returns a hashed value from that. I have included the SQL function but for obvious reasons have not included our 'salt' value.

// Function created to encrypt passwords via call to Oracle
//
// The dummy (SELECT '1' FROM DUAL WHERE 1 = 1) part of the statement is required to workaround the
// fact that ProIV will assume an implied WHERE statement if it sees a binded variable before
// locating a WHERE command and force the WHERE command just before the binded variable.

SQL
SELECT PASSWORD_HASHED, (SELECT '1' FROM DUAL WHERE 1 = 1)
FROM (SELECT SECUADMIN.HASH_VAL(:$PASSWORD) AS PASSWORD_HASHED FROM DUAL)
ENDSQL

---------------------------------------
SQL Function


CREATE OR REPLACE function SECUADMIN.hash_val (p_in_val in varchar2)--, p_key in varchar2)
return varchar2
is
l_enc_val raw(2000);
begin
l_enc_val :=
DBMS_CRYPTO.hash ( src => utl_i18n.string_to_raw (concat(p_in_val , 'salt value'),
'AL32UTF8'
),
typ => DBMS_CRYPTO.hash_md5
);
RETURN lower(l_enc_val);
End;
/

[Ross Bevin]

Hi Darren,

Thanks for posting your solution; much appreciated.

Regards
Ross