oracle从10gR2版本开始支持这个包,利用这个函数可以对字段进行加减密。
包括可以给RAW和LOB类型的字段加密和解密,比如声音和图片,支持以下加密算法Data Encryption Standard (DES), Triple DES (3DES, 2-key and 3-key)
Advanced Encryption Standard (AES)
MD5, MD4, and SHA-1 cryptographic hashes
MD5 and SHA-1 Message Authentication Code (MAC)
子程序参数类型
Type | Description |
---|---|
|
A source or destination binary LOB |
|
A source or destination character LOB (excluding NCLOB) |
|
Specifies a cryptographic algorithm type (used with BLOB, CLOB, and RAW datatypes) |
|
A source or destination RAW buffer |
不能直接加密varchar2类型,需要转为RAW类型后再加密,互相转换语法为:
UTL_I18N.RAW_TO_CHAR (data, 'AL32UTF8');
UTL_I18N.STRING_TO_RAW (string, 'AL32UTF8');
DECRYPT子程序汇总见下图
Subprogram | Description |
---|---|
DECRYPT Function |
Decrypts |
DECRYPT Procedures |
Decrypts |
ENCRYPT Function |
Encrypts |
ENCRYPT Procedures |
Encrypts |
HASH Function |
Applies one of the supported cryptographic hash algorithms (MD4, MD5, or SHA-1) to data |
MAC Function |
Applies Message Authentication Code algorithms (MD5 or SHA-1) to data to provide keyed message protection |
RANDOMBYTES Function |
Returns a |
RANDOMINTEGER Function |
Returns a random |
RANDOMNUMBER Function |
Returns a random 128-bit integer of the |
语法如下:
DECRYPT Function
DBMS_CRYPTO.DECRYPT( src IN RAW, typ IN PLS_INTEGER, key IN RAW, iv IN RAW DEFAULT NULL) RETURN RAW;
参数解释:
Parameter Name | Description |
---|---|
|
要被解密的RAW数据 |
|
使用的加密类型 |
|
解密使用的KEY |
iv |
块密码初始化向量,默认为NULL |
ENCRYPT Function
DBMS_CRYPTO.ENCRYPT( src IN RAW, typ IN PLS_INTEGER, key IN RAW, iv IN RAW DEFAULT NULL) RETURN RAW;
以此函数为基础,举一个实例:
CREATE TABLE KEYINFOMTBL(
KEYCODE RAW(32) NOT NULL,
CONSTRAINT KEYINFOMTBL_P PRIMARY KEY (
KEYCODE)
USING INDEX
)
/
INSERT INTO KEYINFOMTBL VALUES ( DBMS_CRYPTO.RANDOMBYTES (32) );
加密函数
CREATE OR REPLACE FUNCTION F_ENCRYPT
(
INPUT_STRING VARCHAR2
)
RETURN RAW
IS
ENCRYPTED_RAW RAW (100);
KEY_BYTES_RAW RAW (32);
ENCRYPTION_TYPE PLS_INTEGER :=
DBMS_CRYPTO.ENCRYPT_AES256
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN
SELECT KEYCODE INTO KEY_BYTES_RAW FROM KEYINFOMTBL;
ENCRYPTED_RAW := DBMS_CRYPTO.ENCRYPT
(
SRC => UTL_I18N.STRING_TO_RAW (INPUT_STRING, 'AL32UTF8'),
TYP => ENCRYPTION_TYPE,
KEY => KEY_BYTES_RAW
);
RETURN ENCRYPTED_RAW;
END;
/
解密函数:
CREATE OR REPLACE FUNCTION F_DECRYPT
(
INPUT_RAW RAW
)
RETURN VARCHAR2
IS
OUTPUT_STRING VARCHAR2(100);
DECRYPTED_RAW RAW (100);
KEY_BYTES_RAW RAW (32);
ENCRYPTION_TYPE PLS_INTEGER :=
DBMS_CRYPTO.ENCRYPT_AES256
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN
SELECT KEYCODE INTO KEY_BYTES_RAW FROM KEYINFOMTBL;
DECRYPTED_RAW := DBMS_CRYPTO.DECRYPT
(
SRC => INPUT_RAW,
TYP => ENCRYPTION_TYPE,
KEY => KEY_BYTES_RAW
);
OUTPUT_STRING := UTL_I18N.RAW_TO_CHAR (DECRYPTED_RAW, 'AL32UTF8');
RETURN OUTPUT_STRING;
END;
/
数据加密解密:
create table t_encrypt_tab
(id number, name varchar2(20),en_name raw(128)) ;
insert into t_encrypt_tab (id,name) select object_id,object_name from dba_objects where rownum<10;
select * from t_encrypt_tab;
ID NAME EN_NAME
---------- -------------------- --------------------------------------------------
20 ICOL$
44 I_USER1
28 CON$
15 UNDO$
29 C_COBJ#
3 I_OBJ#
25 PROXY_ROLE_DATA$
39 I_IND1
51 I_CDEF2
9 rows selected.
update t_encrypt_tab set en_name=F_ENCRYPT(name);
9 rows updated.
select * from t_encrypt_tab;
ID NAME EN_NAME
---------- -------------------- --------------------------------------------------
20 ICOL$ 1E25F9801E5C0D825FCDDC45B140F296
44 I_USER1 CB4F3B270E846A4A1FE001BD315C4955
28 CON$ A94584B4B378D3707F0E042E5F4F7D9F
15 UNDO$ 256F436472140002C26E353644EDFB6E
29 C_COBJ# D4A047EE4D93865E14A1F3CA5AB5D6FC
3 I_OBJ# 3EA0676355828A105D1B4AD5F485E9F8
25 PROXY_ROLE_DATA$ B390040207C3F7F3B5029ADD3D6A9147E48A516BB93E5BCE5A
CB9E80CD537386
39 I_IND1 BC0FC96C70A3D35BC1DD3C244646B319
51 I_CDEF2 49DD0A82B2AACAA180F57DA4F1A3DC1D
9 rows selected.
select id,name,f_decrypt(EN_NAME) de_name,en_name from t_encrypt_tab;
ID NAME DE_NAME EN_NAME
---------- -------------------- -------------------------------------------------- --------------------------------------------------
20 ICOL$ ICOL$ 1E25F9801E5C0D825FCDDC45B140F296
44 I_USER1 I_USER1 CB4F3B270E846A4A1FE001BD315C4955
28 CON$ CON$ A94584B4B378D3707F0E042E5F4F7D9F
15 UNDO$ UNDO$ 256F436472140002C26E353644EDFB6E
29 C_COBJ# C_COBJ# D4A047EE4D93865E14A1F3CA5AB5D6FC
3 I_OBJ# I_OBJ# 3EA0676355828A105D1B4AD5F485E9F8
25 PROXY_ROLE_DATA$ PROXY_ROLE_DATA$ B390040207C3F7F3B5029ADD3D6A9147E48A516BB93E5BCE5A
CB9E80CD537386
39 I_IND1 I_IND1 BC0FC96C70A3D35BC1DD3C244646B319
51 I_CDEF2 I_CDEF2 49DD0A82B2AACAA180F57DA4F1A3DC1D
9 rows selected.
RANDOMINTEGER Function
随机返回一个整型数
RANDOMBYTES Function
随机返回一个类型为RAW的值,通常用来作为加密KEY的生成手段
RANDOMNUMBER Function
随机返回一个正数,范围在0..2**128-1
举例:
SQL> select DBMS_CRYPTO.RANDOMINTEGER from dual;
RANDOMINTEGER
-------------
293305514
SQL> select DBMS_CRYPTO.RANDOMBYTES(32) from dual;
DBMS_CRYPTO.RANDOMBYTES(32)
--------------------------------------------------------------------------------
3042DF993F824904D84E260D650177DA1EB3613E4F02B04A50E039B4756B59BD
SQL> select DBMS_CRYPTO.RANDOMNUMBER from dual;
RANDOMNUMBER
------------
1.9505E+38
另注:
另外几种加密算法的加密函数举例
CREATE OR REPLACE FUNCTION F_ENCRYPT
(
INPUT_STRING VARCHAR2
)
RETURN RAW
IS
ENCRYPTED_RAW RAW (100);
KEY_BYTES_RAW RAW (32);
ENCRYPTION_TYPE PLS_INTEGER := DBMS_CRYPTO.HASH_MD5;
BEGIN
SELECT KEYCODE INTO KEY_BYTES_RAW FROM KEYINFOMTBL;
ENCRYPTED_RAW := DBMS_CRYPTO.Hash
(
SRC => UTL_I18N.STRING_TO_RAW (INPUT_STRING, 'AL32UTF8'),
TYP => ENCRYPTION_TYPE
);
RETURN ENCRYPTED_RAW;
END;
/
CREATE OR REPLACE FUNCTION F_ENCRYPT
(
INPUT_STRING VARCHAR2
)
RETURN RAW
IS
ENCRYPTED_RAW RAW (100);
KEY_BYTES_RAW RAW (32);
ENCRYPTION_TYPE PLS_INTEGER := DBMS_CRYPTO.HMAC_SH1;
BEGIN
SELECT KEYCODE INTO KEY_BYTES_RAW FROM KEYINFOMTBL;
ENCRYPTED_RAW := DBMS_CRYPTO.MAC
(
SRC => UTL_I18N.STRING_TO_RAW (INPUT_STRING, 'AL32UTF8'),
TYP => ENCRYPTION_TYPE,
KEY => KEY_BYTES_RAW
);
RETURN ENCRYPTED_RAW;
END;
/
CREATE OR REPLACE FUNCTION F_ENCRYPT
(
INPUT_STRING VARCHAR2
)
RETURN RAW
IS
ENCRYPTED_RAW RAW (100);
KEY_BYTES_RAW RAW (32);
ENCRYPTION_TYPE PLS_INTEGER := DBMS_CRYPTO.DES3_CBC_PKCS5;
BEGIN
SELECT KEYCODE INTO KEY_BYTES_RAW FROM KEYINFOMTBL;
ENCRYPTED_RAW := DBMS_CRYPTO.ENCRYPT
(
SRC => UTL_I18N.STRING_TO_RAW (INPUT_STRING, 'AL32UTF8'),
TYP => ENCRYPTION_TYPE,
KEY => KEY_BYTES_RAW
);
RETURN ENCRYPTED_RAW;
END;
/