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…
Combined with the Transparent Data Encryption (TDE), you will never permit to let sensitive data in clear form in the database.
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! 😥
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
To test this new feature, I check rapidly columns present in the database AdventureWorks
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;
Before I begin a test, I will see what my data type are.
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;
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:
- SQL Server 2016 – Security: Dynamic Data masking – Numeric Data Types
- bit, tinyint, smallint, int, float & money
- SQL Server 2016 – Security: Dynamic Data masking – String Data Types
- sysname, char, nchar, nvarchar & varchar
- SQL Server 2016 – Security: Dynamic Data masking – Other Data Types
- xml, date, datetime & varbinary
See you soon for a next episode! 😎