Oracle dbms_crypto加密解密包介绍

最新推荐文章于 2023-11-03 14:42:25 发布

xiaolong2230

最新推荐文章于 2023-11-03 14:42:25 发布

阅读量1.8k

点赞数

分类专栏：数据库文章标签： oracle dbms_crypto

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

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)

子程序参数类型
在这里插入图片描述

不能直接加密varchar2类型，需要转为RAW类型后再加密，互相转换语法为：

UTL_I18N.RAW_TO_CHAR (data, 'AL32UTF8');
UTL_I18N.STRING_TO_RAW (string, 'AL32UTF8');

DECRYPT子程序汇总见下图（官方地址：https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_crypto.htm#i1004271）
在这里插入图片描述

语法如下：
DECRYPT Function

DBMS_CRYPTO.DECRYPT(
   src IN RAW,
   typ IN PLS_INTEGER,
   key IN RAW,
   iv  IN RAW          DEFAULT NULL)
 RETURN RAW;

参数解释：
在这里插入图片描述

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;  
/

实例：

加密方法：

create or replace function xxdba.f_string_encrypt(string_in in varchar2)
  return raw is
  string_in_raw RAW(1000) := UTL_I18N.STRING_TO_RAW(string_in, 'AL32UTF8');
  key_string    varchar2(32) := 'xxxxx1234567890xxxx!@#$%^';
  key_raw       RAW(128) := UTL_RAW.cast_to_raw(key_string);
  iv_string     varchar2(32) := '12345678';
  iv_raw        RAW(128) := UTL_RAW.cast_to_raw(iv_string);
  encrypted_raw RAW(1000);
begin
  encrypted_raw := dbms_crypto.Encrypt(src => string_in_raw,
                                       typ => DBMS_CRYPTO.DES_CBC_PKCS5,
                                       key => key_raw,
                                       iv  => iv_raw);
  return encrypted_raw;
end;

解密方法：

create or replace function xxdba.f_string_decrypt(encrypted_raw IN RAW)
  return varchar2 is
  decrypted_raw raw(1000);
  key_string    varchar2(32) := 'xxxxx1234567890xxxx!@#$%^';
  key_raw       RAW(128) := UTL_RAW.cast_to_raw(key_string);
  iv_string     varchar2(32) := '12345678';
  iv_raw        RAW(128) := UTL_RAW.cast_to_raw(iv_string);
begin
  decrypted_raw := DBMS_CRYPTO.DECRYPT(src => encrypted_raw,
                                       typ => DBMS_CRYPTO.DES_CBC_PKCS5,
                                       key => key_raw,
                                       iv  => iv_raw);
  return UTL_I18N.RAW_TO_CHAR(decrypted_raw, 'AL32UTF8');
END;

本文转自：https://blog.csdn.net/dazuiba008/article/details/79651119