Re: DBMS_OBFUSCATION

From: Pete Finnigan <plsql_at_petefinnigan.com>
Date: Sat, 1 Nov 2003 16:32:08 +0000
Message-ID: <og86AnAIA+o$QxL9@peterfinnigan.demon.co.uk>

Hi Rick and Fabio,

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

o - 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

o - 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:

<code>
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;
</code>

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.

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 page http://www.cybcon.com/~jkstill/util/encryption/en cryption.html about encryption. 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.

Hope this helps

Kind regards

Pete

-- 
Pete Finnigan
email:pete_at_petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

Received on Sat Nov 01 2003 - 10:32:08 CST

Re: DBMS_OBFUSCATION_TOOLKIT