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