oracle从8i开始提供一个数据加密包:dbms_obfuscation_toolkit.利用这个包,我们可以对数据进行DES,Triple DES或者MD5加密. 本文就此讲解如何使用以及使用过程需要注意的问题.
1. dbms_obfuscation_toolkit简介
dbms_obfuscation_toolkit主要有一下几个存储过程:-
DESGETKEY -- 产生密钥,用于DES算法 DES3GETKEY -- 产生密钥,用于Triple DES算法 DESENCRYPT -- 用DES算法加密数据 DESDECRYPT -- 用DES算法解密数据 DES3ENCRYPT -- 用Triple DES算法加密数据 DES3DECRYPT -- 用DES算法解密数据 MD5 -- 用MD5算法加密数据
2. 准备数据表
在开始前,我们先创建表users:
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;
3. 创建包PG_ENCRYPT_DECRYPT
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;
4. 测试 在SQL Plus下输入:
SQL > update users set encrypted = PG_ENCRYPT_DECRYPT.ENCRYPT_3KEY_MODE(password,1); SQL > commit;
执行完以上SQL语句后,encrypted 存储的就是加密后的password字段.我们看一下结果:-
SQL > select * from users;
USERID PASSWORD ENCRYPTED ------ --------- ---------------- user1 user1234 69EF3A211A0F2C32 user2 abcd1234 CF7562203F6CEDE5 user3 oracle12 65D71D7148FA001D
这个加密结果是否正确? 我们对加密结果解密就知道了,在SQL Plus下输入:
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
大家可以看到,解密结果和密码原文完全一模一样.这说明我们的加密解密过程是正确的.
5. 进一步思考
我们再看一下表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);
以及加密输出结果:- [/code] USERID PASSWORD ENCRYPTED ------ --------- ---------------- user1 user1234 69EF3A211A0F2C32 user2 abcd1234 CF7562203F6CEDE5 user3 oracle12 65D71D7148FA001D [/code]
不知细心的朋友注意到没有? 在表中,password 和 encrypted 的长度都是64, 都是8的倍数, 再看一下我们的密码原文和加密后的密码也是8的倍数,这不是 巧合,而是DES算法和Triple DES算法的特征之一. 输入长度必须是8的倍数, 而输出也是8的倍数,所以我们的字段长度也是8的倍数. 如果输入不是8的倍数 会怎样? 大家可以把密码原文修改一下试试.
6. 密钥的保存
不管我们用什么样的加密算法,有一点非常重要的是: 密钥的保存. 密钥就是一把钥匙,因为加密算法是公开的,所以你无论如何加密, 只要我知道你的密钥,我就可以解密,那么你的加密就没有效果. 在本文中, 我们的密钥是这样定义的:-
iKey varchar2(8):='oracle9i';
oracle9i就是我们的密钥. 所以,如果只是简单地把以上程序在oracle上运行一下就使用,那么任何有权限登陆 的人看到这个程序,就可以知道密钥. 所以简单的做法是利用Oracle提供的WRAP 把整个程序加密,用加密后的文本创建程序. 这样别人就看不到你的源代码了. 把程序保存为source.sql,在Dos命令下输入:-
Wrap iname=source.sql oname=target.sql
就可以了,然后SQL Plus运行target.sql. 当然了, 这里讲的密钥保存还是很简单的. 并不是百分百保险. 大家可以自己 想想如何更安全地保持你的密钥.
oracle 10g 加密包改为:DBMS_CRYPTO General Information | Source | {ORACLE_HOME}/rdbms/admin/dbmsobtk.sql | Algorithm Constants | Name | Data Type | Value | Hash Functions | HASH_MD4 (128 bit hash) | PLS_INTEGER | 1 | HASH_MD5 (128 bit hash) | PLS_INTEGER | 2 | HASH_SH1 (160 bit hash) | PLS_INTEGER | 3 | MAC Functions | HMAC_MD5 (128 bit hash) | PLS_INTEGER | 1 | HMAC_SH1 (160 bit hash) | PLS_INTEGER | 2 | Block Cipher Algorithms | ENCRYPT_DES (56 bit) | PLS_INTEGER | 1; -- 0x0001 | ENCRYPT_3DES_2KEY (128 bit) | PLS_INTEGER | 2; -- 0x0002 | ENCRYPT_3DES | PLS_INTEGER | 3; -- 0x0003 | ENCRYPT_AES128 (128 bit) | PLS_INTEGER | 6; -- 0x0006 | ENCRYPT_AES192 (192 bit) | PLS_INTEGER | 7; -- 0x0007 | ENCRYPT_AES256 (256 bit) | PLS_INTEGER | 8; -- 0x0008 | ENCRYPT_RC4 (Stream Cipher) | PLS_INTEGER | 129; -- 0x0081 | Block Cipher Chaining Modifiers | CHAIN_CBC (Cipher Block Chaining) | PLS_INTEGER | 256; -- 0x0100 | CHAIN_CFB (Cipher Feedback) | PLS_INTEGER | 512; -- 0x0200 | CHAIN_ECB (Electronic cookbook) | PLS_INTEGER | 768; -- 0x0300 | CHAIN_OFB (Output Feedback) | PLS_INTEGER | 1024; -- 0x0400 | Block Cipher Padding Modifiers | PAD_PKCS5 (Complies with PKCS #5) | PLS_INTEGER | 4096; -- 0x1000 | PAD_NONE (No Dadding) | PLS_INTEGER | 8192; -- 0x2000 | PAD_ZERO (Pad with Zeros) | PLS_INTEGER | 12288; -- 0x3000 | Block Ciphers Suites | DES_CBC_PKCS5 | PLS_INTEGER | ENCRYPT_DES + CHAIN_CBC + PAD_PKCS5; | DES3_CBC_PKCS5 | PLS_INTEGER | ENCRYPT_3DES + CHAIN_CBC + PAD_PKCS5; |
| Dependencies | DBMS_CRYPTO_FFI | DECRYPTBYTES | ENCRYPTBYTES | DECRYPT | ENCRYPT | UTL_RAW |
| Exceptions | Error Code | Reason | 28827 | The specified cipher suite is not defined | 28829 | No value has been specified for the cipher suite to be used | 28233 | Source data was previously encrypted | 28234 | DES: Specified key size too short. DES keys must be at least 8 bytes (64 bits). AES: Specified key size is not supported. AES keys must be 128, 192, or 256 bits | 28239 | The encryption key has not been specified or contains a NULL value |
| | DECRYPT | Decrypt crypt text data using stream or block cipher with user supplied key and optional iv
Overload 1 | dbms_crypto.decrypt(src IN RAW, typ IN PLS_INTEGER, key IN RAW, iv IN RAW DEFAULT NULL) RETURN RAW; | See Encrypt Overload 1 demo | Overload 2 | dbms_crypto.decrypt(dst IN OUT NOCOPY BLOB, src IN BLOB, typ IN PLS_INTEGER, key IN RAW, iv IN RAW DEFAULT NULL); | | Overload 3 | dbms_crypto.decrypt (dst IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, src IN BLOB, typ IN PLS_INTEGER, key IN RAW, iv IN RAW DEFAULT NULL); | | | ENCRYPT | Encrypt plain text data using stream or block cipher with user supplied key and optional iv
Overload 1 | dbms_crypto.encrypt(src IN RAW, typ IN PLS_INTEGER, key IN RAW, iv IN RAW DEFAULT NULL) RETURN RAW; | set serveroutput on
DECLARE l_credit_card_no VARCHAR2(19) := '1234-5678-9012-3456'; l_ccn_raw RAW(128) := utl_raw.cast_to_raw(l_credit_card_no); l_key RAW(128) := utl_raw.cast_to_raw('abcdefgh');
l_encrypted_raw RAW(2048); l_decrypted_raw RAW(2048); BEGIN dbms_output.put_line('Original : ' || l_credit_card_no);
l_encrypted_raw := dbms_crypto.encrypt(l_ccn_raw, dbms_crypto.des_cbc_pkcs5, l_key);
dbms_output.put_line('Encrypted : ' || RAWTOHEX(utl_raw.cast_to_raw(l_encrypted_raw)));
l_decrypted_raw := dbms_crypto.decrypt(src => l_encrypted_raw, typ => dbms_crypto.des_cbc_pkcs5, key => l_key);
dbms_output.put_line('Decrypted : ' || utl_raw.cast_to_varchar2(l_decrypted_raw)); END; / | set serveroutput on
DECLARE enc_val RAW(2000); l_key RAW(2000); l_key_len NUMBER := 128/8; -- convert bits to bytes l_mod NUMBER := dbms_crypto.ENCRYPT_AES128 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5;
BEGIN l_key := dbms_crypto.randombytes(l_key_len);
enc_val := dbms_crypto.encrypt( utl_i18n.string_to_raw('1234-5678-9012-3456', 'AL32UTF8'), l_mod, l_key);
dbms_output.put_line(enc_val); END; / | Overload 2 | dbms_crypto.encrypt(dst IN OUT NOCOPY BLOB, src IN BLOB, typ IN PLS_INTEGER, key IN RAW, iv IN RAW DEFAULT NULL); | | Overload 3 | dbms_crypto.encrypt(dst IN OUT NOCOPY BLOB, src IN CLOB CHARACTER SET ANY_CS, typ IN PLS_INTEGER, key IN RAW, iv IN RAW DEFAULT NULL); | | dbms_crypto.encrypt(UTL_RAW.CAST_TO_RAW(CONVERT('XXX','AL32UTF8')),typ,key); | HASH | Hash source data by cryptographic hash type
Overload 1 | dbms_crypto.hash(src IN RAW, typ IN PLS_INTEGER) RETURN RAW; | | Overload 2 | dbms_crypto.hash(src IN BLOB, typ IN PLS_INTEGER) RETURN RAW; | | Overload 3 | dbms_crypto.hash(src IN CLOB CHARACTER SET ANY_CS, typ IN PLS_INTEGER) RETURN RAW; | | | MAC | Message Authentication Code algorithms provide keyed message protection
Overload 1 | dbms_crypto.mac(src IN RAW, typ IN PLS_INTEGER, key IN RAW) RETURN RAW; | | Overload 2 | dbms_crypto.mac(src IN BLOB, typ IN PLS_INTEGER, key IN RAW) RETURN RAW; | | Overload 3 | dbms_crypto.mac(src IN CLOB CHARACTER SET ANY_CS, typ IN PLS_INTEGER, key IN RAW) RETURN RAW; | | | RANDOMBYTES | Returns a raw value containing a pseudo-random sequence of bytes | dbms_crypto.randomnytes(number_bytes PLS_INTEGER) RETURN RAW; | SELECT dbms_crypto.randombytes(1) FROM dual; SELECT LENGTH(dbms_crypto.randombytes(1)) FROM dual;
SELECT dbms_crypto.randombytes(28) FROM dual; SELECT LENGTH(dbms_crypto.randombytes(28)) FROM dual;
SELECT dbms_crypto.randombytes(64) FROM dual; SELECT LENGTH(dbms_crypto.randombytes(64)) FROM dual; | | RANDOMINTEGER | Returns a random BINARY_INTEGER | dbms_crypto.randominteger RETURN NUMBER; | SELECT dbms_crypto.randominteger FROM dual; | | RANDOMNUMBER | Returns a random Oracle Number | dbms_crypto.randomnumber RETURN NUMBER; | SELECT dbms_crypto.randomnumber FROM dual; |
|