Dynamic Data Masking – Altering the masked column

By Samir Behara on October 28, 2017 • ( 0 )

Data Security is one of the major concerns in organizations these days. SQL Server 2016 has a number of security features which makes it one of the most secure databases till date.

SQL Server 2016 provides a new built-in security feature called as ‘Dynamic Data Masking‘ which helps protect sensitive and personally-identifiable information from unauthorized access by masking stored data. We can configure users who can have access to the unmasked data and for other unauthorized users, we can just show the masked data.

It’s very important to understand that sensitive data needs to be only exposed to the people who need to see it. Application end users, Developers and Customer Care representatives are not among them.

Application end users should have access only to the data which they need to see.
Developers who have access to the database for development work or troubleshooting a problem, should not be allowed to see sensitive data.
Call Center Representatives ask you the last 4 digit of your SSN for verification purpose. They do not need to see the entire unmasked sensitive data.

Dynamic Data Masking Working

The best thing about Dynamic Data Masking is that the data is masked on the fly and the underlying data in the database does not change. It is very simple to enable this feature in existing databases. There is no need of any application additional code changes in the front end application to take advantage of this functionality.

I wrote an article describing Dynamic Data Masking. Read it for more detailed information – Dynamic Data Masking in SQL Server 2016

We enable Data Masking using in built functions in the database side, and the masked information is displayed in the user interface, without the need of any code changes. Simple to implement.

How to alter/drop a masked column?

Dynamic Data Masking can be implemented in new tables or even existing tables in your database. In both cases, there is no additional coding required at the user interface side.
There might be scenarios where you might need to alter the masked column after the initial implementation. Wondering if it is possible? Yes it is.

You can alter/drop an existing masked column as per your requirement by just writing a simple T-SQL query —

	— Modify your existing masked columns
	ALTER TABLE dbo.Customer
	ALTER COLUMN LastName ADD MASKED WITH (FUNCTION = 'partial(2,"X-XXX-X",1)')

	— View the unmasked data in Customer table
	SELECT * FROM dbo.Customer;