After my first blog on Security with Row level Security, I test the second Security new feature in the next version of SQL Server: Dynamic Data Masking.
I presented this new feature to my Oracle colleagues and I’m surprised that Oracle 12c has the same option known as Data Redaction.
The principle is simple: for some users, data are masked on-the-fly…
Dynamic Data Masking Principle
Combined with the Transparent Data Encryption (TDE), you  will never permit to let sensitive data in clear form in the database.
Dynamic Data Masking Principle with TDE
The masking is done on a column level by simple rules.
It is very simple:
As user, I asked to have the personal phone number of our new secretary but I received 0041-79-XXX-XX-XX… The phone number is masked! 😥
Phone number masking sample

Masking Mode

In Oracle, you find 4 masking mode:

  • Full redaction: all content redacted with default characters/digits
  • Partial redaction: Part of the data is redacted. For fixed size data fields
  • Regular expression: usage of regular expression to match and replace specific pattern in variable size data fields
  • Random redaction: random data displayed depending on the data type

Thanks to my colleague Cyril Wasmer for these info.

In SQL Server, you find 3 masking modes:

  • Default: Full masking according to the data types of the designated fields.
  • Email: Masking method which exposes the first letter of an email address, the “@” character and the constant suffix “.com”
  • Custom String: Masking method which exposes the first and last letters and adds a custom padding string in the middle

Extract from msdn, here

Test

To test this new feature, I check rapidly columns present in the database AdventureWorks

Query used:

1
2
3
4
5
6
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' ORDER BY T.NAME,C.NAME;

Columns in AdventureWorks
As you can see, we have many columns in this database.

Before I begin a test, I will see what my data type are.

Query used:

1
2
3
4
5
SELECT DISTINCT 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' order by SIZE;

List of Data Types in AdventureWorks

I have 16 data types in this database (xml, bit, tinyint,smallint,int,date,datetime,float,money,sysname,char,nchar,nvarchar, varchar, varbinary and kleinString).
KleinString is just a custom data type in varchar. The result will be the same as varchar, then I exclude this data type from my study.
For my test and in the name of the fun, I will take a column per type… Let’s go!

I connect to AdventureWorks with a:

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

SQL Server version used for these test is Microsoft SQL Server 2016 (CTP2.4) – 13.0.600.65 (X64)
I will split my article in three others parts. I will perform several tests against AdventureWorks, as follows:

Summary

I will write a quick summary for each tests.
Result of the Dynamic Data Masking per data types
I hope this convinces you to go see this new feature.

See you soon for a next episode! 😎

Post Views: 952
Thumbnail [60x60]
by
Stéphane Haby