Thursday, June 30, 2011

Examples on how to use "dbms_obfuscation_toolkit" package


drop table tbl_test purge;

create table tbl_test (
source_passwd varchar2(64),
encrypted_string varchar2(2000),
encrypted_raw raw(2000),
decrypted_string1 varchar2(64),
decrypted_string2 varchar2(64))
/



insert into tbl_test (source_passwd) values('abc');
insert into tbl_test (source_passwd) values('this is long password');
insert into tbl_test (source_passwd) values('Something_Special*');
commit;

-- below program will encrypt the password and store them into both varchar2 and raw columns.
-- the decryption will decrypt twice, one using varchar2, one using raw column

set serveroutput on
execute dbms_output.enable(10000);
declare
v_encrypted_raw RAW(2048);
v_encrypted_string VARCHAR2(2048);
v_decrypted_raw RAW(2048);
v_decrypted_string1 VARCHAR2(2048);
v_decrypted_string2 VARCHAR2(2048);
begin
-- encryption loop
for c1 in (select * from tbl_test)
loop
dbms_output.put_line(c1.source_passwd);
dbms_obfuscation_toolkit.DESEncrypt(input => UTL_RAW.CAST_TO_RAW(rpad(c1.source_passwd,64,' ')),
key => UTL_RAW.CAST_TO_RAW('abcdefgh'),
encrypted_data => v_encrypted_raw);
v_encrypted_string := UTL_RAW.CAST_TO_VARCHAR2(v_encrypted_raw);
update tbl_test
set encrypted_string=v_encrypted_string,
encrypted_raw=v_encrypted_raw
where source_passwd = c1.source_passwd;
end loop;
commit;
-- decrytion loop - using string column
for c1 in (select * from tbl_test)
loop
dbms_output.put_line(c1.source_passwd);
dbms_obfuscation_toolkit.DESDecrypt(input => UTL_RAW.CAST_TO_RAW(c1.encrypted_string),
key => UTL_RAW.CAST_TO_RAW('abcdefgh'),
decrypted_data => v_decrypted_raw);
v_decrypted_string1 := trim(UTL_RAW.CAST_TO_VARCHAR2(v_decrypted_raw));
update tbl_test
set decrypted_string1=v_decrypted_string1
where source_passwd = c1.source_passwd;
end loop;
commit;

-- decrytion loop - using raw column
for c1 in (select * from tbl_test)
loop
dbms_output.put_line(c1.source_passwd);
dbms_obfuscation_toolkit.DESDecrypt(input => c1.encrypted_raw,
key => UTL_RAW.CAST_TO_RAW('abcdefgh'),
decrypted_data => v_decrypted_raw);
v_decrypted_string2 := trim(UTL_RAW.CAST_TO_VARCHAR2(v_decrypted_raw));
update tbl_test
set decrypted_string2=v_decrypted_string2
where source_passwd = c1.source_passwd;
end loop;
commit;
end;
/

select * from tbl_test where source_passwd=decrypted_string1;
select * from tbl_test where source_passwd=decrypted_string2;