分类: Oracle
2006-11-02 18:41:55
drop table users;
create table users(
userid varchar2(50) primary key,
password varchar2(64), --密码原文
encrypted varchar2(64) --加密后的密码
);
insert into users values ('user1','user1234',null);
insert into users values ('user2','abcd1234',null);
insert into users values ('user3','oracle12',null);
commit;
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;
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 := rawkey||hextoraw(to_char(ascii(substr(iKey, i, 1))));
end loop;
return rawkey;
end;
/*
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 vDecrypted;
end;
/*
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 (
iValue
, key_string => rawkey
, which => iMode
);
-- convert to raw as out
vEncryptedRaw := UTL_RAW.CAST_TO_RAW(vEncrypted);
return vEncryptedRaw;
end;
end;
SQL > update users set encrypted = PG_ENCRYPT_DECRYPT.ENCRYPT_3KEY_MODE(password,1);
SQL > commit;
SQL > select * from users;
USERID PASSWORD ENCRYPTED
------ --------- ----------------
user1 user1234 69EF3A211A0F2C32
user2 abcd1234 CF7562203F6CEDE5
user3 oracle12 65D71D7148FA001D
SQL > select userid,password,PG_ENCRYPT_DECRYPT.DECRYPT_3KEY_MODE(encrypted,1) DECRYPTED from users;
USERID PASSWORD DECRYPTED
------ --------- ----------
user1 user1234 user1234
user2 abcd1234 abcd1234
user3 oracle12 oracle12
create table users(
userid varchar2(50) primary key,
password varchar2(64), --密码原文
encrypted varchar2(64) --加密后的密码
);
insert into users values ('user1','user1234',null);
insert into users values ('user2','abcd1234',null);
insert into users values ('user3','oracle12',null);
iKey varchar2(8):='oracle9i';
Wrap iname=source.sql oname=target.sql
Trackback:
回:简单回复如下: