Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted October 23, 2017

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

Dynamic Data Masking on Azure SQL Database

By Anoop Kumar

Introduction

Data security is a major requirement for any application. Every time we create an application, we must follow all security policies before going live with that application.

In SQL Server, various methods are available to implement data security: data encryption at the database level using TDE, cell level data encryption, always encryption, and many more. All these methods have their own pros and cons. There will always be a tradeoff between data security and performance in data read/write operations. These methods ensure data security, and comply with all expected security policies, however they impact overall application performance.

Microsoft introduced a new feature, dynamic data masking, which is available in SQL Server 2016 onwards, on both on-premise and Azure SQL database versions.

Dynamic Data masking (DDM) is one of the methods to hide sensitive information from non-privileged users. This feature helps to mask the data in a specific format without exposing the complete data. The best part of this feature is that it does not have any impact on the application; the data will be partially or fully masked and exposed to users to fulfil their needs.

As an example, a table contains an SSN number and an application support engineer may identify a user by the last four digits of the social security number, without exposing the full SSN to the support engineer. A masking rule can be defined that masks all the digits not to be exposed to support engineer but the last four digits of the social security number return in the result set of queries. Similarly, as per application needs, other data such as email addresses or credit card numbers, etc. can also be masked.

SQL Server provides four functions to mask the data and there are multiple ways to implement data masking on any column data. The generic method is T-SQL commands to implement dynamic data masking on both On-premise and Azure database. Also, the Azure table configuration blade can be leveraged to implement DDM on a column in Azure SQL Database.

SQL Server engine uses a different character or value for masking the original value of a designated column to be masked. The value or character used varies, based on the data type of the designated data type of the column to be masked. Refer to the table below to understand the character or value of data mask.

Data Type

SQL Server Data Type

Mask Value

String

char, nchar, varchar, nvarchar, text, ntext

X

Number

bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real

0

Date Time

date, datetime2, datetime, datetimeoffset, smalldatetime, time

01.01.1900 00:00:00.0000000

Binary

binary, varbinary, image

single byte of ASCII value 0 

Data Masking functions

default() : The default() function masks the full value of a column. The masked character will appear as per data type of the designated field to be masked. If the data type of the column is string then the mask function replaces the original value with X. If the data type of the column is a number then the mask function will replace the original value with 0 (zero). Refer to the above table to understand the relation between masking character and SQL server data types.

Example

Original Value in Column

Masked Value for Non-privileged Users

Definition Syntax with Function

123.456.1234

XXXX

Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL

email() : The email() function helps in masking the email address stored in a column. The function will mask the whole string of the email address except the first letter of the email, the special character of address (@), and constant suffix of the email address (.com).

Example

Original Value in Column

Masked Value for Non-privileged Users

Definition Syntax with Function

abcd@outlook.com

aXXX@XXXX.com

Email varchar(80) MASKED WITH (FUNCTION = 'email()') NULL

random() : The random()  function is used on any numeric type to mask the original value with a random value within a specified range.

Example

Original Value in Column

Masked Value for Non-privileged Users

Definition Syntax with Function

99000

321

employee_salary int MASKED WITH (FUNCTION = 'random([start range], [end range])')

employee_salary int MASKED WITH (FUNCTION = 'random([101], [999)')

partial() : The partial () function gives the option to customize the masking as per business needs. The function exposes the first and last letters and adds a custom padding string in the middle. If the original value in the column is too short to complete the defined mask rule, part of the prefix or suffix will not be exposed. The partial() function is useful to mask SSN or credit card numbers. Using this mask function, we can define the rule to expose only the last 4 digits of the whole number.

Example

Original Value in Column

Masked Value for Non-privileged Users

Definition Syntax with Function

123-45-6789

XXX-XX-6789

SSN varchar(11) MASKED WITH (FUNCTION = 'partial(prefix, [padding],suffix)') NULL

SSN varchar(11) MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)')

Overall, DDM is the method to mask your data, while not exposing sensitive data to non-privileged users. This method is a great way to limit data exposure without impacting the application. The most important benefit of DDM is that it doesn't change data at a physical level in the table or column and data masking can be achieved without introducing any change in the application. In future articles, we will discuss how T-SQL commands and the Azure table configuration blade helps in implementing DDM.

See all articles by Anoop Kumar



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server – Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM