Oracle dbms_crypto加密解密包介绍
程序员文章站
2022-05-15 14:35:14
...
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) := 'aaa@qq.com#$%^';
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) := 'aaa@qq.com#$%^';
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