• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

SQL Server 2016 New Features – Dynamic Data Masking

June 7, 2016 by Andy Hayes Leave a Comment

sql server 2016 dynamic data maskingData 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

Filed Under: All Articles, SQL Server Administration Tagged With: security, sql server, sql server 2016

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

CAPTCHA
Refresh

*

Primary Sidebar

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©