Oracle----加密解密
为了保护敏感数据,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:
SYS AS SYSDBA on 2008-03-05 15:58:26 at ORCL>create table users(
2userid varchar2(50) primary key,
3password varchar2(64),
4encrypted varchar2(64)
5);
已建立表格.
SYS AS SYSDBA on 2008-03-05 16:00:20 at ORCL>insert into users values('user1','user1234',null);
已建立1個資料列.
SYS AS SYSDBA on 2008-03-05 16:00:25 at ORCL>insert into users values('user2','abcd1234',null);
已建立1個資料列.
SYS AS SYSDBA on 2008-03-05 16:00:42 at ORCL>insert into users values('user3','oracle12',null)
已建立1個資料列.
commit;
3.创建包PG_ENCRYPT_DECRYPT
SYS AS SYSDBA on 2008-03-05 16:01:43 at ORCL>create or replace package pg_encrypt_decrypt is
2ikey varchar2(8):='oracle9i';
3function gen_raw_key(ikey in varchar2) return raw;
4function decrypt_3key_mode(ivalue in raw,imode in pls_integer) return varchar2;
5function encrypt_3key_mode(ivalue in varchar2,imode in pls_integer) return raw;
6end;
7/
已建立套裝程式.
SYS AS SYSDBA on 2008-03-05 16:29:39 at ORCL>create or replace package body pg_encrypt_decrypt
is
2function gen_raw_key(ikey in varchar2) return raw as
3rawkey raw(240):='';
4begin
5for i in 1..length(ikey) loop
6rawkey:=rawkey||hextoraw(to_char(ascii(substr(ikey,i,1))));
7end loop;
8return rawkey;
9end;
10
11function decrypt_3key_mode(ivalue in raw,imode in pls_integer) return varchar2 as
12vdecrypted varchar2(4000);
13rawkey raw(240):='';
14begin
15rawkey:=gen_raw_key(ikey);
16vdecrypted:=dbms_obfuscation_toolkit.des3decrypt(
17utl_raw.cast_to_varchar2(ivalue),key_string=>rawkey,which=>imode);
18return vdecrypted;
19end;
20
21function encrypt_3key_mode(ivalue in varchar2,imode in pls_integer) return raw
22is
23vencrypted varchar2(4000);
24vencryptedraw raw(2048);
25rawkey raw(240):='';
26begin
27rawkey:=gen_raw_key(ikey);
28vencrypted:=dbms_obfuscation_toolkit.des3encrypt(ivalue,key_string=>rawkey,which=>imode)
;
29vencryptedraw:=utl_raw.cast_to_raw(vencrypted);
30return vencryptedraw;
31end;
32end;
33
34/
已建立套裝程式主體.
SYS AS SYSDBA on 2008-03-05 16:29:39 at ORCL>create or replace package body pg_encrypt_decrypt
is
2function gen_raw_key(ikey in varchar2) return raw as
3rawkey raw(240):='';
4begin
5for i in 1..length(ikey) loop
6rawkey:=rawkey||hextoraw(to_char(ascii(substr(ikey,i,1))));
7end loop;
8return rawkey;
9end;
10
11function decrypt_3key_mode(ivalue in raw,imode in pls_integer) return varchar2 as
12vdecrypted varchar2(4000);
13rawkey raw(240):='';
14begin
15rawkey:=gen_raw_key(ikey);
16vdecrypted:=dbms_obfuscation_toolkit.des3decrypt(
17utl_raw.cast_to_varchar2(ivalue),key_string=>rawkey,which=>imode);
18return vdecrypted;
19end;
20
21function encrypt_3key_mode(ivalue in varchar2,imode in pls_integer) return raw
22is
23vencrypted varchar2(4000);
24vencryptedraw raw(2048);
25rawkey raw(240):='';
26begin
27rawkey:=gen_raw_key(ikey);
28vencrypted:=dbms_obfuscation_toolkit.des3encrypt(ivalue,key_string=>rawkey,which=>imode)
;
29vencryptedraw:=utl_raw.cast_to_raw(vencrypted);
30return vencryptedraw;
31end;
32end;
33
34/
已建立套裝程式主體.
SYS AS SYSDBA on 2008-03-05 16:29:57 at ORCL>update users set encrypted=pg_encrypt_decrypt.enc
rypt_3key_mode(password,1);
已更新3個資料列.
SYS AS SYSDBA on 2008-03-05 16:32:37 at ORCL>commit;
確認完成.
SYS AS SYSDBA on 2008-03-05 16:34:35 at ORCL>column encrypted format a20
SYS AS SYSDBA on 2008-03-05 16:34:41 at ORCL>/
USERIDPASSWORDENCRYPTED
---------- ---------- --------------------
user1user123469EF3A211A0F2C32
user2abcd1234CF7562203F6CEDE5
user3oracle1265D71D7148FA001D
SYS AS SYSDBA on 2008-03-05 16:34:42 at ORCL>select userid,password,pg_encrypt_decrypt.decrypt_3key_mode(encrypted,1) decrypted from users;
USERIDPASSWORD
---------- ----------
DECRYPTED
--------------------------------------------------------------------------------
user1user1234
user1234
user2abcd1234
abcd1234
user3oracle12
oracle12
6.密钥的保存不管我们用什么样的加密算法,有一点非常重要的是:密钥的保存.密钥就是一把钥匙,因为加密算法是公开的,所以你无论如何加密,只要我知道你的密钥,我就可以解密,那么你的加密就没有效果.在本文中,我们的密钥是这样定义的:-
iKey varchar2(8):='oracle9i';
oracle9i就是我们的密钥.所以,如果只是简单地把以上程序在oracle上运行一下就使用,那么任何有权限登陆的人看到这个程序,就可以知道密钥.所以简单的做法是利用Oracle提供的WRAP把整个程序加密,用加密后的文本创建程序.这样别人就看不到你的源代码了.把程序保存为source.sql,在Dos命令下输入:-
Wrap iname=source.sql name=target.sql
就可以了,然后SQL Plus运行target.sql.