Andy Lamb's questions

Usamos o MASKEDrecurso de coluna no SQL Server para ocultar dados confidenciais ao consultar. Mas nos deparamos com um cenário em que o INSERTexecutado como parte da MERGEinstrução é inserir um valor mascarado . Ou seja, o valor recém-inserido é visto como o valor mascarado, mesmo ao consultar a tabela como um usuário com a UNMASKpermissão.

Especificamente, o cenário ocorre quando:

1. Uma coluna é MASKED.
2. O usuário atual não tem UNMASKpermissão.
3. A cláusula MERGEda declaração ONusa a MASKEDcoluna.
4. A MERGEdeclaração não corresponde, então cai na INSERTseção.

Abaixo está uma reprodução mínima do problema.

-- 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;

O resultado da última SELECTdeclaração é mostrado abaixo. Este select está sendo executado como um usuário que tem a UNMASKpermissão, mas as duas linhas inseridas por um usuário sem essa permissão ainda têm a coluna usada como uma chave no MERGEaparentemente mascarado. Eu suspeito que seja porque o valor mascarado foi inserido durante o MERGE, em vez de qualquer problema com permissões.

Este é um problema conhecido? Ou estou fazendo algo errado?