Oracle9i Supplied PL/SQL Packages and Types Reference. 加密(encrypt)解密(decrypt)是采用 Oracle DBMS_OBFUSCATION_TOOLKIT package.
[@more@]
利用这个包,我们可以对数据进行DES,Triple DES或者MD5加密.
DESGETKEY -- 产生密钥,用于DES算法
DES3GETKEY -- 产生密钥,用于Triple DES算法
DESENCRYPT -- 用DES算法加密数据
DESDECRYPT -- 用DES算法解密数据
DES3ENCRYPT -- 用Triple DES算法加密数据
DES3DECRYPT -- 用DES算法解密数据
MD5 -- 用MD5算法加密数据
Triple DES (3DES) is a far stronger cipher than DES; the resulting ciphertext (encrypted data) is much harder to break using an exhaustive search: 2**112 or 2**168 attempts instead of 2**56 attempts 这是怎么样的一个概念呢? 以现在的计算机计算能力来说吧,
uppose you build a computer capable of making 1000 attempts each second. How long would it take to exhaust 2 to the 56 (256) attempts? it will go supernova many billions of years before you'll finish.
下面看看对字符串: password 加密的过程:
DECLARE
input_string VARCHAR2(16) := 'password';
key_string VARCHAR2(8) := 'oracle9i';
encrypted_string VARCHAR2(2048);
decrypted_string VARCHAR2(2048);
error_in_input_buffer_length EXCEPTION;
PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :='*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES ***';
BEGIN
dbms_output.put_line('> ========= BEGIN TEST =========');
dbms_output.put_line('> Input string : ' ||
input_string);--BEGIN
dbms_obfuscation_toolkit.DESEncrypt(
input_string => input_string,
key_string => key_string,
encrypted_string => encrypted_string );
dbms_output.put_line('> Encrypted string : ' ||
encrypted_string);-- Add DESDecrypt as shown, change raw to key_string
dbms_obfuscation_toolkit.DESDecrypt(
input_string => encrypted_string,
key_string => key_string,
decrypted_string => decrypted_string);
dbms_output.put_line('> Decrypted output : ' ||
decrypted_string);
dbms_output.put_line('> ');
if input_string =
decrypted_string THEN
dbms_output.put_line('> DES Encryption and Decryption successful');
END IF;
EXCEPTION
WHEN error_in_input_buffer_length THEN
dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
END;
运行的结果:
> ========= BEGIN TEST =========
> Input string : password
> Encrypted string : .]%.‡—I
> Decrypted output : password
>
> DES Encryption and Decryption successful
这里的encrypted string不同的sql/plus版本是不同的结果的,因为字符集不同,这里必段要注意:加密的字符串(input_string)必须是8的倍数哦,其实加密后的字符串也是8的倍数,如果不是的话,结果就是:
> ========= BEGIN TEST =========
> Input string : passwo1rd
> *** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES ***
再来看一个例子,采用raw和string的加密比较: DECLARE input_string VARCHAR2(16) := 'tigertigertigert'; raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(input_string); key_string VARCHAR2(8) := 'scottsco'; raw_key RAW(128) := UTL_RAW.CAST_TO_RAW(key_string); encrypted_raw RAW(2048); encrypted_string VARCHAR2(2048); decrypted_raw RAW(2048); decrypted_string VARCHAR2(2048); error_in_input_buffer_length EXCEPTION; PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232); INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) := '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING EXCEPTION ***'; double_encrypt_not_permitted EXCEPTION; PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233); DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) := '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***'; -- 1. Begin testing raw data encryption and decryption BEGIN dbms_output.put_line('> ========= BEGIN TEST RAW DATA ========='); dbms_output.put_line('> Raw input : ' || UTL_RAW.CAST_TO_VARCHAR2(raw_input)); BEGIN dbms_obfuscation_toolkit.DESEncrypt(input => raw_input, key => raw_key, encrypted_data => encrypted_raw ); dbms_output.put_line('> encrypted hex value : ' || rawtohex(encrypted_raw)); dbms_obfuscation_toolkit.DESDecrypt(input => encrypted_raw, key => raw_key, decrypted_data => decrypted_raw); dbms_output.put_line('> Decrypted raw output : ' || UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw)); dbms_output.put_line('> '); if UTL_RAW.CAST_TO_VARCHAR2(raw_input) = UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw) THEN dbms_output.put_line('> Raw DES Encyption and Decryption successful'); END if; EXCEPTION WHEN error_in_input_buffer_length THEN dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG); END; dbms_output.put_line('> '); -- 2. Begin testing string data encryption and decryption dbms_output.put_line('> ========= BEGIN TEST STRING DATA ========='); BEGIN dbms_output.put_line('> input string : ' || input_string); dbms_obfuscation_toolkit.DESEncrypt( input_string => input_string, key_string => key_string, encrypted_string => encrypted_string ); dbms_output.put_line('> encrypted hex value : ' || rawtohex(UTL_RAW.CAST_TO_RAW(encrypted_string))); dbms_obfuscation_toolkit.DESDecrypt( input_string => encrypted_string, key_string => key_string, decrypted_string => decrypted_string ); dbms_output.put_line('> decrypted string output : ' || decrypted_string); if input_string = decrypted_string THEN dbms_output.put_line('> String DES Encyption and Decryption successful'); END if; EXCEPTION WHEN error_in_input_buffer_length THEN dbms_output.put_line(' ' || INPUT_BUFFER_LENGTH_ERR_MSG); END; dbms_output.put_line('> '); END; 运行的结果如下: > ========= BEGIN TEST RAW DATA ========= > Raw input : tigertigertigert > encrypted hex value : 5AAB8C0D278AD75CA1968790D00FD75A > Decrypted raw output : tigertigertigert > > Raw DES Encyption and Decryption successful > > ========= BEGIN TEST STRING DATA ========= > input string : tigertigertigert > encrypted hex value : 5AAB8C0D278AD75CA1968790D00FD75A > decrypted string output : tigertigertigert > String DES Encyption and Decryption successful > 其实处理方法和前一个例子是一样的.
在存储过程中的实现: create table cc_table( empno varchar2(64), cc_no varchar2(64) ) ; insert into cc_table values('password','password'); select * from cc_table 1 password password 存储过程如下: CREATE OR REPLACE PROCEDURE decrypt_cc_no (empno varchar2) IS key_string VARCHAR2(8) := 'scottsco'; encrypted_string VARCHAR2(2048); decrypted_string VARCHAR2(2048); BEGIN select cc_no into encrypted_string from cc_table where empno = empno; dbms_obfuscation_toolkit.DESDecrypt( input_string => encrypted_string, key_string => key_string, decrypted_string => decrypted_string); update cc_table set cc_no = decrypted_string where empno = empno; commit; END; select * from cc_table; 1 password
再来看看一个采用des3(triple) 的加密过程: 先准备好表和数据: create table TEST_NIEGC ( USERID VARCHAR2(100), PASSWORD VARCHAR2(100), ENCRYPTED VARCHAR2(100) ); insert into test_niegc values('1','niegc','user1234'); insert into test_niegc values('2','ngc','user1234'); commit; 创建包PG_ENCRYPT_DECRYPT_NIEGC create or replace package PG_ENCRYPT_DECRYPT_NIEGC 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_NIEGC 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 --vEncrypted := 'aaaaaaaaa'; vEncryptedRaw := UTL_RAW.CAST_TO_RAW(vEncrypted); return vEncryptedRaw; end; end; / 在sql/plus执行: update test_niegc a set a.encrypted=pg_encrypt_decrypt_niegc.ENCRYPT_3KEY_MODE(a.encrypted,1); commit; select * from test_niegc 1 niegc 69EF3A211A0F2C32 2 ngc 69EF3A211A0F2C32 update test_niegc a set a.encrypted=pg_encrypt_decrypt_niegc.DECRYPT_3KEY_MODE(a.encrypted,1); select * from test_niegc; 1 1 niegc user1234 2 2 ngc user1234 是DES算法和Triple DES算法的特征之一. 输入长度必须是8的倍数, 而输出也是8的倍数,所以我们的字段长度也是8的倍数. 如果输入不是8的倍数,会报错的哦. 这里的密钥: iKey varchar2(8):='oracle9i'; 就是了.所以如果人家知道了密钥当然可以解密了.可以采用把这个package 再加密,这样人家就比较难看到密钥了.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9099175/viewspace-904554/,如需转载,请注明出处,否则将追究法律责任。