Back
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.
Here is a simple example using SQL*Plus with bind variables:
The relevant part of the output is:
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.
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.
-- 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;
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}
Back