-- encrypt.sql
-- demonstrates the use of the dbms_obfuscation_toolkit by
-- prompting a user for a password and encrypting and decrypting
-- the data

set serveroutput on;
CLEAR BUFFER
PROMPT Please enter a password - Must be 8 characters !
PROMPT
ACCEPT PASSWD

DECLARE
input_string VARCHAR2(16) := '&PASSWD';
raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
key_string VARCHAR2(16) := 'keepthesecretnum';
raw_key RAW(128) := UTL_RAW.CAST_TO_RAW(key_string);
encrypted_raw RAW(2048);
encrypted_string VARCHAR2(2048);
decrypted_raw RAW(2048);
decrypted_string VARCHAR2(2048);
error_in_input_buffer_length EXCEPTION;
PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
'*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING EXCEPTION
***';
double_encrypt_not_permitted EXCEPTION;
PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233);
DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=
'*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';

-- 1. Begin testing raw data encryption and decryption
BEGIN
dbms_output.put_line('> ========= BEGIN TEST RAW DATA =========');
dbms_output.put_line('> Raw input : ' ||
UTL_RAW.CAST_TO_VARCHAR2(raw_input));
BEGIN
dbms_obfuscation_toolkit.DESEncrypt(input => raw_input,
key => raw_key, encrypted_data => encrypted_raw );
dbms_output.put_line('> encrypted hex value : ' ||
rawtohex(encrypted_raw));
dbms_obfuscation_toolkit.DESDecrypt(input => encrypted_raw,
key => raw_key, decrypted_data => decrypted_raw);
dbms_output.put_line('> Decrypted raw output : ' ||
UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw));
dbms_output.put_line('> ');
if UTL_RAW.CAST_TO_VARCHAR2(raw_input) =
UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw) THEN
dbms_output.put_line('> Raw DES Encyption and Decryption
successful');
END if;
EXCEPTION
WHEN error_in_input_buffer_length THEN
dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
END;
dbms_output.put_line('> ');
END;
/


Sample Output

SQL> start encrypt
Please enter a password - Must be 8 characters !

wildwind
old 2: input_string VARCHAR2(16) := '&PASSWD';
new 2: input_string VARCHAR2(16) := 'wildwind';
> ========= BEGIN TEST RAW DATA =========
> Raw input : wildwind
> encrypted hex value : 28EAA8E9E2CEA710
> Decrypted raw output : wildwind
>
> Raw DES Encyption and Decryption successful
>

PL/SQL procedure successfully completed.
=========================================================

HTHU

Ankur Shah
Oracle DBA
DHR-GA

----- Original Message -----
From: Shivsagar
To: LazyDBA.com Discussion
Sent: Friday, May 30, 2003 2:48 PM
Subject: dbms_obfuscation


Hi,
I need to encrypt and decrypt the column values of a particular table.
I am aware of DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT and
DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT procedures.
Now both of these procedures need a key_string which is like a key to open
the lock.
My problem is, if i write a stored proc to carry out this
encryption/decryption, wouldnt the KEY be visible to anyone who can view the
proc.
How do i make sure that no one except me and the boss knows the key.
Sagar
Oracle LazyDBA home page