Oracle dbms_crypto加密解密包介绍

你的名字 2024-02-19 21:09 8阅读 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)

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

不能直接加密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）][https_docs.oracle.com_cd_B19306_01_appdev.102_b14258_d_crypto.htm_i1004271]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9sb25nMjIzMA_size_16_color_FFFFFF_t_70 1]

语法如下：  
DECRYPT Function

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

参数解释：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9sb25nMjIzMA_size_16_color_FFFFFF_t_70 2]

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][https_blog.csdn.net_dazuiba008_article_details_79651119]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9sb25nMjIzMA_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/a3adec2aa2744a62bbe3295d2cf2d9b9.png
[https_docs.oracle.com_cd_B19306_01_appdev.102_b14258_d_crypto.htm_i1004271]: https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_crypto.htm#i1004271%EF%BC%89
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9sb25nMjIzMA_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/55dd1220661b4dc593d305ffcd319457.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9sb25nMjIzMA_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/7c3804e554c24e5ba2bc3e4a37900247.png
[https_blog.csdn.net_dazuiba008_article_details_79651119]: https://blog.csdn.net/dazuiba008/article/details/79651119