oracle 3des函数,oracle用存储过程加密一段字符串(3des算法)

oracle用存储过程加密一段字符串(3des算法)

2/9/2008人气：10621

create or replace package PG_ENCRYPT_DECRYPT

is

iKey varchar2(8):='Oracle9i';

function GEN_RAW_KEY  ( iKey in varchar2) return raw;

function DECRYPT_3KEY_MODE(iValue in raw,iMode in pls_integer)return varchar2;

function ENCRYPT_3KEY_MODE(iValue in varchar2,iMode in pls_integer)return raw;

function FormatStr(iValue in varchar2)return varchar2;

function FormatStr2(iValue in varchar2)return varchar2;

end;

/

create or replace package body PG_ENCRYPT_DECRYPT

is

function GEN_RAW_KEY  ( iKey in varchar2)

return raw

as

rawkey raw(240) := '';

begin

for i in 1..length(iKey) loop

rawkey := rawkeyhextoraw(to_char(ascii(substr(iKey, i, 1))));

end loop;

return rawkey;

end GEN_RAW_KEY;

/* Creating function DECRYPT_3KEY_MODE*/

FUNCTION DECRYPT_3KEY_MODE  ( iValue in raw, iMode in pls_integer)return varchar2

as

vDecrypted varchar2(4000);

rawkey raw(240) := '';

begin

rawkey := GEN_RAW_KEY(iKey);-- decrypt input string

vDecrypted := dbms_obfuscation_toolkit.des3decrypt (UTL_RAW.CAST_TO_VARCHAR2(iValue), key_string => rawkey, which => iMode);

return FormatStr2(vDecrypted);

end DECRYPT_3KEY_MODE;

/*Creating function ENCRYPT_3KEY_MODE*/

FUNCTION ENCRYPT_3KEY_MODE  ( iValue in varchar2,  iMode in pls_integer)return raw

as

vEncrypted varchar2(4000);

vEncryptedRaw Raw(2048);

rawkey raw(240) := '';

begin

rawkey := GEN_RAW_KEY(iKey);-- encrypt input string

vEncrypted := dbms_obfuscation_toolkit.des3encrypt (FormatStr(iValue), key_string => rawkey, which => iMode);

-- convert to raw as out

vEncryptedRaw := UTL_RAW.CAST_TO_RAW(vEncrypted);

return vEncryptedRaw;

end ENCRYPT_3KEY_MODE;

function FormatStr(iValue in varchar2)return varchar2

as

begin

declare

i number;

j number;

m_value varchar2(4000);

begin

m_value:=iValue;

i:=(length(m_value) mod 8);

if i<>0 then

j:=1;

for j in 1..(8-i) loop

m_value:=m_value'#';

end loop;

end if;

return m_value;

end;

end FormatStr;

function FormatStr2(iValue in varchar2)return varchar2

as

begin

declare

i number;

j number;

m_value varchar2(4000);

begin

m_value:=iValue;

i:=instr(iValue,'#',1,1);

if i>0 then

m_value:=substr(m_value,1,i-1);

end if;

return m_value;

end;

end FormatStr2;

end PG_ENCRYPT_DECRYPT;

create table users1(

userid varchar2(50)

PRimary key,

passWord varchar2(4000),  --密码原文

encrypted varchar2(4000)  --加密后的密码

);

insert into users1 values ('user1','user1234yyyy',null);

insert into users1 values ('user2','abcd1234yy',null);

insert into users1 values ('user3','oracle12yyy',null);

update users1 set encrypted = PG_ENCRYPT_DECRYPT.ENCRYPT_3KEY_MODE(password,1);--加密

select * from users1;--正常查看

select userid,password,PG_ENCRYPT_DECRYPT.DECRYPT_3KEY_MODE(encrypted,1) DECRYPTED from users1;--解密后查看