oralce 数据加密、解密

最新推荐文章于 2022-10-10 16:54:13 发布

rskunique

最新推荐文章于 2022-10-10 16:54:13 发布

阅读量328

点赞数

分类专栏： oracle 语句

本文链接：https://blog.csdn.net/rskunique/article/details/76020103

版权

oracle 语句专栏收录该内容

7 篇文章 0 订阅

订阅专栏

/*

加密、解密包

*/

CREATE OR REPLACE PACKAGE cux_fnd_encrypt_pkg IS
  FUNCTION pswd_encrypt(p_pswd IN VARCHAR2, p_key IN VARCHAR2)
    RETURN VARCHAR2;
  FUNCTION pswd_decrypt(p_pswd IN VARCHAR2, p_key IN VARCHAR2)
    RETURN VARCHAR2;
  FUNCTION encode_base64(p_clob_in IN CLOB) RETURN CLOB;
  FUNCTION decode_base64(p_clob_in IN CLOB) RETURN CLOB;
  PROCEDURE write_to_clob(p_buffer IN VARCHAR2, x_clob IN OUT CLOB);
END;
CREATE OR REPLACE PACKAGE BODY cux_fnd_encrypt_pkg IS
  g_crypto_type PLS_INTEGER := dbms_crypto.encrypt_aes256 +
                               dbms_crypto.chain_cbc +
                               dbms_crypto.pad_pkcs5;
  FUNCTION trim_clob(p_clob_in IN CLOB) RETURN CLOB
  
   IS
    v_offset         INTEGER;
    v_buffer_size    BINARY_INTEGER := 4800;
    v_buffer_varchar VARCHAR2(4800);
    v_buffer_raw     RAW(4800);
    l_clob_x         CLOB;
    v_clob           CLOB;
  BEGIN
    dbms_lob.createtemporary(v_clob, TRUE);
    v_offset := 1;
    FOR i IN 1 .. ceil(dbms_lob.getlength(p_clob_in) / v_buffer_size) LOOP
      dbms_lob.read(p_clob_in, v_buffer_size, v_offset, v_buffer_varchar);
      v_buffer_varchar := REPLACE(REPLACE(v_buffer_varchar, chr(10), ''),
                                  chr(13),
                                  '');
      dbms_lob.writeappend(v_clob,
                           length(v_buffer_varchar),
                           v_buffer_varchar);
      v_offset := v_offset + v_buffer_size;
    END LOOP;
    l_clob_x := v_clob;
    dbms_lob.freetemporary(v_clob);
  
    RETURN l_clob_x;
  END trim_clob;
  FUNCTION pswd_encrypt(p_pswd IN VARCHAR2, p_key IN VARCHAR2)
    RETURN VARCHAR2 IS
    l_key_word       VARCHAR2(100);
    l_encrypted_pswd VARCHAR2(240);
    l_pswd_raw       RAW(2000);
  BEGIN
    IF lengthb(p_key) >= 32 THEN
      l_key_word := substrb(p_key, 1, 32);
    ELSE
      l_key_word := substrb(rpad(p_key, 32, '0'), 1, 32);
    END IF;
  
    l_pswd_raw       := dbms_crypto.encrypt(src => utl_raw.cast_to_raw(p_pswd),
                                            typ => g_crypto_type,
                                            key => utl_raw.cast_to_raw(l_key_word));
    l_encrypted_pswd := l_pswd_raw;
  
    RETURN l_encrypted_pswd;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END pswd_encrypt;
  FUNCTION pswd_decrypt(p_pswd IN VARCHAR2, p_key IN VARCHAR2)
    RETURN VARCHAR2
  
   IS
    l_key_word       VARCHAR2(100);
    l_encrypted_pswd VARCHAR2(240);
    l_pswd_raw       RAW(2000);
    l_raw            RAW(2000) := p_pswd;
  BEGIN
    IF lengthb(p_key) >= 32 THEN
      l_key_word := substrb(p_key, 1, 32);
    ELSE
      l_key_word := substrb(rpad(p_key, 32, '0'), 1, 32);
    END IF;
  
    l_pswd_raw       := dbms_crypto.decrypt(src => l_raw,
                                            typ => g_crypto_type,
                                            key => utl_raw.cast_to_raw(l_key_word));
    l_encrypted_pswd := utl_raw.cast_to_varchar2(l_pswd_raw);
  
    RETURN l_encrypted_pswd;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END pswd_decrypt;
  FUNCTION encode_base64(p_clob_in IN CLOB) RETURN CLOB
  
   IS
    l_dest_blob    BLOB;
    l_amount       NUMBER;
    l_dest_offset  NUMBER := 1;
    l_src_offset   NUMBER := 1;
    l_lang_context NUMBER := dbms_lob.default_lang_ctx;
    l_warning      NUMBER;
  
    v_clob           CLOB;
    v_result         CLOB;
    v_offset         INTEGER;
    v_chunk_size     BINARY_INTEGER := (4800 / 4) * 3;
    v_buffer_varchar VARCHAR2(7200);
    v_buffer_raw     RAW(7200);
  BEGIN
    IF p_clob_in IS NULL THEN
      RETURN NULL;
    END IF;
  
    dbms_lob.createtemporary(l_dest_blob, TRUE);
  
    l_amount      := dbms_lob.getlength(p_clob_in);
    l_dest_offset := 1;
    l_src_offset  := 1;
    dbms_lob.converttoblob(dest_lob     => l_dest_blob,
                           src_clob     => p_clob_in,
                           amount       => l_amount,
                           dest_offset  => l_dest_offset,
                           src_offset   => l_src_offset,
                           blob_csid    => nls_charset_id('UTF8'),
                           lang_context => l_lang_context,
                           warning      => l_warning);
  
    dbms_lob.createtemporary(v_clob, TRUE);
    v_offset := 1;
    FOR i IN 1 .. ceil(dbms_lob.getlength(l_dest_blob) / v_chunk_size) LOOP
      dbms_lob.read(l_dest_blob, v_chunk_size, v_offset, v_buffer_raw);
      v_buffer_raw     := utl_encode.base64_encode(v_buffer_raw);
      v_buffer_varchar := utl_raw.cast_to_varchar2(v_buffer_raw);
      dbms_lob.writeappend(v_clob,
                           length(v_buffer_varchar),
                           v_buffer_varchar);
      v_offset := v_offset + v_chunk_size;
    END LOOP;
    v_result := v_clob;
    dbms_lob.freetemporary(v_clob);
    RETURN v_result;
  END encode_base64;

  FUNCTION decode_base64(p_clob_in IN CLOB) RETURN CLOB IS
    v_blob           BLOB;
    v_result         BLOB;
    v_offset         INTEGER;
    v_buffer_size    BINARY_INTEGER := 4800;
    v_buffer_varchar VARCHAR2(4800);
    v_buffer_raw     RAW(4800);
  
    l_dest_clob    CLOB;
    l_amount       NUMBER;
    l_dest_offset  NUMBER := 1;
    l_src_offset   NUMBER := 1;
    l_lang_context NUMBER := dbms_lob.default_lang_ctx;
    l_warning      NUMBER;
  
    l_clob_x CLOB;
  BEGIN
    IF p_clob_in IS NULL THEN
      RETURN NULL;
    END IF;
  
    l_clob_x := trim_clob(p_clob_in);
  
    dbms_lob.createtemporary(v_blob, TRUE);
    v_offset := 1;
    FOR i IN 1 .. ceil(dbms_lob.getlength(l_clob_x) / v_buffer_size) LOOP
      dbms_lob.read(l_clob_x, v_buffer_size, v_offset, v_buffer_varchar);
      v_buffer_raw := utl_raw.cast_to_raw(v_buffer_varchar);
      v_buffer_raw := utl_encode.base64_decode(v_buffer_raw);
      dbms_lob.writeappend(v_blob,
                           utl_raw.length(v_buffer_raw),
                           v_buffer_raw);
      v_offset := v_offset + v_buffer_size;
    END LOOP;
    v_result := v_blob;
    dbms_lob.freetemporary(v_blob);
  
    l_amount := dbms_lob.getlength(v_result);
  
    dbms_lob.createtemporary(l_dest_clob, TRUE);
    l_dest_offset := 1;
    l_src_offset  := 1;
    dbms_lob.converttoclob(dest_lob     => l_dest_clob,
                           src_blob     => v_result,
                           amount       => l_amount,
                           dest_offset  => l_dest_offset,
                           src_offset   => l_src_offset,
                           blob_csid    => nls_charset_id('UTF8'),
                           lang_context => l_lang_context,
                           warning      => l_warning);
    RETURN l_dest_clob;
  END decode_base64;
  PROCEDURE write_to_clob(p_buffer IN VARCHAR2, x_clob IN OUT CLOB) IS
    v_writing_position NUMBER := 0;
    v_amount           NUMBER;
  BEGIN
    v_amount           := length(p_buffer);
    v_writing_position := dbms_lob.getlength(x_clob) + 1;
    dbms_lob.write(x_clob, v_amount, v_writing_position, p_buffer);
  END;
END;

/*

测试1适用于加密、解密密码

*/

 DECLARE
  l_encrypt VARCHAR2(200);
  l_decrypt VARCHAR2(200);
BEGIN
  l_encrypt := cux_fnd_encrypt_pkg.pswd_encrypt(p_pswd => '123456789',
                                         p_key  => 'test');
  dbms_output.put_line(l_encrypt);
  l_decrypt := cux_fnd_encrypt_pkg.pswd_decrypt(p_pswd => l_encrypt,
                                         p_key  => 'test');
  dbms_output.put_line(l_decrypt);
END;

/*

测试2 适用于加密、解密内容

*/

DECLARE
  l_encrypt VARCHAR2(200);
  l_decrypt VARCHAR2(200);
BEGIN
  l_encrypt := cux_fnd_encrypt_pkg.encode_base64('123456789');
  dbms_output.put_line(l_encrypt);
  l_decrypt := cux_fnd_encrypt_pkg.decode_base64(l_encrypt);
  dbms_output.put_line(l_decrypt);
END;