Sql-server – Dynamic Data Masking Doesn’t Seem To Work Correctly With ISNULL

azure-sql-databasedata-maskingdynamic-data-maskingsql server

Here is the code to reproduce the issue:

CREATE TABLE [dbo].[EmployeeDataMasking](
    [RowId] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] NULL,
    [LastName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
    [FirstName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
 CONSTRAINT [PK_EmployeeDataMasking] PRIMARY KEY CLUSTERED 
(
    [RowId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
) ON [PRIMARY]
GO

Insert Into dbo.EmployeeDataMasking (EmployeeId, LastName, FirstName)
VALUES( 1,'Smithsonian','Daniel'),( 2,'Templeton','Ronald')

-- Partial data masking works correctly here
Select  
    EmployeeId,
    LastName,
    FirstName
From dbo.EmployeeDataMasking

-- Partial data masking does not work correctly here. Full masking is used.
Select  
    EmployeeId,
    ISNULL(LastName,'') as LastName,
    ISNULL(FirstName,'') as FirstName
From dbo.EmployeeDataMasking

enter image description here

It appears, when using ISNULL, the partial masking is being converted to a full (default) mask. Is this the expected behavior?

Update: I discovered that COALESCE does not exhibit this problem. These queries return the expected results:

Select  
    EmployeeId,
    COALESCE(LastName,'') as LastName,
    COALESCE(FirstName,'') as FirstName
From dbo.EmployeeDataMasking

Using Azure SQL Database

Best Answer

As a test, I created a custom user function that does nothing except pass the string back to the caller; it also exhibits this behavior.

Be aware, this code will drop users and objects, so only run it in a test database.

First, we create the required objects, users, and insert some data:

IF OBJECT_ID(N'dbo.EmployeeDataMasking', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.EmployeeDataMasking;
END
IF OBJECT_ID(N'dbo.SomeFunc', N'FN') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.SomeFunc;
END
GO

IF EXISTS (
    SELECT 1
    FROM sys.database_principals dp
    WHERE dp.name = N'SomeUser'
    )
BEGIN
    DROP USER [SomeUser];
END
CREATE USER [SomeUser] WITHOUT LOGIN;
ALTER ROLE db_datareader ADD MEMBER [SomeUser];

CREATE TABLE [dbo].[EmployeeDataMasking](
    [RowId] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] NULL,
    [LastName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
    [FirstName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
 CONSTRAINT [PK_EmployeeDataMasking] PRIMARY KEY CLUSTERED 
(
    [RowId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
) ON [PRIMARY]
GO

GO
CREATE FUNCTION dbo.SomeFunc(@i varchar(50))
RETURNS varchar(50)
AS
BEGIN
    RETURN @i;
END
GO
ALTER AUTHORIZATION ON dbo.SomeFunc TO [SOmeUser];
GRANT EXECUTE TO [SomeUser];
GRANT SHOWPLAN TO [SomeUser];


Insert Into dbo.EmployeeDataMasking (EmployeeId, LastName, FirstName)
VALUES( 1,'Smithsonian','Daniel'),( 2,'Templeton','Ronald')

Next, we'll query the data from the perspective of the non-privileged user:

EXECUTE AS USER = N'SomeUser';
Select  
    EmployeeId,
    ISNULL(LastName,'') as LastName,
    ISNULL(FirstName,'') as FirstName,
    dbo.SomeFunc(LastName) as LastName,
    dbo.SomeFunc(FirstName) as FirstName,
    COALESCE(LastName,'') as LastName,
    COALESCE(FirstName,'') as FirstName
FROM dbo.EmployeeDataMasking
REVERT

The results:

╔════════════╦══════════╦═══════════╦══════════╦═══════════╦══════════╦═══════════╗
║ EmployeeId ║ LastName ║ FirstName ║ LastName ║ FirstName ║ LastName ║ FirstName ║
╠════════════╬══════════╬═══════════╬══════════╬═══════════╬══════════╬═══════════╣
║          1 ║ xxxx     ║ xxxx      ║ xxxx     ║ xxxx      ║ SmXXXXan ║ DaXXXXel  ║
║          2 ║ xxxx     ║ xxxx      ║ xxxx     ║ xxxx      ║ TeXXXXon ║ RoXXXXld  ║
╚════════════╩══════════╩═══════════╩══════════╩═══════════╩══════════╩═══════════╝

As you can see, the custom user function, dbo.SomeFunc exhibits the same behavior as ISNULL. At this point, I'm unsure why, since COALESCE does not exhibit the unwanted behavior.

Related Question