SQL Server 2016 – Security: Dynamic Data masking – Numeric Data Types

This blog is a part of the series “SQL Server 2016 – Security: Dynamic Data masking“.
I remember you my different logins used to connect to AdventureWorks:

• sql login u1 with the db_owner role –> No masking
• sql login u2 with the db_datareader role –> masking

The Data Types used are: bit, tinyint, smallint, int, float & money.

I run this query to have all columns with the tested data type in the clause WHERE:

SELECT  T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE]
FROM SYS.OBJECTS AS T JOIN SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC='USER_TABLE' and T.NAME<>'sysdiagrams' and P.NAME='bit' ORDER BY T.NAME,C.NAME

Bit Data type

Let’s go with the bit Data Type 😛

I select one of these columns, for example: DimEmployee.SalesPersonFlag
With this Data Type, only the default masking mode works

ALTER TABLE [DimEmployee] ALTER COLUMN [SalesPersonFlag] ADD MASKED WITH (FUNCTION = 'default()')

And the question is, what the user u2 see in place of the real data…

I test with u2 and I’m surprise. Data are masked! Cool I have only ‘0’ for all data. 😉

Tinyint Data type

I select the column and type with this data type and I select one of this column for my test and use only the default masking mode:

With u1, I see all different values and 18484 rows

With u2, I see for all 18484 rows but with a value 0.

Smallint & int Data types

I do the same for these others “integer” data types:

• smallint Data type selection and masking:

• int Data type selection and masking:

With u1, I see all different values.

With u2, I see for all rows with a value of 0.

Float Data Type

Like all integers data types, the Dynamic Data Masking replace the value per 0 with the default masking mode.

With u1, I see all different values.

With u2, I see for all rows with a value of 0.

Money Data Type

Like all numerical data types, the Dynamic Data Masking replace the value per 0 with the default masking mode.

With u1, I see all different values.

With u2, I see for all rows with a value of 0 and NULL.

It is good to notice that a NULL value is not masked.

Core message

• Numeric Data Types tested use only the default masking mode
• The default value is 0
• A NULL value is not masked