| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: using obfuscation
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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=20
SELECT =
TO_CHAR(ROWNUM,'fm0000000000000009'),TO_CHAR(ROWNUM,'fm0000000000000009')=
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):=3D 'abcde123'; raw_key RAW(400) :=3D UTL_RAW.CAST_TO_RAW(key_string); encrypted_raw RAW(2048);
input_string:=3Dxrec.encrypted_data; raw_input:=3D UTL_RAW.CAST_TO_RAW(input_string); DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(input =3D> raw_input,key =3D> =raw_key, encrypted_data =3D> encrypted_raw );
UPDATE sensitive_table=20
SET encrypted_data =3D UTL_RAW.CAST_TO_VARCHAR2(encrypted_raw) WHERE CURRENT OF xtab;=20
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):=3D 'abcde123'; g_Key_raw RAW(400) :=3D 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) :=3D
'*** 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) :=3D
'*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';
-- END;Received on Tue Sep 17 2002 - 19:08:19 CDT
/
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 =3D> = UTL_RAW.CAST_TO_RAW(p_Input_txt) ,KEY =3D> cc_security_pkg.g_Key_raw ,DECRYPTED_DATA =3D> 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') =3D 'buildingindex' THEN RETURN(p_Input_txt); ELSE DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(INPUT =3D> = UTL_RAW.CAST_TO_RAW(p_Input_txt) ,KEY =3D> cc_security_pkg.g_Key_raw ,ENCRYPTED_DATA =3D> 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 0000000000000010: SELECT cc_decrypt(encrypted_data),clear_text FROM sensitive_table WHERE = encrypted_data =3D cc_encrypt('0000000000000010'); 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 the trust given to me by the use of the DETERMINISTIC = pragma. I need to build the index using cc_encrypt so that my query can = use it. But I need it to do nothing when building the index because the = data is already encrypted. To do my trickery I will use = DMBS_APPLICATION_INFO to set a magic value in my session context that is = used in the IF statement of cc_encrypt. =20 --Set up session context for building the index BEGIN DBMS_APPLICATION_INFO.SET_CLIENT_INFO('buildingindex'); DBMS_OUTPUT.PUT_LINE(USERENV('CLIENT_INFO')); END;
/
I'm know ready to create the index. I learned from Expert One-on-One = Oracle that the index column length needs to be constrained. See the = section on function-based indexes: CREATE INDEX encrypted_data_idx ON=20 sensitive_table(SUBSTR(cc_encrypt(encrypted_data),1,16)); I'll reset CLIENT_INFO so that cc_encrypt does it's normal work: =20 BEGIN DBMS_APPLICATION_INFO.SET_CLIENT_INFO(''); DBMS_OUTPUT.PUT_LINE(USERENV('CLIENT_INFO')); END;
/
Function-based indexes have certain requirements that you will need to = bone-up on: ALTER SESSION SET QUERY_REWRITE_ENABLED=3DTRUE; ALTER SESSION SET QUERY_REWRITE_INTEGRITY=3DTRUSTED; ALTER SESSION SET OPTIMIZER_GOAL=3DFIRST_ROWS; ANALYZE INDEX encrypted_data_idx COMPUTE STATISTICS; Ok. Now I'm ready to test the use of the index. =20 SELECT cc_decrypt(encrypted_data),clear_text FROM sensitive_table WHERE = SUBSTR(cc_encrypt(encrypted_data),1,16) =3D = cc_encrypt('0000000000000010'); Operation Object Name Rows Bytes Cost TQ In/Out PStart PStop SELECT STATEMENT 6 1 =20 TABLE ACCESS BY INDEX ROWID SENSITIVE_TABLE 6 204 1 = =20 INDEX RANGE SCAN ENCRYPTED_DATA_IDX 6 1 = =20 My statement is too complicated so I will hide some of the complexity = behind a view: CREATE OR REPLACE VIEW exposed_view=20 AS SELECT SUBSTR(cc_encrypt(encrypted_data),1,16) encrypted_data, = clear_text FROM sensitive_table; Here it is a little cleaner: SELECT cc_decrypt(encrypted_data),clear_text FROM exposed_view WHERE = encrypted_data =3D cc_encrypt('0000000000000010'); Operation Object Name Rows Bytes Cost TQ In/Out PStart PStop SELECT STATEMENT 6 1 =20 TABLE ACCESS BY INDEX ROWID SENSITIVE_TABLE 6 204 1 = =20 INDEX RANGE SCAN ENCRYPTED_DATA_IDX 6 1 = =20 A little more cleansing: CREATE OR REPLACE VIEW final_view AS SELECT cc_decrypt(encrypted_data) = decrypted_data,encrypted_data,clear_text FROM exposed_view; SELECT decrypted_data,clear_text FROM final_view WHERE encrypted_data = =3D cc_encrypt('0000000000000010'); Operation Object Name Rows Bytes Cost TQ In/Out PStart PStop SELECT STATEMENT 6 1 =20 TABLE ACCESS BY INDEX ROWID SENSITIVE_TABLE 6 204 1 = =20 INDEX RANGE SCAN ENCRYPTED_DATA_IDX 6 1 = =20 Tada! Encrypted data without sacrificing speed and security. Actually, = it's not ready for prime time. You need to handle insert and update = operations on the table and column (a before .. for each row trigger = should work, or maybe an instead-of trigger.) You also need certain = privileges to create function-based indexes. I haven't considered the = implications for index rebuilds. And finally, you're going to have to = deal with the purists that will choke on the violation of the = DETERMINISTIC requirement to return the same value for a given input. I wasn't able to make use of this method but I hope someone gets some = value out of my work. I welcome any feedback from you or anyone else = that tries it. Tony Aponte -----Original Message----- Sent: Monday, September 09, 2002 5:43 PM To: Multiple recipients of list ORACLE-L 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.=20 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.=20 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 --=20 Please see the official ORACLE-L FAQ: http://www.orafaq.com --=20 Author: Steiner, Randy INET: RASTEIN_at_NYCT.com 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: ListGuru_at_fatcity.com (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). ------_=_NextPart_001_01C25E9E.91DD5D04 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 6.0.6249.1"> <TITLE>RE: using obfuscation</TITLE> </HEAD> <BODY> <!-- Converted from text/plain format --> <P><FONT SIZE=3D2>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.</FONT></P> <P><FONT SIZE=3D2>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:</FONT></P> <P><FONT SIZE=3D2>CREATE TABLE sensitive_table (encrypted_data = VARCHAR2(30), clear_text VARCHAR2(30));</FONT> </P> <P><FONT SIZE=3D2>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:</FONT></P> <P><FONT SIZE=3D2>INSERT INTO sensitive_table </FONT> <BR><FONT SIZE=3D2>SELECT = TO_CHAR(ROWNUM,'fm0000000000000009'),TO_CHAR(ROWNUM,'fm0000000000000009')= FROM DBA_OBJECTS WHERE ROWNUM < 1000;</FONT> </P> <P><FONT SIZE=3D2>COMMIT;</FONT> </P> <P><FONT SIZE=3D2>Now run the anonymous block to encrypt the data in the = encrypted_data column:</FONT> </P> <P><FONT SIZE=3D2>--Encrypt data</FONT> <BR><FONT SIZE=3D2>DECLARE</FONT> <BR><FONT SIZE=3D2>CURSOR xtab IS SELECT encrypted_data FROM = sensitive_table FOR UPDATE;</FONT> <BR><FONT = SIZE=3D2>input_string = VARCHAR2(16) ;</FONT> <BR><FONT = SIZE=3D2>raw_input &= nbsp; RAW(400) ;</FONT> <BR><FONT = SIZE=3D2>key_string = VARCHAR2(8):=3D 'abcde123';</FONT> <BR><FONT = SIZE=3D2>raw_key &nb= sp; RAW(400) :=3D UTL_RAW.CAST_TO_RAW(key_string);</FONT> <BR><FONT SIZE=3D2>encrypted_raw = RAW(2048);</FONT> <BR><FONT SIZE=3D2>error_in_input_buffer_length EXCEPTION;</FONT> <BR><FONT SIZE=3D2>PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, = -28232);</FONT> <BR><FONT SIZE=3D2>INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=3D</FONT> <BR><FONT SIZE=3D2> '*** DES INPUT BUFFER NOT A = MULTIPLE OF 8 BYTES - IGNORING EXCEPTION ***';</FONT> <BR><FONT SIZE=3D2>double_encrypt_not_permitted EXCEPTION;</FONT> <BR><FONT SIZE=3D2>PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, = -28233);</FONT> <BR><FONT SIZE=3D2>DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=3D</FONT> <BR><FONT SIZE=3D2> '*** CANNOT DOUBLE ENCRYPT DATA - = IGNORING EXCEPTION ***';</FONT> <BR><FONT SIZE=3D2>BEGIN</FONT> <BR><FONT SIZE=3D2>FOR xrec IN xtab LOOP</FONT> <BR> <FONT = SIZE=3D2>input_string:=3Dxrec.encrypted_data;</FONT> <BR> <FONT = SIZE=3D2>raw_input:=3D UTL_RAW.CAST_TO_RAW(input_string);</FONT> <BR> <FONT = SIZE=3D2>DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(input =3D> raw_input,key = =3D> raw_key, encrypted_data =3D> encrypted_raw );</FONT> <BR><FONT SIZE=3D2> UPDATE sensitive_table </FONT> <BR> <FONT = SIZE=3D2> SET encrypted_data =3D = UTL_RAW.CAST_TO_VARCHAR2(encrypted_raw)</FONT> <BR> <FONT SIZE=3D2> = WHERE CURRENT OF xtab; </FONT> <BR><FONT SIZE=3D2>END LOOP;</FONT> <BR><FONT SIZE=3D2>COMMIT;</FONT> <BR><FONT SIZE=3D2>EXCEPTION</FONT> <BR><FONT SIZE=3D2> WHEN error_in_input_buffer_length THEN</FONT> <BR><FONT SIZE=3D2> = dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);</FONT> <BR><FONT SIZE=3D2>END;</FONT> <BR><FONT SIZE=3D2>/</FONT> </P> <P><FONT SIZE=3D2>I want to create a package (spec only) to contain = global variables to be used by my encrypt/decrypt functions:</FONT> </P> <P><FONT SIZE=3D2>CREATE OR REPLACE PACKAGE cc_security_pkg</FONT> <BR><FONT SIZE=3D2>IS</FONT> <BR><FONT SIZE=3D2>g_KeyString_txt VARCHAR2(8):=3D = 'abcde123';</FONT> <BR><FONT = SIZE=3D2>g_Key_raw &= nbsp; RAW(400) :=3D UTL_RAW.CAST_TO_RAW(g_KeyString_txt);</FONT> <BR><FONT = SIZE=3D2>g_Input_raw = RAW(400) ;</FONT> <BR><FONT SIZE=3D2>g_Decrypted_raw = RAW(2048);</FONT> <BR><FONT SIZE=3D2>g_Encrypted_raw = RAW(2048);</FONT> <BR><FONT SIZE=3D2>g_ErrorInInputBufferLength_exc EXCEPTION;</FONT> <BR><FONT SIZE=3D2>PRAGMA EXCEPTION_INIT(g_ErrorInInputBufferLength_exc, = -28232);</FONT> <BR><FONT SIZE=3D2>g_InputBufferLengthErrMsg_txt VARCHAR2(100) = :=3D</FONT> <BR><FONT SIZE=3D2> '*** DES INPUT BUFFER NOT A = MULTIPLE OF 8 BYTES - IGNORING EXCEPTION ***';</FONT> <BR><FONT SIZE=3D2>g_DoubleEncrypt_exc EXCEPTION;</FONT> <BR><FONT SIZE=3D2>PRAGMA EXCEPTION_INIT(g_DoubleEncrypt_exc, = -28233);</FONT> <BR><FONT SIZE=3D2>g_DoubleEncryptionErrMsg_txt VARCHAR2(100) = :=3D</FONT> <BR><FONT SIZE=3D2> '*** CANNOT DOUBLE ENCRYPT DATA - = IGNORING EXCEPTION ***';</FONT> <BR><FONT SIZE=3D2>--</FONT> <BR><FONT SIZE=3D2>END;</FONT> <BR><FONT SIZE=3D2>/</FONT> </P> <P><FONT SIZE=3D2>Here is the decrypt function. Not much to = explain:</FONT> </P> <P><FONT SIZE=3D2>CREATE OR REPLACE cc_decrypt(p_Input_txt = VARCHAR2)</FONT> <BR><FONT SIZE=3D2>RETURN VARCHAR2</FONT> <BR><FONT SIZE=3D2>IS</FONT> <BR><FONT SIZE=3D2>BEGIN</FONT> <BR><FONT SIZE=3D2> DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(INPUT = =3D> UTL_RAW.CAST_TO_RAW(p_Input_txt)</FONT> <BR><FONT SIZE=3D2> = = = = = = = = ,KEY =3D> = cc_security_pkg.g_Key_raw</FONT> <BR> = = = = = = = = <FONT SIZE=3D2> = ,DECRYPTED_DATA =3D> cc_security_pkg.g_Decrypted_raw );</FONT></P> <P><FONT SIZE=3D2> = RETURN(UTL_RAW.CAST_TO_VARCHAR2(cc_security_pkg.g_Decrypted_raw));</FONT>= <BR><FONT SIZE=3D2>END cc_decrypt;</FONT> <BR><FONT SIZE=3D2>/</FONT> </P> <P><FONT SIZE=3D2>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:</FONT></P> <P><FONT SIZE=3D2>CREATE OR REPLACE FUNCTION cc_encrypt(p_Input_txt = VARCHAR2)</FONT> <BR><FONT SIZE=3D2>RETURN VARCHAR2</FONT> <BR><FONT SIZE=3D2>DETERMINISTIC</FONT> <BR><FONT SIZE=3D2>IS</FONT> <BR><FONT SIZE=3D2>BEGIN</FONT> <BR><FONT SIZE=3D2> IF USERENV('CLIENT_INFO') =3D 'buildingindex' = THEN</FONT> <BR><FONT SIZE=3D2> = RETURN(p_Input_txt);</FONT> <BR><FONT SIZE=3D2> ELSE</FONT> <BR><FONT SIZE=3D2> = DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(INPUT =3D> = UTL_RAW.CAST_TO_RAW(p_Input_txt)</FONT> <BR><FONT SIZE=3D2> = = = = = = = = ,KEY =3D> = cc_security_pkg.g_Key_raw</FONT> <BR> = = = = = = = = <FONT SIZE=3D2> = ,ENCRYPTED_DATA =3D> cc_security_pkg.g_Encrypted_raw );</FONT></P> <P><FONT SIZE=3D2> = RETURN(UTL_RAW.CAST_TO_VARCHAR2(cc_security_pkg.g_Encrypted_raw));</FONT>= <BR><FONT SIZE=3D2> END IF;</FONT> <BR><FONT SIZE=3D2>END cc_encrypt;</FONT> <BR><FONT SIZE=3D2>/</FONT> </P> <P><FONT SIZE=3D2>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 0000000000000010:</FONT></P> <P><FONT SIZE=3D2>SELECT cc_decrypt(encrypted_data),clear_text FROM = sensitive_table WHERE encrypted_data =3D = cc_encrypt('0000000000000010');</FONT></P> <P><FONT SIZE=3D2>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 the = trust given to me by the use of the DETERMINISTIC pragma. I need = to build the index using cc_encrypt so that my query can use it. But I = need it to do nothing when building the index because the data is = already encrypted. To do my trickery I will use = DMBS_APPLICATION_INFO to set a magic value in my session context that is = used in the IF statement of cc_encrypt. </FONT></P> <P><FONT SIZE=3D2>--Set up session context for building the index</FONT> <BR><FONT SIZE=3D2>BEGIN</FONT> <BR><FONT = SIZE=3D2>DBMS_APPLICATION_INFO.SET_CLIENT_INFO('buildingindex');</FONT> <BR><FONT SIZE=3D2>DBMS_OUTPUT.PUT_LINE(USERENV('CLIENT_INFO'));</FONT> <BR><FONT SIZE=3D2>END;</FONT> <BR><FONT SIZE=3D2>/</FONT> </P> <P><FONT SIZE=3D2>I'm know ready to create the index. I learned = from Expert One-on-One Oracle that the index column length needs to be = constrained. See the section on function-based indexes:</FONT></P> <P><FONT SIZE=3D2>CREATE INDEX encrypted_data_idx ON </FONT> <BR><FONT SIZE=3D2> = sensitive_table(SUBSTR(cc_encrypt(encrypted_data),1,16));</FONT> </P> <P><FONT SIZE=3D2>I'll reset CLIENT_INFO so that cc_encrypt does it's = normal work:</FONT> <BR><FONT SIZE=3D2> </FONT> <BR><FONT SIZE=3D2>BEGIN</FONT> <BR><FONT SIZE=3D2>DBMS_APPLICATION_INFO.SET_CLIENT_INFO('');</FONT> <BR><FONT SIZE=3D2>DBMS_OUTPUT.PUT_LINE(USERENV('CLIENT_INFO'));</FONT> <BR><FONT SIZE=3D2>END;</FONT> <BR><FONT SIZE=3D2>/</FONT> </P> <P><FONT SIZE=3D2>Function-based indexes have certain requirements that = you will need to bone-up on:</FONT> </P> <P><FONT SIZE=3D2>ALTER SESSION SET QUERY_REWRITE_ENABLED=3DTRUE;</FONT> <BR><FONT SIZE=3D2>ALTER SESSION SET = QUERY_REWRITE_INTEGRITY=3DTRUSTED;</FONT> <BR><FONT SIZE=3D2>ALTER SESSION SET OPTIMIZER_GOAL=3DFIRST_ROWS;</FONT> <BR><FONT SIZE=3D2>ANALYZE INDEX encrypted_data_idx COMPUTE = STATISTICS;</FONT> </P> <P><FONT SIZE=3D2>Ok. Now I'm ready to test the use of the = index. </FONT> </P> <P><FONT SIZE=3D2>SELECT cc_decrypt(encrypted_data),clear_text FROM = sensitive_table WHERE SUBSTR(cc_encrypt(encrypted_data),1,16) =3D = cc_encrypt('0000000000000010');</FONT></P> <P><FONT SIZE=3D2>Operation Object = Name Rows Bytes = Cost TQ In/Out = PStart PStop</FONT> </P> <P><FONT SIZE=3D2>SELECT = STATEMENT = 6 = = 1 = = = = </FONT> <BR><FONT SIZE=3D2> TABLE ACCESS BY INDEX ROWID = SENSITIVE_TABLE 6 204 = 1 = = = = </FONT> <BR><FONT SIZE=3D2> INDEX RANGE SCAN = ENCRYPTED_DATA_IDX 6 = = 1 = = = = </FONT> </P> <P><FONT SIZE=3D2>My statement is too complicated so I will hide some of = the complexity behind a view:</FONT> </P> <P><FONT SIZE=3D2>CREATE OR REPLACE VIEW exposed_view </FONT> <BR><FONT SIZE=3D2>AS</FONT> <BR><FONT SIZE=3D2>SELECT SUBSTR(cc_encrypt(encrypted_data),1,16) = encrypted_data, clear_text FROM sensitive_table;</FONT> </P> <P><FONT SIZE=3D2>Here it is a little cleaner:</FONT> </P> <P><FONT SIZE=3D2>SELECT cc_decrypt(encrypted_data),clear_text FROM = exposed_view WHERE encrypted_data =3D = cc_encrypt('0000000000000010');</FONT> </P> <P><FONT SIZE=3D2>Operation Object = Name Rows Bytes = Cost TQ In/Out = PStart PStop</FONT> </P> <P><FONT SIZE=3D2>SELECT = STATEMENT = 6 = = 1 = = = = </FONT> <BR><FONT SIZE=3D2> TABLE ACCESS BY INDEX ROWID = SENSITIVE_TABLE 6 204 = 1 = = = = </FONT> <BR><FONT SIZE=3D2> INDEX RANGE SCAN = ENCRYPTED_DATA_IDX 6 = = 1 = = = = </FONT> </P> <P><FONT SIZE=3D2>A little more cleansing:</FONT> </P> <P><FONT SIZE=3D2>CREATE OR REPLACE VIEW final_view</FONT> <BR><FONT SIZE=3D2>AS</FONT> <BR><FONT SIZE=3D2>SELECT cc_decrypt(encrypted_data) = decrypted_data,encrypted_data,clear_text FROM exposed_view;</FONT> </P> <P><FONT SIZE=3D2>SELECT decrypted_data,clear_text FROM final_view WHERE = encrypted_data =3D cc_encrypt('0000000000000010');</FONT> <BR><FONT SIZE=3D2>Operation Object = Name Rows Bytes = Cost TQ In/Out = PStart PStop</FONT> </P> <P><FONT SIZE=3D2>SELECT = STATEMENT = 6 = = 1 = = = = </FONT> <BR><FONT SIZE=3D2> TABLE ACCESS BY INDEX ROWID = SENSITIVE_TABLE 6 204 = 1 = = = = </FONT> <BR><FONT SIZE=3D2> INDEX RANGE SCAN = ENCRYPTED_DATA_IDX 6 = = 1 = = = = </FONT> </P> <BR> <P><FONT SIZE=3D2>Tada! Encrypted data without sacrificing speed = and security. Actually, it's not ready for prime time. You = need to handle insert and update operations on the table and column (a = before .. for each row trigger should work, or maybe an instead-of = trigger.) You also need certain privileges to create = function-based indexes. I haven't considered the implications for = index rebuilds. And finally, you're going to have to deal with the = purists that will choke on the violation of the DETERMINISTIC = requirement to return the same value for a given input.</FONT></P> <P><FONT SIZE=3D2>I wasn't able to make use of this method but I hope = someone gets some value out of my work. I welcome any feedback = from you or anyone else that tries it.</FONT></P> <P><FONT SIZE=3D2>Tony Aponte</FONT> </P> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>From: Steiner, Randy [<A = HREF=3D"mailto:RASTEIN_at_NYCT.com">mailto:RASTEIN_at_NYCT.com</A>]</FONT> <BR><FONT SIZE=3D2>Sent: Monday, September 09, 2002 5:43 PM</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>Subject: using obfuscation</FONT> </P> <BR> <P><FONT SIZE=3D2>Hi all,</FONT> </P> <P><FONT SIZE=3D2>I have downloaded the Metalink Notes on implementing = dbms_obfuscation. I am</FONT> <BR><FONT SIZE=3D2>using multiple front ends on the database, so the way = I plan to implement</FONT> <BR><FONT SIZE=3D2>the de-encryption is with a de-encrypt function in a = view. </FONT> </P> <P><FONT SIZE=3D2>Create View my_data</FONT> <BR><FONT SIZE=3D2>AS</FONT> <BR><FONT SIZE=3D2>Select de_encrypt(sensitive_data) AS = sensitive_data</FONT> <BR><FONT SIZE=3D2>,other_data</FONT> <BR><FONT SIZE=3D2>FROM original_table</FONT> <BR><FONT SIZE=3D2>;</FONT> </P> <P><FONT SIZE=3D2>If I select from the view with a where clause on = other_data, the response</FONT> <BR><FONT SIZE=3D2>time is fine. If I select from the view with a where = clause on</FONT> <BR><FONT SIZE=3D2>sensitive_data, I do a full table scan and which = takes about 15 minutes.</FONT> <BR><FONT SIZE=3D2>The de-encrypt function is copied from a Metalink = note, nothing fancy. </FONT> </P> <P><FONT SIZE=3D2>Since I have various front ends, I can not de-encrypt = the data in the front</FONT> <BR><FONT SIZE=3D2>end. The only way I can think of is with the = function in a view, but the</FONT> <BR><FONT SIZE=3D2>response time is unacceptable. Does anyone have = any thoughts on this?</FONT> </P> <P><FONT SIZE=3D2>Thanks</FONT> <BR><FONT SIZE=3D2>Randy</FONT> <BR><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com">http://www.orafaq.com</A></FONT> <BR><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: Steiner, Randy</FONT> <BR><FONT SIZE=3D2> INET: RASTEIN_at_NYCT.com</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>----------------------------------------------------------------= ----</FONT> <BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB = ORACLE-L</FONT> <BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed = from). You may</FONT> <BR><FONT SIZE=3D2>also send the HELP command for other information = (like subscribing).</FONT> </P> </BODY> </HTML> ------_=_NextPart_001_01C25E9E.91DD5D04-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Aponte, Tony INET: AponteT_at_hsn.net Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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).
 |
 |