利用Oracle的dbms_obfuscation_toolkit加密解密数据--潇洒之泉

http://luoxq.mypm.net


	公告

	登陆

	日志日历

	日志

	评论

	链接

	统计

利用Oracle的dbms_obfuscation_toolkit加密解密数据

利用Oracle的dbms_obfuscation_toolkit加密解密数据

  为了保护敏感数据,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;

luoxq 发表于 2007/10/25 15:14:00

阅读全文 | 回复(0) | 引用通告 | 编辑 | 收藏该日志

上一篇：转贴＂B/S还是C/S？国内工程企业信息化技术的新方向＂
下一篇：AIX升级编译错误（AIX 5204升级到5208）

发表评论：