前天开始居家办公,用向日葵远程办公室电脑,但是昨天办公室的网崩了,导致无法远程,于是开始用腾讯云做数据库,为防止数据泄露就有了下边的关键数据脱敏操作。
笔记如下
select * from job_xs where xh='U201311735';
--REPLACE(<字段名>,SUBSTR(<字段名>,2,1), “*”) AS <字段名>
update job_xs set sfzh = REPLACE(sfzh,SUBSTR(sfzh,7,8), '********')
where length(sfzh)=18;
--手机号脱敏;
UPDATE XYDB_RZRJ
SET lxdh = ( CASE WHEN lxdh IS NOT NULL THEN
SUBSTR (lxdh , 1 , 3) || '*****' || SUBSTR (lxdh , 9)
ELSE NULL END )
where uuid='16ce4c60-de9a-4015-9e56-5af86fd49482';
--工号脱敏;
UPDATE XYDB_RZRJ
SET XMLXRGH = ( CASE WHEN XMLXRGH IS NOT NULL THEN
SUBSTR (XMLXRGH , 1 , 3) || '*****' || SUBSTR (XMLXRGH , 9)
ELSE NULL END );
--身份证脱敏;
UPDATE XYDB_RZRJ
SET sfzjh = ( CASE WHEN sfzjh IS NOT NULL THEN
SUBSTR (sfzjh , 1 , 3) || '***********' || SUBSTR (sfzjh , 15)
ELSE NULL END );
--邮箱脱敏;
UPDATE XYDB_RZRJ
SET dzxx = ( CASE WHEN dzxx IS NOT NULL THEN
SUBSTR (dzxx , 1 , 3) || '*****' || SUBSTR (dzxx , 9)
ELSE NULL END )
where uuid='16ce4c60-de9a-4015-9e56-5af86fd49482';