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 会提前过滤掉为空的数据 有效提升执行时间和性能
本文来自博客园,作者:vx_guanchaoguo0,转载请注明原文链接:https://www.cnblogs.com/guanchaoguo/p/17835698.html