I do have an index on the unencrypted SENSITIVE_DATA. >> Does it look something like >> select * >> from original_table >> where decrypt(sensitive_data) = 'CLEAR TEXT' Yes but the call to decrypt(sensitive_data) is embedded in the view.
An index on the function means the unencrypted sensitive data is still in the index. -----Original Message----- Sent: Monday, September 09, 2002 6:04 PM To: Multiple recipients of list ORACLE-L Subject: Re: using obfuscation Randy, Do you have an index on SENSITIVE_DATA? Also, you didn't include the long running query. Does it look something like select * from original_table where decrypt(sensitive_data) = 'CLEAR TEXT' ? That requires a full table scan, unless you build a functional index with the clear text of the sensitive_data. But if you do that, your data won't really be secure, as the clear text will now be in an index. Jared "Steiner, Randy" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 09/09/2002 02:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: using obfuscation Hi all, I have downloaded the Metalink Notes on implementing dbms_obfuscation. I am using multiple front ends on the database, so the way I plan to implement the de-encryption is with a de-encrypt function in a view. Create View my_data AS Select de_encrypt(sensitive_data) AS sensitive_data ,other_data FROM original_table ; If I select from the view with a where clause on other_data, the response time is fine. If I select from the view with a where clause on sensitive_data, I do a full table scan and which takes about 15 minutes. The de-encrypt function is copied from a Metalink note, nothing fancy. Since I have various front ends, I can not de-encrypt the data in the front end. The only way I can think of is with the function in a view, but the response time is unacceptable. Does anyone have any thoughts on this? Thanks Randy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steiner, Randy INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steiner, Randy INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).