SQL Server动态数据掩码中UPDATE操作结果异常原因咨询

问题还原 #

测试SQL Server动态数据掩码功能时，执行以下代码出现异常：

EXECUTE AS USER = 'TestManager2'
SELECT * FROM Employee
update Employee set ServicePeriodInYears = ServicePeriodInYears+1 where FirstName='Aram'
-- 结果为0，但实际ServicePeriodInYears >0
update Employee set ServicePeriodInYears = 11 where FirstName='Sos'
-- 结果为11
REVERT
SELECT * FROM Employee

第一条UPDATE语句未实际修改数据（原权限用户查询真实值仍大于0，TestManager2查询看到0），第二条UPDATE可正常修改真实值。

原因解析 #

1. FirstName列掩码导致WHERE条件未匹配 #

如果FirstName列配置了动态数据掩码，且TestManager2未被授予UNMASK权限，那么TestManager2查询到的FirstName是掩码后的值，并非数据库存储的真实值。此时where FirstName='Aram'无法匹配到真实存储FirstName='Aram'的行，第一条UPDATE语句实际未更新任何数据。

2. 掩码列的读取权限限制（若WHERE条件匹配的情况） #

即使FirstName列未掩码，TestManager2能匹配到目标行，由于ServicePeriodInYears是掩码列，无UNMASK权限的用户在UPDATE语句中引用该列当前值时，只会读取到掩码后的虚拟值（比如数字掩码默认返回0）。此时ServicePeriodInYears+1的计算逻辑基于0+1，会将真实值覆盖为1，但TestManager2查询时仍会看到掩码后的0，造成“未修改”的错觉。

3. 字面量UPDATE不受掩码影响 #

第二条UPDATE语句使用字面量赋值（set ServicePeriodInYears=11），不需要读取原列的掩码值，只要where FirstName='Sos'能匹配到行（无论FirstName是否掩码，只要用户看到的值是'Sos'），就能直接修改真实列值。执行REVERT后用原权限用户查询，就能看到真实的修改结果11。

验证方案 #

• 检查TestManager2的权限：执行SELECT HAS_PERMS_BY_NAME(NULL, NULL, 'UNMASK')，返回0表示无UNMASK权限。
• 查看列的掩码配置：执行SELECT name, is_masked, masking_function FROM sys.columns WHERE object_id = OBJECT_ID('Employee')，确认FirstName和ServicePeriodInYears的掩码规则。

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