基于Mybatis的数据库脱敏方案

前言

当你的公司达到一定规模的时候，会有专门的人员来审计你们公司的数据，尤其是支付公司，财税公司，当审计人员发现你们的数据库中所有人的真实信息都是明文存储时，肯定是不可以的。本文介绍配合Mybatis-plus来实现数据脱敏，demo将会在文章最后附上地址。

---

基于TypeHandler数据库脱敏方案

由于项目数据库中间件使用的是Mybatis，所以使用Mybatis中的BaseTypeHandler的一个类型处理器,对数据进行AES加密存入数据。

代码实现

import lombok.extern.slf4j.Slf4j;
import sun.misc.BASE64Decoder;
import sun.misc.BASE64Encoder;

import javax.crypto.Cipher;
import javax.crypto.spec.IvParameterSpec;
import javax.crypto.spec.SecretKeySpec;

/**
 * 加密工具
 */
@Slf4j
public class DataDesensitizationUtils {
    private DataDesensitizationUtils() {

    }

    private static final String KEY = "8ce87b8aa3463f4561635f66991592ae";
    /**
     * 加密
     * @param data
     * @return
     * @throws Exception
     */
    public static String encrypt(String data) {
        try {
            byte[] plaintext = data.getBytes();
            Cipher cipher = Cipher.getInstance("AES/CBC/PKCS5Padding");
            cipher.init(Cipher.ENCRYPT_MODE, new SecretKeySpec(KEY.substring(0, 16).getBytes(), "AES"), new IvParameterSpec(KEY.substring(16).getBytes()));
            byte[] encrypted = cipher.doFinal(plaintext);
            return new BASE64Encoder().encode(encrypted).trim();
        } catch (Exception e) {
            log.error("加密数据失败", e);
            return null;
        }
    }

    /**
     * 解密
     * @param data
     * @return
     */
    public static String decrypt(String data) {
        try {
            byte[] encrypted = new BASE64Decoder().decodeBuffer(data);
            Cipher cipher = Cipher.getInstance("AES/CBC/PKCS5Padding");
            cipher.init(Cipher.DECRYPT_MODE, new SecretKeySpec(KEY.substring(0, 16).getBytes(), "AES"), new IvParameterSpec(KEY.substring(16).getBytes()));
            byte[] original = cipher.doFinal(encrypted);
            String originalString = new String(original);
            return originalString.trim();
        } catch (Exception e) {
            log.error("解密数据失败", e);
            return null;
        }
    }
}

import cn.hutool.core.text.CharSequenceUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import xyz.molzhao.util.DataDesensitizationUtils;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 敏感信息（例如:姓名/身份证/银行卡号）脱敏存入数据库
 * 本文采用AES加解密，实际情况可根据需要自行选择加解密方式
 * 数据库CRUD时如何使用？
 */
@Slf4j
public class SensitiveDataTypeHandler extends BaseTypeHandler<String> {
    /**
     * 密钥
     */
    private static final String ERROR = "SensitiveDataTypeHandler异常";

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
        try {
            ps.setString(i, DataDesensitizationUtils.encrypt(parameter));
        } catch (Exception e) {
            log.info(ERROR, e);
        }
    }

    @Override
    public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String columnValue = rs.getString(columnName);
        try {
            return CharSequenceUtil.isEmpty(columnValue) ? columnValue : DataDesensitizationUtils.decrypt(columnValue);
        } catch (Exception e) {
            log.info(ERROR, e);
            return columnValue;
        }
    }

    @Override
    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String columnValue = rs.getString(columnIndex);
        try {
            return CharSequenceUtil.isEmpty(columnValue) ? columnValue : DataDesensitizationUtils.decrypt(columnValue);
        } catch (Exception e) {
            log.info(ERROR, e);
            return columnValue;
        }
    }

    @Override
    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String columnValue = cs.getString(columnIndex);
        try {
            return CharSequenceUtil.isBlank(columnValue) ? columnValue : DataDesensitizationUtils.decrypt(columnValue);
        } catch (Exception e) {
            log.info(ERROR, e);
            return columnValue;
        }
    }

Handler使用

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="xyz.molzhao.mapper.UserMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="xyz.molzhao.domain.User">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age"/>
        <result column="email" property="email"/>
        <result column="mobile" property="mobile" typeHandler="xyz.molzhao.handler.SensitiveDataTypeHandler"/>
        <result column="id_card" property="idCard" typeHandler="xyz.molzhao.handler.SensitiveDataTypeHandler"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, `name`, age, email, mobile, id_card
    </sql>
    <select id="selectById" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"></include>
        from `user`
        where id = #{id};
    </select>
    <select id="selectByMobile" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"></include>
        from `user`
        where mobile = #{mobile, jdbcType=VARCHAR, typeHandler=xyz.molzhao.handler.SensitiveDataTypeHandler};
    </select>


</mapper>

单元测试

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import xyz.molzhao.domain.User;

import javax.annotation.Resource;

import static org.junit.jupiter.api.Assertions.assertEquals;

@SpringBootTest
@RunWith(SpringRunner.class)
public class UserServiceImplTest {
    @Resource
    private IUserService userService;

    @Test
    public void testSave() {
        // Arrange
        User user1 = User.builder().name("test1")
                .age(18)
                .email("xxxx@xxx.com")
                .mobile("17858661611")
                .idCard("330101200001010101")
                .build();

        // Act
        userService.save(user1);
        User result = userService.selectById(user1.getId());

        // Assert
        assertEquals(result.getMobile(), user1.getMobile());
    }

    @Test
    public void testSelectByMobile() {
        // Arrange
        String mobile = "17858661600";

        // Act
        User user = userService.selectByMobile(mobile);

        // Assert
        assertEquals(mobile, user.getMobile());
    }

数据库结果

mobile	id_card
7ylDd+J1hwZob5fKaa2ZgQ==	I6zf0lL2jmul0NTb0QE0htbGc9jNEezjU0On9vKHhng=

由此看出我们数据库里虽然存的是密文但是在单元测试中可以和明文匹配，由此数据库脱敏也就完成了。

---

【转载】 基于Mybatis插件+注解实现方案

public interface Crypt {
    String encrypt(String plain);

    /**
     * 解密
     *
     * @param cipher
     *            密文
     * @return 原始明文
     */
    String decrypt(String cipher);
}

package xyz.molzhao.crypt;

import org.springframework.stereotype.Service;
import xyz.molzhao.util.DataDesensitizationUtils;

@Service
public class AESCryptImpl implements Crypt {
    @Override
    public String encrypt(String plain) {
        return DataDesensitizationUtils.encrypt(plain);
    }

    @Override
    public String decrypt(String cipher) {
        return DataDesensitizationUtils.decrypt(cipher);
    }
}

package xyz.molzhao.crypt;

import java.util.HashMap;
import java.util.Map;

public class CryptContext {
    private static final Map<CryptTypeEnum, Crypt> Crypts = new HashMap<>(CryptTypeEnum.values().length);

    private CryptContext() {

    }

    /**
     * 获取加密方式
     *
     * @param cryptTypeEnum
     *            加密方式枚举
     * @return 机密方式实现类
     */
    public static Crypt getCrypt(CryptTypeEnum cryptTypeEnum) {
        Crypt crypt = Crypts.get(cryptTypeEnum);
        if (crypt == null) {
            crypt = Crypts.get(CryptTypeEnum.AES);
        }

        return crypt;
    }

    /**
     * 设置加密方式
     *
     * @param cryptTypeEnum
     *            加密类型
     * @param crypt
     *            加载方式
     */
    public static void setCrypt(CryptTypeEnum cryptTypeEnum, Crypt crypt) {
        Crypts.put(cryptTypeEnum, crypt);
    }
}

package xyz.molzhao.crypt;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target({ ElementType.FIELD, ElementType.PARAMETER, ElementType.METHOD })
@Retention(RetentionPolicy.RUNTIME)
public @interface CryptField {
    CryptTypeEnum value() default CryptTypeEnum.AES;
}

package xyz.molzhao.crypt;

public class CryptLoader {
    /**
     * 加载所有加密方式实现类
     */
    public void loadCrypt() {
        CryptContext.setCrypt(CryptTypeEnum.AES, new AESCryptImpl());
    }
}

package xyz.molzhao.crypt;

public enum CryptTypeEnum {
    /** AES加密（这个可是加密，不是脱敏） */
    AES
}

package xyz.molzhao.crypt;

import cn.hutool.core.text.CharSequenceUtil;
import org.apache.ibatis.annotations.Param;
import org.springframework.core.ParameterNameDiscoverer;

import java.lang.annotation.Annotation;
import java.lang.reflect.Constructor;
import java.lang.reflect.Method;
import java.lang.reflect.Parameter;

/**
 * MyBatis接口参数名称发现器
 *
 * @author wangzhuhua
 * @date 2018/09/05 下午3:12
 **/
public class MyBatisParameterNameDiscoverer implements ParameterNameDiscoverer {

    @Override
    public String[] getParameterNames(Method method) {
        return getParameterNames(method.getParameters(), method.getParameterAnnotations());
    }

    @Override
    public String[] getParameterNames(Constructor<?> ctor) {
        return getParameterNames(ctor.getParameters(), ctor.getParameterAnnotations());
    }

    /**
     * Mybatis参数名称解析
     *
     * @param parameters
     *            参数数组
     * @param parameterAnnotations
     *            参数注解数组
     * @return 参数名称
     */
    private String[] getParameterNames(Parameter[] parameters, Annotation[][] parameterAnnotations) {
        String[] parameterNames = new String[parameters.length];
        for (int i = 0; i < parameters.length; i++) {
            Parameter param = parameters[i];
            String paramName = param.getName();

            // mybatis 自定义参数名称
            for (Annotation annotation : parameterAnnotations[i]) {
                if (annotation instanceof Param) {
                    String customName = ((Param) annotation).value();
                    if (CharSequenceUtil.isNotEmpty(customName)) {
                        paramName = customName;
                        break;
                    }
                }
            }

            parameterNames[i] = paramName;
        }
        return parameterNames;
    }
}

package xyz.molzhao.crypt;


import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import org.apache.ibatis.binding.MapperMethod;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.defaults.DefaultSqlSession;
import org.springframework.core.ParameterNameDiscoverer;

import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

@Intercepts(value = {
        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class,
                RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class,
                RowBounds.class, ResultHandler.class})})
public class CryptInterceptor implements Interceptor {

    /**
     * 参数注解缓存
     */
    private static final ConcurrentHashMap<String, Map<String, CryptField>> PARAM_ANNOTATIONS_MAP = new ConcurrentHashMap<>();
    /**
     * 返回值注解缓存
     */
    private static final ConcurrentHashMap<String, CryptField> RETURN_ANNOTATIONS_MAP = new ConcurrentHashMap<>();
    /**
     * 参数名解析器
     */
    private final ParameterNameDiscoverer parameterNameDiscoverer = new MyBatisParameterNameDiscoverer();

    public CryptInterceptor() {
        (new CryptLoader()).loadCrypt();
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs();
        // 入参
        Object parameter = args[1];
        MappedStatement statement = (MappedStatement) args[0];
        // 判断是否需要解析
        if (!isNotCrypt(parameter)) {
            Map<String, CryptField> cryptFieldMap = getParameterAnnotations(statement);
            // 单参数 string
            if (parameter instanceof String && !cryptFieldMap.isEmpty()) {
                args[1] = stringEncrypt(cryptFieldMap.keySet().iterator().next(), (String) parameter,
                        getParameterAnnotations(statement));
                // 单参数 list
            } else if (parameter instanceof DefaultSqlSession.StrictMap) {
                DefaultSqlSession.StrictMap<Object> strictMap = (DefaultSqlSession.StrictMap<Object>) parameter;
                for (Map.Entry<String, Object> entry : strictMap.entrySet()) {
                    if (entry.getKey().contains("collection")) {
                        continue;
                    }
                    if (entry.getKey().contains("list")) {
                        listEncrypt((List) entry.getValue(), cryptFieldMap.get(entry.getKey()));
                    }
                }
                // 多参数
            } else if (parameter instanceof MapperMethod.ParamMap) {
                MapperMethod.ParamMap<Object> paramMap = (MapperMethod.ParamMap<Object>) parameter;
                // 解析每一个参数
                for (Map.Entry<String, Object> entry : paramMap.entrySet()) {
                    // 判断不需要解析的类型 不解析map
                    if (isNotCrypt(entry.getValue()) || entry.getValue() instanceof Map
                            || entry.getKey().contains("param")) {
                        continue;
                    }
                    // 如果string
                    if (entry.getValue() instanceof String) {
                        entry.setValue(stringEncrypt(entry.getKey(), (String) entry.getValue(), cryptFieldMap));
                        continue;
                    }
                    // 如果 list
                    if (entry.getValue() instanceof List) {
                        listEncrypt((List) entry.getValue(), cryptFieldMap.get(entry.getKey()));
                        continue;
                    }
                    beanEncrypt(entry.getValue());
                }
                // bean
            } else {
                beanEncrypt(parameter);
            }
        }

        // 获得出参
        Object returnValue = invocation.proceed();

        // 出参解密
        if (isNotCrypt(returnValue)) {
            return returnValue;
        }

        // 获得方法注解(针对返回值)
        CryptField cryptField = getMethodAnnotations(statement);
        if (returnValue instanceof String) {
            return stringDecrypt((String) returnValue, cryptField);
        }
        if (returnValue instanceof List) {
            listDecrypt((List) returnValue, cryptField);
            return returnValue;
        }

        return returnValue;
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {

    }

    /**
     * 获取 方法上的注解
     *
     * @param statement MappedStatement
     * @return 方法上的加密注解 {@link CryptField}
     * @throws ClassNotFoundException
     */
    private CryptField getMethodAnnotations(MappedStatement statement) throws ClassNotFoundException {
        String id = statement.getId();

        CryptField cryptField = RETURN_ANNOTATIONS_MAP.get(id);
        if (cryptField != null) {
            return cryptField;
        }

        // 获取执行方法
        Method method = null;
        final Class clazz = Class.forName(id.substring(0, id.lastIndexOf(".")));
        for (Method _method : clazz.getDeclaredMethods()) {
            if (_method.getName().equals(id.substring(id.lastIndexOf(".") + 1))) {
                method = _method;
                break;
            }
        }
        if (method == null) {
            return null;
        }

        return method.getAnnotation(CryptField.class);
    }

    /**
     * 获取 方法参数上的注解
     *
     * @param statement MappedStatement
     * @return 参数名与其对应加密注解
     * @throws ClassNotFoundException
     */
    private Map<String, CryptField> getParameterAnnotations(MappedStatement statement) throws ClassNotFoundException {
        // 执行ID
        final String id = statement.getId();

        Map<String, CryptField> cryptFieldMap = PARAM_ANNOTATIONS_MAP.get(id);
        if (cryptFieldMap != null) {
            return cryptFieldMap;
        } else {
            cryptFieldMap = new HashMap<>();
        }

        // 获取执行方法
        Method method = null;
        final Class clazz = Class.forName(id.substring(0, id.lastIndexOf(".")));
        for (Method _method : clazz.getDeclaredMethods()) {
            if (_method.getName().equals(id.substring(id.lastIndexOf(".") + 1))) {
                method = _method;
                break;
            }
        }
        if (method == null) {
            return cryptFieldMap;
        }

        // 获取参数名称
        String[] paramNames = parameterNameDiscoverer.getParameterNames(method);
        // 获取方法参数注解列表
        final Annotation[][] paramAnnotations = method.getParameterAnnotations();
        // 填充参数注解
        for (int i = 0; i < paramAnnotations.length; i++) {
            Annotation[] paramAnnotation = paramAnnotations[i];
            for (Annotation annotation : paramAnnotation) {
                if (annotation instanceof CryptField) {
                    cryptFieldMap.put(paramNames[i], (CryptField) annotation);
                    break;
                }
            }
        }

        // 存入缓存
        PARAM_ANNOTATIONS_MAP.put(id, cryptFieldMap);

        return cryptFieldMap;
    }

    /**
     * 判断是否需要加解密
     *
     * @param obj 待加密对象
     * @return 是否需要加密
     */
    private boolean isNotCrypt(Object obj) {
        return obj == null || obj instanceof Double || obj instanceof Integer || obj instanceof Long
                || obj instanceof Boolean;
    }

    /**
     * String 加密
     *
     * @param name             参数名称
     * @param plain            参数明文
     * @param paramAnnotations 加密注解
     * @return 密文
     */
    private String stringEncrypt(String name, String plain, Map<String, CryptField> paramAnnotations) {
        return stringEncrypt(plain, paramAnnotations.get(name));
    }

    /**
     * String 加密
     *
     * @param plain      参数明文
     * @param cryptField 加密注解
     * @return 密文
     */
    private String stringEncrypt(String plain, CryptField cryptField) {
        if (StringUtils.isBlank(plain) || cryptField == null) {
            return plain;
        }

        return CryptContext.getCrypt(cryptField.value()).encrypt(plain);
    }

    /**
     * String 解密
     *
     * @param cipher     参数密文
     * @param cryptField 加密注解
     * @return 明文
     */
    private String stringDecrypt(String cipher, CryptField cryptField) {
        if (StringUtils.isBlank(cipher) || cryptField == null) {
            return cipher;
        }

        return CryptContext.getCrypt(cryptField.value()).decrypt(cipher);
    }

    /**
     * list 加密
     *
     * @param plainList  明文列表
     * @param cryptField 加密方式注解
     * @return 密文列表
     * @throws IllegalAccessException
     */
    private List listEncrypt(List plainList, CryptField cryptField) throws IllegalAccessException {
        for (int i = 0; i < plainList.size(); i++) {
            Object plain = plainList.get(i);
            // 判断不需要解析的类型
            if (isNotCrypt(plain) || plain instanceof Map) {
                break;
            }
            if (plain instanceof String) {
                plainList.set(i, stringEncrypt((String) plain, cryptField));
                continue;
            }
            beanEncrypt(plain);
        }

        return plainList;
    }

    /**
     * list 解密
     *
     * @param cipherList 密文列表
     * @param cryptField 加密方式注解
     * @return 明文列表
     * @throws IllegalAccessException
     */
    private List listDecrypt(List cipherList, CryptField cryptField) throws IllegalAccessException {
        for (int i = 0; i < cipherList.size(); i++) {
            Object cipher = cipherList.get(i);
            // 判断不需要解析的类型
            if (isNotCrypt(cipher) || cipher instanceof Map) {
                break;
            }
            if (cipher instanceof String) {
                cipherList.set(i, stringDecrypt((String) cipher, cryptField));
                continue;
            }
            beanDecrypt(cipher);
        }

        return cipherList;
    }

    /**
     * bean 加密
     *
     * @param plainObject 明文对象
     * @throws IllegalAccessException
     */
    private void beanEncrypt(Object plainObject) throws IllegalAccessException {
        Class objClazz = plainObject.getClass();
        Field[] objFields = objClazz.getDeclaredFields();
        for (Field field : objFields) {
            CryptField cryptField = field.getAnnotation(CryptField.class);
            if (cryptField != null) {
                field.setAccessible(true);
                Object plain = field.get(plainObject);
                if (plain == null) {
                    continue;
                }
                if (field.getType().equals(String.class)) {
                    field.set(plainObject, stringEncrypt((String) plain, cryptField));
                    continue;
                }
                if (field.getType().equals(List.class)) {
                    field.set(plainObject, listEncrypt((List) plain, cryptField));
                    continue;
                }
                field.setAccessible(false);
            }
        }
    }

    /**
     * bean 解密
     *
     * @param cipherObject 密文对象
     * @throws IllegalAccessException
     */
    private void beanDecrypt(Object cipherObject) throws IllegalAccessException {
        Class objClazz = cipherObject.getClass();
        Field[] objFields = objClazz.getDeclaredFields();
        for (Field field : objFields) {
            CryptField cryptField = field.getAnnotation(CryptField.class);
            if (cryptField != null) {
                field.setAccessible(true);
                Object cipher = field.get(cipherObject);
                if (cipher == null) {
                    continue;
                }
                if (field.getType().equals(String.class)) {
                    field.set(cipherObject, stringDecrypt((String) cipher, cryptField));
                    continue;
                }
                if (field.getType().equals(List.class)) {
                    field.set(cipherObject, listDecrypt((List) cipher, cryptField));
                    continue;
                }
            }
        }
    }
}

添加Mybatis插件

@Bean
   public ConfigurationCustomizer configurationCustomizer() {
       return configuration -> {
           //插件拦截链采用了责任链模式，执行顺序和加入连接链的顺序有关
           CryptInterceptor myPlugin = new CryptInterceptor();
           //设置参数，比如阈值等，可以在配置文件中配置，这里直接写死便于测试
           configuration.addInterceptor(myPlugin);
       };
   }

@CryptField
private String mobile;

@CryptField
private String idCard;

public interface UserMapper extends BaseMapper<User> {
    User selectById(@Param("id") Long id);

    User selectByMobile(@CryptField @Param("mobile") String mobile);
}

使用的时候只需要在变量上加上@CryptField注解即可。

项目demo