快速清洗数据库数据（数据脱敏方案）

最新推荐文章于 2024-08-09 08:18:47 发布

置顶 PerccyKing

最新推荐文章于 2024-08-09 08:18:47 发布

阅读量2.3k

点赞数 2

分类专栏：数据库后端文章标签： mysql java

本文链接：https://blog.csdn.net/qq_29602577/article/details/106331944

版权

后端同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

数据库

2 篇文章 0 订阅

订阅专栏

文章推荐

免费的idea mybatis log plugin

文章目录

快速清洗数据库数据（数据脱敏方案）

快速清洗数据库数据（数据脱敏方案）

时间：20200522

背景

场景1

甲方向乙方提供部分生产数据样本，但是数据中存在敏感信息，需要将这部分数据清洗掉，或者对这部分不影响业务的数据进行混淆

场景2

在开发过程中，引用了部分生产数据，数据当中存在敏感信息，为预防，这部分数据流向互联网，提前对这部分数据进行清洗或者混淆处理

痛点

数据表太多
不能快速确定敏感数据在什么地方
边找边执行，费事费力

需求

能批量清洗数据，去除敏感信息
能进行配置操作（配置为json数据-基于mysql对json的支持），适用于所有的表
可扩展，必要时只需要修改混淆算法
执行效率要高（此点占时没有实现）

方案

第一步：创建一个存储过程，接收两个参数，第一个参数为配置信息，第二个参数为混淆字符
第二步：配置信息中包括，需要进行混淆的表，以及表中部分需要混淆的字段
第三步：读取配置，拼接为一条SQL，每个字段执行更新操作，且每个字段的数据进行混淆操作
第四部：实现混淆的方法，供第三步使用

实现

数据清洗存储过程

CREATE
    DEFINER = root@`%` PROCEDURE proc_data_clean(IN data TEXT, IN blur VARCHAR(10))
BEGIN
    /**
      proc_name:        数据清洗过程
      author_name:      PerccyKing
      create_time:      2020/05/21 上午 10:08
      function:         将某张表的部分字段进行数据混淆
      param_in_data:    json数据,包括待混淆的表与字段
      param_in_exp:     [{"table":"table_name","field":["field1","field2"]},{"table":"table_name","field":["field1","field2"]}]
      param_in_blur:    混淆字符
      param_in_exp:     *
     */
    # 表名
    DECLARE $table_name VARCHAR(60) DEFAULT '';
    #配置了表的数量
    DECLARE $table_num INT;
    # 字段列表
    DECLARE $fields VARCHAR(2048);
    # 字段
    DECLARE $field_name VARCHAR(255);

    # === 开始解析json数据

    DECLARE i INT DEFAULT 0;

    DECLARE exec_sql VARCHAR(2048);

    DECLARE j INT DEFAULT 0;
    DECLARE fl INT DEFAULT 0;
    DECLARE sql_ VARCHAR(2048);

    SET $table_num = json_length(data);

    WHILE i < $table_num
        DO
            SELECT json_unquote(JSON_EXTRACT(data, CONCAT('$[', i, '].table'))) INTO $table_name;
            SELECT json_unquote(JSON_EXTRACT(data, CONCAT('$[', i, '].field'))) INTO $fields;

            SET fl = json_length($fields);

            SELECT concat('UPDATE ', $table_name, ' set ') INTO sql_;


            WHILE j < fl
                DO
                    SELECT json_unquote(JSON_EXTRACT($fields, CONCAT('$[', j, ']'))) INTO $field_name;
                    # fun_blur 为具体的混淆方法
                    SELECT concat(sql_, $field_name, '= fun_blur(', $field_name, ',\'', blur, '\'),')
                    INTO sql_;
                    SET j = j + 1;
                END WHILE;
            SET j = 0;
            SELECT substr(sql_, 1, char_length(sql_) - 1) INTO sql_;
            SELECT concat(sql_, ' where 1=1') INTO sql_;
#           必要的时候可以写日志，以判断数据是否有被清洗到
#             INSERT INTO tem_log(`sql`) VALUE (sql_);
            SET @sql_ = sql_;
            PREPARE exec_sql FROM @sql_;
            EXECUTE exec_sql;
            DEALLOCATE PREPARE exec_sql;
            SET i = i + 1;
        END WHILE;


END;

具体的混淆算法

当前算法可以自定义，当前算法采用将偶数为全部替换为另外的字符

CREATE
    DEFINER = root@`%` FUNCTION fun_blur(blur_str TEXT, blur VARCHAR(10)) RETURNS TEXT
BEGIN
    DECLARE len INTEGER;
    DECLARE i INTEGER DEFAULT 1;
    DECLARE res TEXT;
    SELECT char_length(blur_str) INTO len;
    WHILE i <= len
        DO
            IF i % 2 = 0 THEN
                SELECT concat(left(blur_str, i - 1), blur, right(blur_str, len - i)) INTO blur_str;
            END IF;
            SET i = i + 1;
        END WHILE;
    RETURN blur_str;
END;

可能的错误

> 1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

解决方案

SET GLOBAL log_bin_trust_function_creators=TRUE;

使用方法

SET @conf = '[{
    "table": "table_name1",
    "field": [
      "field1"
    ]
  },
  {
    "table": "table_name2",
    "field": [
      "field1",
      "field2",
      "field5",
      "field7"
    ]
  }]';

CALL proc_data_clean(@conf, '*');

实战

原始数据

命令

SET @conf = '[{
    "table": "t_user_copy1",
    "field": [
      "uname",
			"upwd",
			"uemail",
			"nickName"
    ]
  }]';
	
CALL proc_data_clean(@conf,'$')