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

This very short article comes from a post to comp.databases.oracle.server in answer to a question where someone was asking how to hide the calls to dbms_obfuscation_toolkit.DESEncrypt so that the call and its parameters cannot be read from the SGA with an SQL query. The following is my answer to the question. I am posting it here as it may be of use to others:

There are a number of possibilities to prevent the clear text call of DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT from being visible to a DBA in the SGA via selecting from v$sqlarea.

If the call to dbms_obfuscation_toolkit.desencrypt is wrapped inside a procedure or package then the actual call to this library is not shown in the SGA just the call to the wrapper package
If bind variables are used then only the name of the bind variable is displayed in the SGA not the actual data.

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. Also check out jared Still's http://www.cybcon.com/~jkstill/util/encryption/encryption.html (Broken link now in 2022). Also search www.fatcity.com for the ORACLE-L list and find a recent posting by Craig Munday about encryption and key protection issues.

Of course wrap your pl/sql with the wrap utility as this is better than clear text source code although not totally secure. Beware of any text strings etc in the wrapped output.

Back