在mysql中实现字段级的数据脱敏与访问控制，核心思路是通过视图对原始表的敏感字段做脱敏处理，再配合权限管理限制用户只能访问脱敏后的视图，无法直接操作原始表数据，从而兼顾数据安全与业务使用需求。
一、核心实现原理
字段级数据脱敏是指针对表中的特定敏感字段，比如手机号、身份证号、银行卡号等，只展示部分可见内容，其余部分用星号或其他字符替换。mysql内置的字符串函数可以完成这类脱敏逻辑，而视图VIEW可以将脱敏后的查询结果封装成虚拟表，对外提供访问入口。权限管理则通过mysql自带的账户权限体系，回收用户对原始表的访问权限，只授予对脱敏视图的查询权限，实现访问层面的控制。
二、准备工作：创建测试数据表
首先我们创建一个存储用户敏感信息的原始表，后续所有操作都基于这张表展开。
-- 创建用户原始表
CREATE TABLE user_info (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL COMMENT '用户名',
phone VARCHAR(20) NOT NULL COMMENT '手机号',
id_card VARCHAR(20) NOT NULL COMMENT '身份证号',
email VARCHAR(100) NOT NULL COMMENT '邮箱'
);
-- 插入测试数据
INSERT INTO user_info (username, phone, id_card, email) VALUES
('张三', '13812345678', '110101199001011234', 'zhangsan@ipipp.com'),
('李四', '13987654321', '310101199501022345', 'lisi@ipipp.com'),
('王五', '13611112222', '440101200001033456', 'wangwu@ipipp.com');
三、使用mysql函数实现字段脱敏
mysql提供了CONCAT、LEFT、RIGHT、REPEAT等字符串函数，可以灵活实现不同字段的脱敏规则，常见的脱敏规则如下：
- 手机号：保留前3位和后4位，中间用4个星号替换
- 身份证号：保留前6位和后4位，中间用8个星号替换
- 邮箱：保留@符号前的前2位和@及后面的域名，中间用星号替换
我们可以通过以下查询语句验证脱敏逻辑是否正确：
-- 测试脱敏逻辑
SELECT
id,
username,
-- 手机号脱敏：前3位+****+后4位
CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS masked_phone,
-- 身份证号脱敏：前6位+********+后4位
CONCAT(LEFT(id_card, 6), '********', RIGHT(id_card, 4)) AS masked_id_card,
-- 邮箱脱敏：前2位+***+@后面的部分
CONCAT(LEFT(SUBSTRING_INDEX(email, '@', 1), 2), '***@', SUBSTRING_INDEX(email, '@', -1)) AS masked_email
FROM user_info;
四、创建脱敏视图VIEW
将上面验证通过的脱敏查询封装成视图，后续用户只需要访问这个视图就可以获取脱敏后的数据，不需要直接接触原始表。
-- 创建脱敏视图
CREATE VIEW user_info_masked AS
SELECT
id,
username,
CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS phone,
CONCAT(LEFT(id_card, 6), '********', RIGHT(id_card, 4)) AS id_card,
CONCAT(LEFT(SUBSTRING_INDEX(email, '@', 1), 2), '***@', SUBSTRING_INDEX(email, '@', -1)) AS email
FROM user_info;
创建完成后，我们可以直接查询视图验证效果：
-- 查询脱敏视图 SELECT * FROM user_info_masked;
五、结合权限管理实现访问控制
视图创建完成后，还需要通过权限管理限制用户只能访问脱敏视图，无法访问原始表，才能真正实现字段级的访问控制。
1. 创建只读业务用户
首先创建一个专门用于业务查询的用户，比如biz_reader，只允许从本地访问：
-- 创建业务用户，密码设置为Biz@123456 CREATE USER 'biz_reader'@'localhost' IDENTIFIED BY 'Biz@123456';
2. 授予视图查询权限
只给biz_reader用户授予user_info_masked视图的查询权限，不授予原始表的任何权限：
-- 授予视图查询权限 GRANT SELECT ON test_db.user_info_masked TO 'biz_reader'@'localhost'; -- 刷新权限使其生效 FLUSH PRIVILEGES;
3. 验证权限控制效果
切换到biz_reader用户登录mysql，尝试查询原始表和视图：
-- 切换到biz_reader用户后执行 -- 查询脱敏视图，正常返回脱敏数据 SELECT * FROM test_db.user_info_masked; -- 尝试查询原始表，会提示权限不足 SELECT * FROM test_db.user_info; -- 错误信息：ERROR 1142 (42000): SELECT command denied to user 'biz_reader'@'localhost' for table 'user_info'
六、方案扩展与注意事项
- 如果业务需要不同角色看到不同脱敏程度的数据，可以创建多个不同脱敏规则的视图，给不同用户授予对应的视图权限即可。
- 视图是虚拟表，每次查询视图都会执行背后的查询逻辑，如果原始表数据量很大，建议对原始表的脱敏字段加索引，提升查询效率。
- 如果需要更新脱敏数据，不建议直接通过视图更新，因为脱敏后的字段是计算得到的，无法直接映射回原始字段，更新操作建议通过存储过程配合权限控制实现。
- mysql的权限是全局生效的，如果需要更细粒度的行级或者字段级权限，也可以结合应用层的权限校验共同实现，数据库层做基础的安全兜底。
注意：如果业务中使用的mysql版本低于5.7，部分字符串函数可能不支持，需要替换成对应版本的兼容函数，比如SUBSTRING_INDEX在低版本中同样可用，只需要调整参数即可。