Pete Finnigan's Oracle Security Forum


    
      
        Pete Finnigan's Oracle Security Forum
        (http://www.petefinnigan.com/forum/yabb/YaBB.cgi)
      

        Oracle Security >> Oracle Security >> DBMS_OBFUSCATION_TOOLKIT
        
(Message started by: Pete Finnigan on May 17^th, 2006, 4:43pm)

Title: DBMS_OBFUSCATION_TOOLKIT
Post by Pete Finnigan on May 17^th, 2006, 4:43pm

I am trying to use the DBMS_OBFUSCATION_TOOLKIT to encrypt the customer first name, last name, and SSN in a pre-insert trigger.

Every time I try to encrypt the SSN through the trigger, I get an Oracle error "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" in the trigger.

It only happens on SSN, not on any other field that I try to encrypt.

SSN is defined in the table as CHAR(9).
Any ideas?

Here is the sample trigger code that fails:

CREATE OR REPLACE TRIGGER SYSADM.TR_OBFUSCATE_CADS_CUSTMR_SSN
BEFORE INSERT
ON SYSADM.CADS_PERSON_ID_INFO
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE

input_str varchar2(32):='';

output_str varchar2(32):='';

key_str varchar2(8):='MagicKey';
BEGIN

If :New.SSN is not null

then

input_str := rpad( :New.SSN, (trunc(length(:New.SSN)/8)+1)*8, chr(0) );

dbms_obfuscation_toolkit.DESencrypt(

input_string => input_str,

key_string => key_str,

encrypted_string => output_str);

:New.SSN := output_str;

end if;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END TR_OBFUSCATE_CADS_CUSTMR_SSN;