Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: using obfuscation

RE: using obfuscation

From: Aponte, Tony <AponteT_at_hsn.net>
Date: Wed, 02 Oct 2002 13:33:23 -0800
Message-ID: <F001.004DEA1A.20021002133323@fatcity.com> 





I was wondering if this was of any help.



Tony Aponte



-----Original Message-----


Sent: Tuesday, September 17, 2002 7:04 PM
To: 'ORACLE-L_at_fatcity.com'


Cc: 'RASTEIN_at_NYCT.com'




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,'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):= '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 0000000000000010:

SELECT cc_decrypt(encrypted_data),clear_text FROM sensitive_table WHERE encrypted_data = 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.  

--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 
  sensitive_table(SUBSTR(cc_encrypt(encrypted_data),1,16));

I'll reset CLIENT_INFO so that cc_encrypt does it's normal work:
  
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=TRUE;
ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;
ALTER SESSION SET OPTIMIZER_GOAL=FIRST_ROWS;
ANALYZE INDEX encrypted_data_idx COMPUTE STATISTICS;

Ok.  Now I'm ready to test the use of the index.  

SELECT cc_decrypt(encrypted_data),clear_text FROM sensitive_table WHERE SUBSTR(cc_encrypt(encrypted_data),1,16) = cc_encrypt('0000000000000010');

Operation	Object Name	Rows	Bytes	Cost	TQ	In/Out	PStart	PStop

SELECT STATEMENT		6  	 	1  	 	      	             	 
  TABLE ACCESS BY INDEX ROWID	SENSITIVE_TABLE	6  	204  	1  	 	      	             	 
    INDEX RANGE SCAN	ENCRYPTED_DATA_IDX	6  	 	1  	 	      	             	 

My statement is too complicated so I will hide some of the complexity behind a view:

CREATE OR REPLACE VIEW exposed_view 
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 = cc_encrypt('0000000000000010');

Operation	Object Name	Rows	Bytes	Cost	TQ	In/Out	PStart	PStop

SELECT STATEMENT		6  	 	1  	 	      	             	 
  TABLE ACCESS BY INDEX ROWID	SENSITIVE_TABLE	6  	204  	1  	 	      	             	 
    INDEX RANGE SCAN	ENCRYPTED_DATA_IDX	6  	 	1  	 	      	             	 

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 = cc_encrypt('0000000000000010');
Operation	Object Name	Rows	Bytes	Cost	TQ	In/Out	PStart	PStop

SELECT STATEMENT		6  	 	1  	 	      	             	 
  TABLE ACCESS BY INDEX ROWID	SENSITIVE_TABLE	6  	204  	1  	 	      	             	 
    INDEX RANGE SCAN	ENCRYPTED_DATA_IDX	6  	 	1  	 	      	             	 


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


-- 
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).


Received on Wed Oct 02 2002 - 16:33:23 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US