Data security is always a hot subject and anything that database software vendors can do to enhance the security of data is usually welcome. One of the new features of SQL Server 2016 is the introduction of Dynamic Data Masking.
As it says on the tin, the data is masked which prevents non-privileged users from seeing it.
With Dynamic Data Masking in SQL Server 2016, there is control over how much of the data is masked from unauthorized viewers. In the case of a call center for example, as part of the authentication process, it may require customers to supply the last 4 digits/characters of a field in the database which is unique to them. In the United States, this might be a social security number for instance. The call center operator would not see the entire field, instead those 4 digits would be the only characters on display.
How to setup Dynamic Data Masking in SQL Server 2016
Masking rules may be defined using T-SQL functions at table design time. In this example I am using SQL Server 2016 RC3.
Let’s say we wanted to store some data specific to users and that the email address field should not be totally exposed when viewed from the application.
CREATE TABLE T1 (ID IDENTITY PRIMARY KEY, Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);
After inserting a row and connecting as a user that just had select permissions on the table, the data returned by my query shows the mask that has been applied from the email() function :
Email -------------------- [email protected] (1 row(s) affected)
Connecting to the same table as a user with elevated permissions such as db_owner returns the data in an unmasked form.
But isn’t data masking the same as encryption?
No, the two serve different purposes and should not be confused with each other.
With data encryption, the data in the tables is physically altered and stored in an encrypted format. This leads to changes in storage requirements and inclusion of encryption keys and functions to de-crypt the data making it very secure.
Data masking however does not alter the data that is stored inside of the database. There are no physical changes required at the database level and anyone with direct access to the physical data can read or write to the data in a typical way using normal SQL commands.
Data masking should never be used as a replacement for encryption.
For more information on this new feature in SQL Server 2016 including the other masking functions which are available, please check out the official documentation from Microsoft via this link
Leave a Reply