关于SQL Server读取时实现一致性数据加密/掩码的技术咨询

结合你的需求（静态数据明文存储、查询时动态掩码、全库掩码值一致），以下是经过实践验证的可行方案：

方案1：自定义确定性映射函数 + 权限控制视图 #

这是最直接且易维护的方案，核心思路是通过全局映射表保证掩码一致性，用视图封装掩码逻辑，再通过权限控制隔离原始数据。

实施步骤 #

1. 创建全局映射表：存储原始敏感值与固定掩码值的对应关系，确保全库统一。
2. 编写确定性掩码函数：从映射表中读取对应掩码，无匹配时返回默认值，函数必须保证同一输入返回固定输出。
3. 构建掩码视图：对每个含敏感列的表创建视图，用掩码函数替换敏感字段。
4. 配置权限：给特定用户授予视图查询权限，同时拒绝其访问原始基表。

示例代码 #

1. 创建映射表

CREATE TABLE dbo.SensitiveValueMap (
    OriginalValue NVARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS PRIMARY KEY, -- 按需设置大小写敏感性
    MaskedValue NVARCHAR(255) NOT NULL UNIQUE
);

-- 初始化映射规则
INSERT INTO dbo.SensitiveValueMap (OriginalValue, MaskedValue)
VALUES ('john', 'xyz'), ('jane', 'abc'), ('bob', '123');

2. 创建确定性掩码函数

CREATE FUNCTION dbo.GetConsistentMask(@OriginalValue NVARCHAR(255))
RETURNS NVARCHAR(255)
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS BEGIN
    DECLARE @Masked NVARCHAR(255);
    -- 确定性查询，保证同一输入返回固定结果
    SELECT @Masked = MaskedValue 
    FROM dbo.SensitiveValueMap 
    WHERE OriginalValue = @OriginalValue;

    -- 无映射时返回统一默认掩码
    RETURN ISNULL(@Masked, '***MASKED***');
END;

3. 创建跨Schema的掩码视图

-- Schema1.TableA的掩码视图
CREATE VIEW Schema1.MaskedTableA
AS
SELECT
    ID,
    dbo.GetConsistentMask(Username) AS Username,
    dbo.GetConsistentMask(Email) AS Email,
    Phone, -- 非敏感列直接返回
    CreateDate
FROM Schema1.TableA;

-- Schema2.TableB的掩码视图
CREATE VIEW Schema2.MaskedTableB
AS
SELECT
    TransactionID,
    dbo.GetConsistentMask(Username) AS Username,
    Amount,
    TransactionDate
FROM Schema2.TableB;

4. 权限配置

-- 创建专用角色
CREATE ROLE MaskedUserRole;

-- 授予视图查询权限
GRANT SELECT ON Schema1.MaskedTableA TO MaskedUserRole;
GRANT SELECT ON Schema2.MaskedTableB TO MaskedUserRole;

-- 拒绝访问原始基表
DENY SELECT ON Schema1.TableA TO MaskedUserRole;
DENY SELECT ON Schema2.TableB TO MaskedUserRole;

-- 将目标用户添加到角色
ALTER ROLE MaskedUserRole ADD MEMBER [RestrictedUser];

方案优势 #

• 严格满足静态数据明文存储要求，原始表无任何加密/掩码操作。
• 映射表全局维护，确保同一敏感值在全库所有表中的掩码结果完全一致，支持跨Schema表关联。
• 权限隔离彻底，特定用户无法直接访问原始数据。
• 映射规则可灵活修改（仅需更新映射表），无需调整视图或函数。

方案2：行级安全（RLS）+ 计算列（可选） #

如果希望用户可以直接查询基表名而无需切换视图，可采用此方案：

1. 给敏感表添加持久化计算列，值为掩码函数的返回结果。
2. 创建RLS策略，针对特定角色，过滤掉原始敏感列的访问权限，强制返回计算列的值。

此方案复杂度较高，需额外维护计算列和RLS规则，推荐优先使用视图方案。

关键注意事项 #

• 掩码函数必须是确定性的：需添加WITH SCHEMABINDING，且函数内不能使用随机函数、GETDATE()等非确定性操作，否则无法保证掩码一致性。
• 映射表性能优化：若敏感值数量较大，需给OriginalValue列创建索引，确保函数查询效率。
• 权限最小化：严格控制映射表的访问权限，仅允许管理员修改，普通用户无读写权限。
• 关联场景测试：验证跨Schema表的掩码列关联逻辑，确保一致性不影响业务查询。

内容的提问来源于stack exchange，提问作者Aravindhan Ravichandran