Andy Lamb's questions

我们利用MASKEDSQL Server 中的列功能在查询时隐藏敏感数据。但我们遇到过这样一种情况，即INSERT作为语句的一部分执行的MERGE是插入屏蔽UNMASK值。即，即使以具有权限的用户身份查询表，新插入的值也会被视为屏蔽值。

具体来说，这种情况发生在：

1. 一列是MASKED。
2. 当前用户没有该UNMASK权限。
3. 该MERGE语句的ON子句使用了该MASKED列。
4. 该MERGE语句不匹配，因此落入该INSERT部分。

以下是该问题的简单复现。

-- Define a test table with one non-masked and one masked column.
CREATE TABLE TestTable(
     C1 NVARCHAR(100) MASKED WITH (FUNCTION = 'default()'),
     C2 NVARCHAR(100) MASKED WITH (FUNCTION = 'default()'),
     C3 NVARCHAR(100) -- Not masked
);

INSERT INTO TestTable
VALUES ('aaa', 'aaa', 'aaa'), ('bbb', 'bbb', 'bbb')

-- Print the current user and initial table content
SELECT SUSER_NAME() AS LoginName, USER_NAME() AS UserName;
SELECT *, 'No columns should be Masked' FROM TestTable

-- Create users with different permissions
CREATE USER user1 WITHOUT LOGIN
-- Setup permissions (No UNMASK permissions)
GRANT SELECT, UPDATE, INSERT TO user1;   

-- Switch context to the user WITHOUT UNMASK permissions
EXECUTE AS USER = 'user1';  

-- Show the user can only see masked data
SELECT SUSER_NAME() AS LoginName, USER_NAME() AS UserName;
SELECT *, 'Columns 1 & 2 should be Masked' FROM TestTable

-- Execute the merge, matching on the masked column
MERGE TestTable target
USING (
    SELECT * FROM (VALUES 
        ('bbb', 'bbb', 'bbb'), -- Will be merged
        ('ccc', 'ccc', 'ccc'), -- Will be inserted
        ('ddd', 'ddd', 'ddd')) -- Will be inserted
        AS s (C1 ,C2 ,C3)
) AS source
ON (source.C1 = target.C1)
WHEN MATCHED THEN UPDATE SET
    target.C2 = source.C2,
    target.C3 = source.C3
WHEN NOT MATCHED BY TARGET
  THEN INSERT (C1, C2, C3)
  VALUES (source.C1, source.C2, source.C3);

-- A regular insert statement to check if masking applies there
INSERT INTO TestTable
VALUES ('eee', 'eee', 'eee')

-- Print the current user and current table content
SELECT SUSER_NAME() AS LoginName, USER_NAME() AS UserName;
SELECT *, 'Columns 1 & 2 should be Masked' FROM TestTable

REVERT; -- Back to the session user context

-- Print the current user and current table content 
SELECT SUSER_NAME() AS LoginName, USER_NAME() AS UserName;
SELECT *, 'No columns should be Masked' FROM TestTable
  
-- Tidy up
DROP TABLE TestTable;
DROP USER user1;

最后一条语句的结果SELECT如下所示。此 select 是以具有UNMASK权限的用户身份执行的，但没有该权限的用户插入的两行仍然具有在显然被屏蔽的 中用作键的列MERGE。我怀疑是因为屏蔽值是在 期间插入的MERGE，而不是权限方面的问题。

这是一个已知问题吗？还是我做错了什么？