good analysis pete... appreciate the effort in putting this together
"Pete Finnigan" <plsql_at_petefinnigan.com> wrote in message
news:og86AnAIA+o$QxL9_at_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.
>
>
> 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 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 - 13:52:46 CST
