oracle SQL 实现对数据库的的脱敏和对称加密

之前的 kettle ETL 太慢了

• insert into select 83w数据 220s
• kettle 83w数据 etl 3h 26w ~

功能变更耗时

• 另外如果需要再次对其他字段做脱敏 时间又比较耗时
• 需要再次编写环节

复制表

INSERT INTO XXXX
SELECT * FROM XXXX_JM;

验证数据

-- 源表总数
SELECT COUNT(*)
FROM XXXX;

-- 目标表总数
SELECT COUNT(*)
FROM XXXX_JM;

加密函数 key，至少要16位

create or replace function des3_enc(input varchar2) return varchar2
is i_data varchar2(128);
v_in varchar2(255);
i_key varchar2(128);
raw_input RAW(128) ;
key_input RAW(128) ;
decrypted_raw RAW(2048);
i_data:= input;
raw_input := UTL_RAW.CAST_TO_RAW(rpad(i_data,(trunc(length(i_data)/8)+1)*8,chr(0)));
key_input := UTL_RAW.CAST_TO_RAW('abcdef0123456789');
dbms_obfuscation_toolkit.DES3Encrypt(input => raw_input,key => key_input,encrypted_data => decrypted_raw);
return to_char(rawtohex(decrypted_raw));
end des3_enc;

解密函数

create or replace function des3_dec
(
    input varchar2
)
return varchar2
is
  i_data varchar2(2048);
i_key varchar2(2048);
v_in varchar2(2048);
i_data := input;
dbms_obfuscation_toolkit.DES3DECRYPT(input_string => UTL_RAW.CAST_TO_varchar2(i_data),key_string => 'abcdef0123456789',decrypted_string=> v_in);
v_in := rtrim(v_in,chr(0));
  return v_in;
end des3_dec;

脱敏语加密

UPDATE GIM_RENKOU_JIAMI
SET SFZH = DES3_ENC ( SFZH )
WHERE
	SFZH IS NOT NULL;

脱敏掩码

UPDATE GIM_RENKOU_JIAMI 
SET XM = RPAD( SUBSTR( XM, 1, 1 ), LENGTH( XM ), '*' ),
LXFS = SUBSTR(LXFS, 1, 3) || '****' || SUBSTR(LXFS, -4),
ZY = RPAD(SUBSTR(ZY, 1, 3), LENGTH(ZY), '*');

UPDATE GIM_RENKOU_JIAMI 
SET BM = RPAD( SUBSTR( BM, 1, 1 ), LENGTH( BM ), '*' ) 
WHERE
	BM IS NOT NULL

UPDATE GIM_RENKOU_JIAMI 
SET MQXM = RPAD( SUBSTR( MQXM, 1, 1 ), LENGTH( MQXM ), '*' ) ;
WHERE
	HJDXZ IS NOT NULL;

UPDATE GIM_RENKOU_JIAMI 
SET POXM = RPAD( SUBSTR( POXM, 1, 1 ), LENGTH( POXM ), '*' ) ;
WHERE
	HJDXZ IS NOT NULL;

UPDATE GIM_RENKOU_JIAMI 
SET FQXM =  RPAD( SUBSTR( FQXM, 1, 1 ), LENGTH( FQXM ), '*' ) ;
WHERE
	HJDXZ IS NOT NULL;

UPDATE GIM_RENKOU_JIAMI 
SET GZDW = RPAD( SUBSTR( GZDW, 1, 3 ), LENGTH( GZDW ), '*' ));
WHERE
	HJDXZ IS NOT NULL;

UPDATE GIM_RENKOU_JIAMI 
SET HJDXZ = RPAD( SUBSTR( HJDXZ, 1, 3 ), LENGTH( HJDXZ ), '*' ) 
WHERE
	HJDXZ IS NOT NULL;

UPDATE GIM_RENKOU_JIAMI 
SET CSRQ =  RPAD( SUBSTR( CSRQ, 1, 3 ), LENGTH( CSRQ ), '*' ) ;
WHERE
	HJDXZ IS NOT NULL;

UPDATE GIM_RENKOU_JIAMI 
SET JG =  RPAD( SUBSTR( JG, 1, 3 ), LENGTH( JG ), '*' ) ;
WHERE
	HJDXZ IS NOT NULL;

UPDATE GIM_RENKOU_JIAMI 
SET XJJDXZ = RPAD( SUBSTR( XJJDXZ, 1, 3 ), LENGTH( XJJDXZ ), '*' ) 
WHERE  XJJDXZ IS NOT NULL;

UPDATE GIM_RENKOU_JIAMI 
SET FQSFZH =  DES3_DEC ( FQSFZH ) ;
WHERE
	HJDXZ IS NOT NULL;

UPDATE GIM_RENKOU_JIAMI 
SET MQSFZH =DES3_DEC ( MQSFZH ) ;
WHERE
	HJDXZ IS NOT NULL;

UPDATE GIM_RENKOU_JIAMI 
SET POSFZH = DES3_DEC ( POSFZH ) ;
WHERE
	HJDXZ IS NOT NULL;
``

### 题外篇
* 很多觉得 没有必要加 where～
* 理由 :
     *  函数内验证 增加代码复杂度
     *  使用 NVL函数会扫描全表
     * where 会提前过滤掉为空的数据 有效提升执行时间和性能