Thanks for the code Ankur
My situation is like this.....
We store HPIN as a column in table CREDITS.
I get a text file with about 100 new HPINs to be inserted into CREDITS.
At the time of insertion i will use DESENCRYPT so that it is stored in the
database in the encrypted format.
I shall be doing this with the help of a stored procedure called HIDE_PROC.
My problem is that several other users will have access to HIDE_PROC and can
see
<quote>
key_string VARCHAR2(16) := 'keepthesecretnum';
<unquote>
And if they know the key_string, the encryption is useless for me.
Any suggestions...?
====
Sagar



----- Original Message -----
From: "Ankur Shah" <[Email Address Removed] "LazyDBA.com Discussion" <[Email Address Removed] "Shivsagar"
<[Email Address Removed] Saturday, May 31, 2003 12:31 AM
Subject: Re: dbms_obfuscation


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