Prevent calls to DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT from being visible in clear text in the SGA

Here is a simple example using SQL*Plus with bind variables:

	-- save the following conents in a file - i called mine des.sql
	-- or you can just type the commends in at the SQL prompt
	alter system flush shared_pool;
	
	create or replace function test_param(ipstr in varchar2, ks in varchar2)
	return varchar2
	as
	input_str varchar2(8):='';
	output_str varchar2(16):='';
	key_str varchar2(8):='';
	begin
	input_str:=ipstr;
	key_str:=ks;
	dbms_obfuscation_toolkit.DESEncrypt(
	input_string => input_str,
	key_string => key_str,
	encrypted_string => output_str);
	return output_str;
	end;
	/
	
	sho err function test_param
	
	accept inp prompt 'string to encrypt : ' hide
	accept enc_key prompt 'encryption key :' hide
	
	variable inpstr varchar2(8)
	variable keystr varchar2(8)
	execute :inpstr:='&inp';
	execute :keystr:='&enc_key';
	
	variable ret_var varchar2(16)
	
	exec :ret_var:=test_param(:inpstr,:keystr);
	print ret_var
	
	col sql_text for a65 wrap
	select sql_text from v$sqlarea;
						

The relevant part of the output is:

	SQL> @des
	
	System altered.
	
	
	Function created.
	
	No errors.
	string to encrypt : ********
	encryption key :********
	
	PL/SQL procedure successfully completed.
	
	
	PL/SQL procedure successfully completed.
	
	
	PL/SQL procedure successfully completed.
	
	
	RET_VAR
	--------------------------------
	{b? ;?e
	
	
	SQL_TEXT
	-----------------------------------------------------------------
	{output snipped}
	BEGIN :inpstr:='testtest'; END;
	
	BEGIN :keystr:='12345678'; END;
	BEGIN :ret_var:=test_param(:inpstr,:keystr); END;
	BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
	SELECT :ret_var ret_var FROM DUAL
	{output snipped}
						

Of course this example is flawed as the only way to get values into bind variables in SQL*Plus is to use an execute statement that will show up in the v$sqlarea output with clear text but i wanted to show that the parameters to the function call can be hidden and the call to dbms_obfuscation_toolkit can also be hidden. The bind variable issues in SQL*Plus can be resolved through a few means - simply selecting the data to be encrypted inside our wrapper function would remove the need to have the data displayed in the SGA and also would remove the need to have parameters in our function call to test_param. Another option would be to use a compiled language such as Pro*C or C / OCI and use bind variable assignments that way. These wouldn't show up so no clear text would be seen.

The call to dbms_obfuscation_toolkit has disappeared as it is now in a wrapper function so it would be slightly harder to find the encryption calls and would deter a casual hacker / dba.

I would be more worried about the key showing up in the SGA, there are many ways to resolve this, read it from a file, hard code it obfuscated or much better using a secure device with products such as the Thales RG7100 HSM or Eracom CSA8000.

commercial solutions are available. There are some links to encryption papers on my website http://www.petefinnigan.com/orasec.htm. Also check out jared Still's encryption page. Also search www.fatcity.com for the ORACLE-L list and find a recent posting by Craig Munday about encryption and key protection issues.