分类: Oracle
2007-03-11 13:02:28
Using DES Encryption
The new DBMS_OBFUSCATION_TOOLKIT allows sophisticated encryption but insists that the text to be encrypted be a multiple of 8 bytes. As suggested by an Ask Tom post, a simple wrapper allows any length text to be encrypted whilst still satisfying this requirement.
Function
create or replace function
encrypt_data(p_text varchar2, p_key varchar2) return varchar2 is
v_text varchar2(4000);
v_enc varchar2(4000);
begin
v_text := rpad( p_text, (trunc(length(p_text)/8)+1)*8, chr(0));
sys.dbms_obfuscation_toolkit.desencrypt(
input_string => v_text,
key_string => p_key,
encrypted_string=>v_enc);
return v_enc;
end;
/
create or replace function decrypt_data(p_text varchar2,p_key varchar2) return varchar2 is
v_text varchar2(4000);
begin
sys.dbms_obfuscation_toolkit.desdecrypt(
input_string => p_text,
key_string => p_key,
decrypted_string=> v_text);
return rtrim(v_text,chr(0));
end;
/
drop table mjs_test;
CREATE TABLE MJS_TEST
(
OWNER VARCHAR2(30) NOT NULL,
OBJECT_NAME VARCHAR2(90) NOT NULL
)
/
insert into mjs_test values ('1', encrypt_data('PASSWORD1','12345678'));
insert into mjs_test values ('2', encrypt_data('PSW2','87654321'));
insert into mjs_test values ('3', encrypt_data('mjs1','ABCDEFGH'));
select * from mjs_test;
commit;
select owner, decrypt_data(object_name,'12345678') from mjs_test where owner = '1';
select owner, decrypt_data(object_name,'87654321') from mjs_test where owner = '2';
select owner, decrypt_data(object_name,'ABCDEFGH') from mjs_test where owner = '3';