Oracle dbms_crypto加密解密包介绍-摩杜云开发者社区

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
`BLOB`	A source or destination binary LOB
`CLOB`	A source or destination character LOB (excluding NCLOB)
`PLS_INTEGER`	Specifies a cryptographic algorithm type (used with BLOB, CLOB, and RAW datatypes)
`RAW`	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 `RAW` data using a stream or block cipher with a user supplied key and optional IV (initialization vector)
DECRYPT Procedures	Decrypts `LOB` data using a stream or block cipher with a user supplied key and optional IV
ENCRYPT Function	Encrypts `RAW` data using a stream or block cipher with a user supplied key and optional IV
ENCRYPT Procedures	Encrypts `LOB` data using a stream or block cipher with a user supplied key and optional IV
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 `RAW` value containing a cryptographically secure pseudo-random sequence of bytes, and can be used to generate random material for encryption keys
RANDOMINTEGER Function	Returns a random `BINARY_INTEGER`
RANDOMNUMBER Function	Returns a random 128-bit integer of the `NUMBER` datatype

语法如下：

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
`src`	要被解密的RAW数据
`typ`	使用的加密类型
`key`	解密使用的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;  
/