RE: using obfuscation
Title: RE: using obfuscation
I was wondering if this was of any help.
Tony Aponte
-Original Message-
From: Aponte, Tony
Sent: Tuesday, September 17, 2002 7:04 PM
To: '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
Subject: RE: using obfuscation
I've been developing a solution for a similar requirement. Although I reached a dead-end with this thread I think it solves your problem.
I'm picking it up from the point where the data in encrypted_data of sensitive_table needs encryption. I did that with an anonymous PL/SQL block:
CREATE TABLE sensitive_table (encrypted_data VARCHAR2(30), clear_text VARCHAR2(30));
I'll populate the table with text 16 characters long. I used 16 to simplify the example. DBMS_OBFUSCATION_TOOLKIT needs the data length to be a multiple of 8:
INSERT INTO sensitive_table
SELECT TO_CHAR(ROWNUM,'fm0009'),TO_CHAR(ROWNUM,'fm0009') FROM DBA_OBJECTS WHERE ROWNUM 1000;
COMMIT;
Now run the anonymous block to encrypt the data in the encrypted_data column:
--Encrypt data
DECLARE
CURSOR xtab IS SELECT encrypted_data FROM sensitive_table FOR UPDATE;
input_string VARCHAR2(16) ;
raw_input RAW(400) ;
key_string VARCHAR2(8):= 'abcde123';
raw_key RAW(400) := UTL_RAW.CAST_TO_RAW(key_string);
encrypted_raw RAW(2048);
error_in_input_buffer_length EXCEPTION;
PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
'*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING EXCEPTION ***';
double_encrypt_not_permitted EXCEPTION;
PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233);
DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=
'*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';
BEGIN
FOR xrec IN xtab LOOP
input_string:=xrec.encrypted_data;
raw_input:= UTL_RAW.CAST_TO_RAW(input_string);
DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(input = raw_input,key = raw_key, encrypted_data = encrypted_raw );
UPDATE sensitive_table
SET encrypted_data = UTL_RAW.CAST_TO_VARCHAR2(encrypted_raw)
WHERE CURRENT OF xtab;
END LOOP;
COMMIT;
EXCEPTION
WHEN error_in_input_buffer_length THEN
dbms_output.put_line(' ' || INPUT_BUFFER_LENGTH_ERR_MSG);
END;
/
I want to create a package (spec only) to contain global variables to be used by my encrypt/decrypt functions:
CREATE OR REPLACE PACKAGE cc_security_pkg
IS
g_KeyString_txt VARCHAR2(8):= 'abcde123';
g_Key_raw RAW(400) := UTL_RAW.CAST_TO_RAW(g_KeyString_txt);
g_Input_raw RAW(400) ;
g_Decrypted_raw RAW(2048);
g_Encrypted_raw RAW(2048);
g_ErrorInInputBufferLength_exc EXCEPTION;
PRAGMA EXCEPTION_INIT(g_ErrorInInputBufferLength_exc, -28232);
g_InputBufferLengthErrMsg_txt VARCHAR2(100) :=
'*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING EXCEPTION ***';
g_DoubleEncrypt_exc EXCEPTION;
PRAGMA EXCEPTION_INIT(g_DoubleEncrypt_exc, -28233);
g_DoubleEncryptionErrMsg_txt VARCHAR2(100) :=
'*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';
--
END;
/
Here is the decrypt function. Not much to explain:
CREATE OR REPLACE cc_decrypt(p_Input_txt VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(INPUT = UTL_RAW.CAST_TO_RAW(p_Input_txt)
,KEY = cc_security_pkg.g_Key_raw
,DECRYPTED_DATA = cc_security_pkg.g_Decrypted_raw );
RETURN(UTL_RAW.CAST_TO_VARCHAR2(cc_security_pkg.g_Decrypted_raw));
END cc_decrypt;
/
This is the corresponding encryption function. Two things to note, the use of the DETERMINISTIC pragma and the conflicting use of the value of CLIENT_INFO to determine the return value (encrypted or not.) I am deliberately (mis?)using DETERMINISTIC to trick Oracle into trusting that I will always return the same value for p_Input_txt. I'll explain why later on when I get to the function-based index. But for now all you need to know is that when the value of CLIENT_INFO equals 'buildingindex' I simply return the original value passed to the function. Otherwise the parameter is decrypted and returned:
CREATE OR REPLACE FUNCTION cc_encrypt(p_Input_txt VARCHAR2)
RETURN VARCHAR2
DETERMINISTIC
IS
BEGIN
IF USERENV('CLIENT_INFO') = 'buildingindex' THEN
RETURN(p_Input_txt);
ELSE
DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(INPUT = UTL_RAW.CAST_TO_RAW(p_Input_txt)
,KEY = cc_security_pkg.g_Key_raw
,ENCRYPTED_DATA = cc_security_pkg.g_Encrypted_raw );
RETURN(UTL_RAW.CAST_TO_VARCHAR2(cc_security_pkg.g_Encrypted_raw));
END IF;
END cc_encrypt;
/
Now I want to be able to use and index when the query includes sensitive_table.encrypteed_data in the predicate. I believe this is where you are stuck. I want to execute this query that doesn't require pre-encryption of 0010:
SELECT cc_decrypt(encrypted_data),clear_text FROM sensitive_table WHERE encrypted_data = cc_encrypt('0010');
But the use of a function (cc_encrypt in this case) causes the optimizer to ignore an index on encrypted_data. This is where the function-based
RE: using obfuscation
Title: RE: using obfuscation
I've been developing a solution for a similar requirement. Although I reached a dead-end with this thread I think it solves your problem.
I'm picking it up from the point where the data in encrypted_data of sensitive_table needs encryption. I did that with an anonymous PL/SQL block:
CREATE TABLE sensitive_table (encrypted_data VARCHAR2(30), clear_text VARCHAR2(30));
I'll populate the table with text 16 characters long. I used 16 to simplify the example. DBMS_OBFUSCATION_TOOLKIT needs the data length to be a multiple of 8:
INSERT INTO sensitive_table
SELECT TO_CHAR(ROWNUM,'fm0009'),TO_CHAR(ROWNUM,'fm0009') FROM DBA_OBJECTS WHERE ROWNUM 1000;
COMMIT;
Now run the anonymous block to encrypt the data in the encrypted_data column:
--Encrypt data
DECLARE
CURSOR xtab IS SELECT encrypted_data FROM sensitive_table FOR UPDATE;
input_string VARCHAR2(16) ;
raw_input RAW(400) ;
key_string VARCHAR2(8):= 'abcde123';
raw_key RAW(400) := UTL_RAW.CAST_TO_RAW(key_string);
encrypted_raw RAW(2048);
error_in_input_buffer_length EXCEPTION;
PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
'*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING EXCEPTION ***';
double_encrypt_not_permitted EXCEPTION;
PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233);
DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=
'*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';
BEGIN
FOR xrec IN xtab LOOP
input_string:=xrec.encrypted_data;
raw_input:= UTL_RAW.CAST_TO_RAW(input_string);
DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(input = raw_input,key = raw_key, encrypted_data = encrypted_raw );
UPDATE sensitive_table
SET encrypted_data = UTL_RAW.CAST_TO_VARCHAR2(encrypted_raw)
WHERE CURRENT OF xtab;
END LOOP;
COMMIT;
EXCEPTION
WHEN error_in_input_buffer_length THEN
dbms_output.put_line(' ' || INPUT_BUFFER_LENGTH_ERR_MSG);
END;
/
I want to create a package (spec only) to contain global variables to be used by my encrypt/decrypt functions:
CREATE OR REPLACE PACKAGE cc_security_pkg
IS
g_KeyString_txt VARCHAR2(8):= 'abcde123';
g_Key_raw RAW(400) := UTL_RAW.CAST_TO_RAW(g_KeyString_txt);
g_Input_raw RAW(400) ;
g_Decrypted_raw RAW(2048);
g_Encrypted_raw RAW(2048);
g_ErrorInInputBufferLength_exc EXCEPTION;
PRAGMA EXCEPTION_INIT(g_ErrorInInputBufferLength_exc, -28232);
g_InputBufferLengthErrMsg_txt VARCHAR2(100) :=
'*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING EXCEPTION ***';
g_DoubleEncrypt_exc EXCEPTION;
PRAGMA EXCEPTION_INIT(g_DoubleEncrypt_exc, -28233);
g_DoubleEncryptionErrMsg_txt VARCHAR2(100) :=
'*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';
--
END;
/
Here is the decrypt function. Not much to explain:
CREATE OR REPLACE cc_decrypt(p_Input_txt VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(INPUT = UTL_RAW.CAST_TO_RAW(p_Input_txt)
,KEY = cc_security_pkg.g_Key_raw
,DECRYPTED_DATA = cc_security_pkg.g_Decrypted_raw );
RETURN(UTL_RAW.CAST_TO_VARCHAR2(cc_security_pkg.g_Decrypted_raw));
END cc_decrypt;
/
This is the corresponding encryption function. Two things to note, the use of the DETERMINISTIC pragma and the conflicting use of the value of CLIENT_INFO to determine the return value (encrypted or not.) I am deliberately (mis?)using DETERMINISTIC to trick Oracle into trusting that I will always return the same value for p_Input_txt. I'll explain why later on when I get to the function-based index. But for now all you need to know is that when the value of CLIENT_INFO equals 'buildingindex' I simply return the original value passed to the function. Otherwise the parameter is decrypted and returned:
CREATE OR REPLACE FUNCTION cc_encrypt(p_Input_txt VARCHAR2)
RETURN VARCHAR2
DETERMINISTIC
IS
BEGIN
IF USERENV('CLIENT_INFO') = 'buildingindex' THEN
RETURN(p_Input_txt);
ELSE
DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(INPUT = UTL_RAW.CAST_TO_RAW(p_Input_txt)
,KEY = cc_security_pkg.g_Key_raw
,ENCRYPTED_DATA = cc_security_pkg.g_Encrypted_raw );
RETURN(UTL_RAW.CAST_TO_VARCHAR2(cc_security_pkg.g_Encrypted_raw));
END IF;
END cc_encrypt;
/
Now I want to be able to use and index when the query includes sensitive_table.encrypteed_data in the predicate. I believe this is where you are stuck. I want to execute this query that doesn't require pre-encryption of 0010:
SELECT cc_decrypt(encrypted_data),clear_text FROM sensitive_table WHERE encrypted_data = cc_encrypt('0010');
But the use of a function (cc_encrypt in this case) causes the optimizer to ignore an index on encrypted_data. This is where the function-based index comes in. I will build the index on cc_encrypt(encrypted_data). But you say this will cause double-encryption and it will bomb out! With that though in mind it should be clear why I coded the cc_encrypt function to break
Re: using obfuscation
Sorry, I munged the create view with the query.. I was reminded of a meeting
while typing the note :)
create view my_data as select de_encrypt(sensitive_data) as
clear_sensitive_data, sensitive_data from my_table;
create index on my_table(sensitive data);
select clear_sensitive_data from my_data where
sensitive_data = encrypt('CLEAR TEXT')
Upon review, it does provide a cyphertext/cleartext attack, doesn't it? Just
goes to show you -- security is hard.
Steiner, Randy wrote:
Don,
It seems like a real good idea, but what am I putting inside my call to the
encrypt function in my Create View statement?
Randy
-Original Message-
Sent: Tuesday, September 10, 2002 10:13 AM
To: Multiple recipients of list ORACLE-L
Subject:Re: using obfuscation
File: Card for Don Jerman What about...
create view my_data as select de_encrypt(sensitive_data) as
clear_sensitive_data where
sensitive_data = encrypt('CLEAR TEXT') ?
This lets you create an index on the sensitive data without decrypting it,
and
the function need only be called once on the clear text.
Caveat: no idea if this should work :)
Steiner, Randy wrote:
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: 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).
begin:vcard
n:Jerman;Don
tel;work:919.508.1886
x-mozilla-html:TRUE
org:Database Management Service,Information Technology
version:2.1
email;internet:[EMAIL PROTECTED]
title:Database Administrator
adr;quoted-printable:;;Database Management Service,Information Technology=0D=0A104 Fayetteville Street Mall;Raleigh;NC;27699-1521;USA
x-mozilla-cpt:;-9536
fn:Don Jerman
end:vcard
RE: using obfuscation
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).
Re: using obfuscation
What about...
create view my_data as select de_encrypt(sensitive_data) as
clear_sensitive_data where
sensitive_data = encrypt('CLEAR TEXT') ?
This lets you create an index on the sensitive data without decrypting it, and
the function need only be called once on the clear text.
Caveat: no idea if this should work :)
Steiner, Randy wrote:
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).
begin:vcard
n:Jerman;Don
tel;work:919.508.1886
x-mozilla-html:TRUE
org:Database Management Service,Information Technology
version:2.1
email;internet:[EMAIL PROTECTED]
title:Database Administrator
adr;quoted-printable:;;Database Management Service,Information Technology=0D=0A104 Fayetteville Street Mall;Raleigh;NC;27699-1521;USA
x-mozilla-cpt:;-9536
fn:Don Jerman
end:vcard
RE: using obfuscation
Don,
It seems like a real good idea, but what am I putting inside my call to the
encrypt function in my Create View statement?
Randy
-Original Message-
Sent: Tuesday, September 10, 2002 10:13 AM
To: Multiple recipients of list ORACLE-L
Subject:Re: using obfuscation
File: Card for Don Jerman What about...
create view my_data as select de_encrypt(sensitive_data) as
clear_sensitive_data where
sensitive_data = encrypt('CLEAR TEXT') ?
This lets you create an index on the sensitive data without decrypting it,
and
the function need only be called once on the clear text.
Caveat: no idea if this should work :)
Steiner, Randy wrote:
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: 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).
RE: using obfuscation
Title: RE: using obfuscation
Can you create a Function based index on that column? That could be of use ...
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-Original Message-
From: Steiner, Randy [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 10, 2002 11:58 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: using obfuscation
Don,
It seems like a real good idea, but what am I putting inside my call to the
encrypt function in my Create View statement?
Randy
-Original Message-
Sent: Tuesday, September 10, 2002 10:13 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: using obfuscation
File: Card for Don Jerman What about...
create view my_data as select de_encrypt(sensitive_data) as
clear_sensitive_data where
sensitive_data = encrypt('CLEAR TEXT') ?
This lets you create an index on the sensitive data without decrypting it,
and
the function need only be called once on the clear text.
Caveat: no idea if this should work :)
Steiner, Randy wrote:
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: 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).
*This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank
you.*1
RE: using obfuscation
I've seen this type of thing done using domain indexes, i.e. defining your
own indextypes. Looked like a bit of work, though.
HTH,
Gary
Gary Kirsh
Next Extent Consulting
-Original Message-
Sent: Tuesday, September 10, 2002 11:58 AM
To: Multiple recipients of list ORACLE-L
Don,
It seems like a real good idea, but what am I putting inside my call to the
encrypt function in my Create View statement?
Randy
-Original Message-
Sent: Tuesday, September 10, 2002 10:13 AM
To: Multiple recipients of list ORACLE-L
Subject:Re: using obfuscation
File: Card for Don Jerman What about...
create view my_data as select de_encrypt(sensitive_data) as
clear_sensitive_data where
sensitive_data = encrypt('CLEAR TEXT') ?
This lets you create an index on the sensitive data without decrypting it,
and
the function need only be called once on the clear text.
Caveat: no idea if this should work :)
Steiner, Randy wrote:
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: 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: Kirsh, Gary
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).
RE: using obfuscation
Title: RE: using obfuscation
Wouldnt
that store the sensitive data in the index?
-Original
Message-
From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 10, 2002
12:13 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: using obfuscation
Can you create a Function based index on
that column? That could be of use ...
Raj
__
Rajendra Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of
ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-Original Message-
From: Steiner, Randy [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 10, 2002 11:58 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: using obfuscation
Don,
It seems like a real good idea, but what
am I putting inside my call to the
encrypt function in my Create View statement?
Randy
-Original Message-
Sent: Tuesday, September 10, 2002 10:13 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: using
obfuscation
File: Card for Don Jerman
What about...
create view my_data as select de_encrypt(sensitive_data)
as
clear_sensitive_data where
sensitive_data = encrypt('CLEAR TEXT') ?
This lets you create an index on the
sensitive data without decrypting it,
and
the function need only be called once on the clear text.
Caveat: no idea if this should work :)
Steiner, Randy wrote:
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: 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).
RE: using obfuscation
Title: RE: using obfuscation I wouldn't worry about that ... unless you dump the index blocks, you won't see that data anyway. OTOH, why don't you set your view to select based on a PK and then apply the function on selected rows?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Steiner, Randy [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 10, 2002 4:13 PM To: Multiple recipients of list ORACLE-L Subject: RE: using obfuscation Wouldn't that store the sensitive data in the index? -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 10, 2002 12:13 PM To: Multiple recipients of list ORACLE-L Subject: RE: using obfuscation Can you create a Function based index on that column? That could be of use ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: using obfuscation
The whole point of encryption is to hide sensitive data. If you put clear text in the index blocks, anyone smart enough to break into your database will probably figure out that dumping the index blocks will reveal all. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/10/2002 01:48 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: using obfuscation I wouldn't worry about that ... unless you dump the index blocks, you won't see that data anyway. OTOH, why don't you set your view to select based on a PK and then apply the function on selected rows?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, September 10, 2002 4:13 PM To: Multiple recipients of list ORACLE-L Wouldn't that store the sensitive data in the index? -Original Message- Sent: Tuesday, September 10, 2002 12:13 PM To: Multiple recipients of list ORACLE-L Can you create a Function based index on that column? That could be of use ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
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).
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).
