{"id":70216,"date":"2017-03-08T17:39:21","date_gmt":"2017-03-08T17:39:21","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=70216"},"modified":"2021-09-29T16:21:16","modified_gmt":"2021-09-29T16:21:16","slug":"encrypting-sql-server-dynamic-data-masking","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/encrypting-sql-server-dynamic-data-masking\/","title":{"rendered":"Encrypting SQL Server: Dynamic Data Masking"},"content":{"rendered":"<p>SQL Server 2016 introduced dynamic data masking (DDM) as a way to prevent unauthorized users from viewing certain types of sensitive information. The database engine masks the data when it is retrieved from the database, based on masking rules defined on the column schema. The data stored within the database remains unchanged.<\/p>\n<p>When a user queries the database, the database engine determines whether that user account has the permissions necessary to access the data in its unmasked state. If the account does not, the engine applies the masking rules to the data when it is returned as part of the query. In this way, you can mask all or part of sensitive data such as national identification numbers, credit card numbers, birth dates, phone numbers, or other types of information.<\/p>\n<p>To implement DDM, you define masking rules on the columns that contain the data you want to protect. For each column, you add the <strong>MASKED<\/strong> <strong>WITH<\/strong> clause to the column definition, using the following syntax:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">MASKED WITH (FUNCTION = '<em>&lt;function&gt;<\/em>(<em>&lt;arguments&gt;<\/em>)')<\/pre>\n<p>You start by replacing the <strong><em>&lt;function&gt;<\/em><\/strong> placeholder with one of four functions: <strong>default<\/strong>, <strong>email<\/strong>, <strong>random<\/strong>, or <strong>partial<\/strong>. For the <strong>random<\/strong> and <strong>partial<\/strong> functions, you must also provide parameter values, as specified by the <strong><em>&lt;arguments&gt;<\/em><\/strong> placeholder. We\u2019ll get into the specifics of how all this works as we go through the examples.<\/p>\n<p>You can add the <strong>MASKED<\/strong> <strong>WITH<\/strong> clause to your column definition when you create a table or afterwards by using an <strong>ALTER<\/strong> <strong>TABLE<\/strong> statement. It does not matter whether the column already contains data. There are some limitations, however. For example, you cannot apply DDM to computed columns or Always Encrypted columns.<\/p>\n<p>While we\u2019re on the topic of Always Encrypted, it\u2019s worth noting that this is the fourth article in a series related to SQL Server encryption. The following links point to the first three articles.<\/p>\n<ul>\n<li><a href=\"https:\/\/www.simple-talk.com\/sql\/sql-development\/encrypting-sql-server-using-encryption-hierarchy-protect-column-data\/\">Encrypting SQL Server: Using an Encryption Hierarchy to Protect Column Data<\/a><\/li>\n<li><a href=\"https:\/\/www.simple-talk.com\/sql\/sql-development\/encrypting-sql-server-transparent-data-encryption-tde\/\">Encrypting SQL Server: Transparent Data Encryption (TDE)<\/a><\/li>\n<li><a href=\"https:\/\/www.simple-talk.com\/sql\/database-administration\/sql-server-encryption-always-encrypted\/\">SQL Server Encryption: Always Encrypted<\/a><\/li>\n<\/ul>\n<p>I\u2019ve included DDM in this series because Microsoft documentation (<a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/bb510663.aspx\">SQL Server Encryption<\/a>) implies that DDM is a type of SQL Server encryption. It is not. The feature simply masks data for non-privileged users upon querying a protected column. No data is being encrypted at rest or in motion. The database engine merely replaces the sensitive data with non-identifying characters. Even Microsoft admits that \u201cunprivileged users with ad-hoc query permissions can apply techniques to gain access to the actual data.\u201d<\/p>\n<p>Although DDM is not really encryption, it still seems worth including it in this series, if for no other reason than to be complete. The DDM feature is easy to implement and requires no changes to the queries themselves. You need only update your column definitions and perhaps tweak the permissions on certain accounts. But keep in mind that DDM should be used only as part of a much larger strategy for protecting data, a strategy that will likely include real encryption.<\/p>\n<h2>Applying a default mask<\/h2>\n<p>The first DDM function we\u2019ll tackle is <strong>default<\/strong>. When you use this function to mask data, and a user with read-only privileges queries that data, the database engine masks the entire value and returns a replacement value. The exact nature of the replacement value depends on the column\u2019s data type.<\/p>\n<p>It is important to note that the <strong>default<\/strong> function does not take any arguments. Microsoft\u2019s documentation is a bit confusing in this regard because it suggests otherwise, but you need only specify the function name and an empty set of parentheses.<\/p>\n<p>To demonstrate how this works, we\u2019ll start by creating a table and populating it with data from the <strong>AdventureWorks2014<\/strong> database. When creating the table, we\u2019ll use the <strong>default<\/strong> function to mask four of the columns, as shown in the following T-SQL script:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">USE master;\r\n  GO\r\n  CREATE DATABASE EmpData4;\r\n  GO\r\n  USE EmpData4;\r\n  GO\r\n  CREATE TABLE EmpInfo(\r\n  \u00a0 EmpID INT PRIMARY KEY,\r\n  \u00a0 FirstName NVARCHAR(50) NOT NULL,\r\n  \u00a0 LastName NVARCHAR(50)\u00a0\r\n  \u00a0 \u00a0 MASKED WITH (FUNCTION = 'default()') NOT NULL,\r\n  \u00a0 Birthdate DATE \u00a0\r\n  \u00a0 \u00a0 MASKED WITH (FUNCTION = 'default()') NOT NULL,\r\n  \u00a0 CurrentFlag BIT \u00a0\r\n  \u00a0 \u00a0 MASKED WITH (FUNCTION = 'default()') NOT NULL,\r\n  \u00a0 SalesLastYear MONEY \u00a0\r\n  \u00a0 \u00a0 MASKED WITH (FUNCTION = 'default()') NOT NULL,\r\n  \u00a0 EmailAddress NVARCHAR(50),\r\n  \u00a0 SickLeave INT,\r\n  \u00a0 SalesYTD MONEY,\r\n  \u00a0 NatID NVARCHAR(15),\r\n  \u00a0 PhoneNumber NVARCHAR(25));\r\n  GO\r\n  INSERT INTO EmpInfo\u00a0\r\n  SELECT e.BusinessEntityID,\r\n  \u00a0 sp.FirstName, sp.LastName,\r\n  \u00a0 e.BirthDate, e.CurrentFlag, sp.SalesLastYear,\r\n  \u00a0 sp.EmailAddress, e.SickLeaveHours, sp.SalesYTD,\u00a0\r\n  \u00a0 e.NationalIDNumber, sp.PhoneNumber\r\n  FROM AdventureWorks2014.HumanResources.Employee e\r\n  \u00a0 INNER JOIN AdventureWorks2014.Sales.vSalesPerson sp\r\n  \u00a0 ON e.BusinessEntityID = sp.BusinessEntityID\r\n  WHERE sp.CountryRegionName = 'United States';<\/pre>\n<p>The table includes a number of extra columns, which we\u2019ll be using in subsequent examples. For now, let\u2019s focus on the four DDM columns.<\/p>\n<p>The definition for each of these columns includes the <strong>MASKED<\/strong> <strong>WITH<\/strong> clause, which must come before the <strong>NULL<\/strong> or <strong>NOT<\/strong> <strong>NULL<\/strong> option. Within the clause, we specify the DDM function, in this case, <strong>default<\/strong>, enclosed in single quotes.<\/p>\n<p>With our table in place and populated, let\u2019s query the masked columns, using the same privileged account we used to create the table:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">SELECT TOP 5\r\n  \u00a0 EmpID, FirstName, LastName,\u00a0\r\n  \u00a0 Birthdate, CurrentFlag, SalesLastYear\u00a0\r\n  FROM EmpInfo;<\/pre>\n<p>Because we\u2019re running the query under a privileged account, we have full access to the data, despite having implemented the masking rules. For this reason, the <strong>SELECT<\/strong> statement will return the same results (shown in the following table) that we would get if the masking rules had not been added to the column definitions.<\/p>\n<table class=\"table--tight\">\n<thead>\n<tr>\n<td>EmpID<\/td>\n<td>FirstName<\/td>\n<td>LastName<\/td>\n<td>Birthdate<\/td>\n<td>CurrentFlag<\/td>\n<td>SalesLastYear<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>274<\/td>\n<td>Stephen<\/td>\n<td>Jiang<\/td>\n<td>1951-10-17<\/td>\n<td>1<\/td>\n<td>0.00<\/td>\n<\/tr>\n<tr>\n<td>275<\/td>\n<td>Michael<\/td>\n<td>Blythe<\/td>\n<td>1968-12-25<\/td>\n<td>1<\/td>\n<td>1750406.4785<\/td>\n<\/tr>\n<tr>\n<td>276<\/td>\n<td>Linda<\/td>\n<td>Mitchell<\/td>\n<td>1980-02-27<\/td>\n<td>1<\/td>\n<td>1439156.0291<\/td>\n<\/tr>\n<tr>\n<td>277<\/td>\n<td>Jillian<\/td>\n<td>Carson<\/td>\n<td>1962-08-29<\/td>\n<td>1<\/td>\n<td>1997186.2037<\/td>\n<\/tr>\n<tr>\n<td>279<\/td>\n<td>Tsvi<\/td>\n<td>Reiter<\/td>\n<td>1974-01-18<\/td>\n<td>1<\/td>\n<td>1849640.9418<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Notice that we did not have to change our query in any way and that the data is unchanged. When the database engine saw that we had access rights to that data, it returned the values just like they\u2019re stored in the database.<\/p>\n<h2>Controlling access to masked data<\/h2>\n<p>The key to controlling access to masked data is in the privileges granted to the user accounts. When I created the table and then queried the data, I was logged in with an administrative account. However, an account that has been granted only <strong>SELECT<\/strong> permission on the table will see the data as masked.<\/p>\n<p>To test this out, let\u2019s start by creating a local user account on the <strong>EmpData4<\/strong> database and granting that account the <strong>SELECT<\/strong> permission:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">USE EmpData4;\r\n  GO\r\n  CREATE USER user1 WITHOUT LOGIN;\r\n  GRANT SELECT ON OBJECT::dbo.EmpInfo TO user1; \u00a0\r\n  GO<\/pre>\n<p>This should all be fairly straightforward. We\u2019ve created an account in the database that is not linked to a login (to keep things simple for our testing), and then we granted the <strong>SELECT<\/strong> permission to that account to allow read access to the <strong>EmpInfo<\/strong> table.<\/p>\n<p>Now let\u2019s run our <strong>SELECT<\/strong> statement again, only this time, we\u2019ll do it within the context of the new user:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">EXECUTE AS USER = 'user1';\r\n  SELECT TOP 5\r\n  \u00a0 EmpID, FirstName, LastName,\u00a0\r\n  \u00a0 Birthdate, CurrentFlag, SalesLastYear\u00a0\r\n  FROM EmpInfo;\r\n  REVERT;<\/pre>\n<p>We\u2019re simply using the <strong>EXECUTE<\/strong> <strong>AS<\/strong> and <strong>REVERT<\/strong> statements to get the data that the new user would see. As the following table shows, the values in the four columns are now very different from how they\u2019re stored in the database.<\/p>\n<table class=\"table--tight\">\n<thead>\n<tr>\n<td>EmpID<\/td>\n<td>FirstName<\/td>\n<td>LastName<\/td>\n<td>Birthdate<\/td>\n<td>CurrentFlag<\/td>\n<td>SalesLastYear<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>274<\/td>\n<td>Stephen<\/td>\n<td>xxxx<\/td>\n<td>1900-01-01<\/td>\n<td>0<\/td>\n<td>0.00<\/td>\n<\/tr>\n<tr>\n<td>275<\/td>\n<td>Michael<\/td>\n<td>xxxx<\/td>\n<td>1900-01-01<\/td>\n<td>0<\/td>\n<td>0.00<\/td>\n<\/tr>\n<tr>\n<td>276<\/td>\n<td>Linda<\/td>\n<td>xxxx<\/td>\n<td>1900-01-01<\/td>\n<td>0<\/td>\n<td>0.00<\/td>\n<\/tr>\n<tr>\n<td>277<\/td>\n<td>Jillian<\/td>\n<td>xxxx<\/td>\n<td>1900-01-01<\/td>\n<td>0<\/td>\n<td>0.00<\/td>\n<\/tr>\n<tr>\n<td>279<\/td>\n<td>Tsvi<\/td>\n<td>xxxx<\/td>\n<td>1900-01-01<\/td>\n<td>0<\/td>\n<td>0.00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>To begin with, the database engine returns each <strong>LastName<\/strong> value as <strong>xxxx<\/strong>. The <strong>LastName<\/strong> column is defined with the <strong>NVARCHAR(50)<\/strong> data type. The database engine returns the <strong>xxxx<\/strong> value for all masked character data, unless the column is defined at a smaller size. For example, a <strong>CHAR(2)<\/strong> column will return a masked value of <strong>xx<\/strong>.<\/p>\n<p>The database engine handles date\/time values differently. Consider the <strong>Birthdate<\/strong> column, which is configured with the <strong>DATE<\/strong> data type. Each value is returned as <strong>1900-01-01<\/strong> (or whatever format is consistent with your regional settings). If this had been some variation of the <strong>DATETIME<\/strong> data type, such as <strong>DATETIME2<\/strong>, the returned values would be <strong>1900-01-01<\/strong> <strong>00:00:00:0000000<\/strong>.<\/p>\n<p>For numeric columns, the database engine returns <strong>0<\/strong> or <strong>0.00<\/strong>, if decimals are involved. In this case, the <strong>CurrentFlag<\/strong> column is configured with the <strong>BIT<\/strong> data type, which qualifies as numeric, and the <strong>SalesLastYear<\/strong> is configured with the <strong>MONEY<\/strong> data type.<\/p>\n<p>Regardless of the data type, the <strong>default<\/strong> function results in the entire value being somehow masked, which means an unauthorized user would not be able to infer the actual value other than to know the type of data, such as character or date.<\/p>\n<p>In some cases, you might want to allow a user to view the data as unmasked. To do so, you would grant the <strong>UNMASK<\/strong> permission to the account:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">GRANT UNMASK TO user1;\r\n  GO<\/pre>\n<p>Now when you run the <strong>SELECT<\/strong> statement as <strong>User1<\/strong>, the results will show the unmasked data. However, if you then decide that the account should not be able to access the data as unmasked, you can revoke the permission:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">REVOKE UNMASK TO user1; \u00a0\r\n  GO<\/pre>\n<p>After you run this statement, the user will again see the data as masked, as defined by the masking rules in the column definitions.<\/p>\n<h2>Verifying column masks<\/h2>\n<p>If you want to view which columns are masked in your database and how that masking is implemented, you can use the <strong>sys.masked_columns<\/strong> system view, as shown in the following <strong>SELECT<\/strong> statement:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">SELECT OBJECT_NAME(object_id) TableName,\u00a0\r\n  \u00a0 name ColumnName,\u00a0\r\n  \u00a0 masking_function MaskFunction\r\n  FROM sys.masked_columns\r\n  ORDER BY TableName, ColumnName;\u00a0<\/pre>\n<p>If you run the statement against our example database, you should see the following results.<\/p>\n<table class=\"table--tight\">\n<thead>\n<tr>\n<td>TableName<\/td>\n<td>ColumnName<\/td>\n<td>MaskFunction<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>EmpInfo<\/td>\n<td>Birthdate<\/td>\n<td>default()<\/td>\n<\/tr>\n<tr>\n<td>EmpInfo<\/td>\n<td>CurrentFlag<\/td>\n<td>default()<\/td>\n<\/tr>\n<tr>\n<td>EmpInfo<\/td>\n<td>LastName<\/td>\n<td>default()<\/td>\n<\/tr>\n<tr>\n<td>EmpInfo<\/td>\n<td>SalesLastYear<\/td>\n<td>default()<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>In this case, we\u2019ve defined masking rules on only four columns in the <strong>EmpInfo<\/strong> table, with all rules using the <strong>default<\/strong> function. If we had used functions that require arguments, those arguments would be shown here as well.<\/p>\n<h2>Applying an email mask<\/h2>\n<p>The next function we\u2019ll look at is <strong>email<\/strong>, which is used specifically to mask email addresses. The <strong>email<\/strong> function is as simple to implement as the <strong>default<\/strong> function, except that it returns somewhat different results. The <strong>default<\/strong> function masks the entire value, whereas the <strong>email<\/strong> function masks all but the first letter and, in some cases, the Internet domain (e.g., .com, .org, .edu).<\/p>\n<p>The behavior of the <strong>email<\/strong> function might seem a bit odd because it returns the .com domain for all email addresses. For example, if the email address ends in .org or .edu, the masked value will read .com, as it would if the domain actually were .com. Another way to look at this is that the function masks the domain for all email addresses except those that end with .com.<\/p>\n<p>Regardless of the domain issue, the process of defining the masking rule is quite straightforward:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">ALTER TABLE EmpInfo\r\n  ALTER COLUMN EmailAddress NVARCHAR(50) \u00a0\r\n  \u00a0 \u00a0 MASKED WITH (FUNCTION = 'email()') NULL;<\/pre>\n<p>In this case, we\u2019re simply modifying the <strong>EmailAddress<\/strong> column to include the masking rule, with the <strong>email<\/strong> function specified. Now let\u2019s query the table as our privileged user:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">SELECT TOP 5 EmpID, EmailAddress\u00a0\r\n  FROM EmpInfo;<\/pre>\n<p>As expected, the <strong>SELECT<\/strong> statement returns the results shown in the following table.<\/p>\n<table class=\"table--tight\">\n<thead>\n<tr>\n<td>EmpID<\/td>\n<td>EmailAddress<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>274<\/td>\n<td>stephen0@adventure-works.com<\/td>\n<\/tr>\n<tr>\n<td>275<\/td>\n<td>michael9@adventure-works.com<\/td>\n<\/tr>\n<tr>\n<td>276<\/td>\n<td>linda3@adventure-works.com<\/td>\n<\/tr>\n<tr>\n<td>277<\/td>\n<td>jillian0@adventure-works.com<\/td>\n<\/tr>\n<tr>\n<td>279<\/td>\n<td>tsvi0@adventure-works.com<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>This time, we\u2019ll run the <strong>SELECT<\/strong> statement as <strong>user1<\/strong>:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">EXECUTE AS USER = 'user1';\r\n  SELECT TOP 5 EmpID, EmailAddress\u00a0\r\n  FROM EmpInfo;\r\n  REVERT;<\/pre>\n<p>Now our results now look quite different.<\/p>\n<table class=\"table--tight\">\n<thead>\n<tr>\n<td>EmpID<\/td>\n<td>EmailAddress<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>274<\/td>\n<td>sXXX@XXXX.com<\/td>\n<\/tr>\n<tr>\n<td>275<\/td>\n<td>mXXX@XXXX.com<\/td>\n<\/tr>\n<tr>\n<td>276<\/td>\n<td>lXXX@XXXX.com<\/td>\n<\/tr>\n<tr>\n<td>277<\/td>\n<td>jXXX@XXXX.com<\/td>\n<\/tr>\n<tr>\n<td>279<\/td>\n<td>tXXX@XXXX.com<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>That\u2019s all there is to masking email addresses. You might decide it\u2019s just as easy to go with the <strong>default<\/strong> function to mask the data. In most cases, the value <strong>xxxx<\/strong> will work as effectively as <strong>tXXX@XXXX<\/strong>.<strong>com<\/strong>. The only advantage to the latter is that it telegraphs to the user that this value is a masked email address, and perhaps that\u2019s exactly what you want to do.<\/p>\n<h2>Applying a random mask<\/h2>\n<p>The next function available to the masking rules is <strong>random<\/strong>, which replaces numerical values with a random value within a specified range. For example, we can apply the function to the <strong>SickLeave<\/strong> column, which is configured with the <strong>INT<\/strong> data type:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">ALTER TABLE EmpInfo\r\n  ALTER COLUMN SickLeave INT\r\n  \u00a0 \u00a0 MASKED WITH (FUNCTION = 'random(1, 5)') NOT NULL;<\/pre>\n<p>In this case, we want the returned random value to fall between <strong>1<\/strong> and <strong>5<\/strong>. We define the range by adding the range limits as arguments to the function. We can do something similar with the <strong>SalesYTD<\/strong> column, which is configured with the <strong>MONEY<\/strong> data type:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">ALTER TABLE EmpInfo\r\n  ALTER COLUMN SalesYTD MONEY \u00a0\r\n  \u00a0 \u00a0 MASKED WITH (FUNCTION = 'random(101, 999)') NOT NULL;<\/pre>\n<p>This time we want the returned values to fall between <strong>101<\/strong> and <strong>999<\/strong>. Now let\u2019s run a query against these columns using our privileged account:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">SELECT TOP 5 EmpID, SickLeave, SalesYTD\u00a0\r\n  FROM EmpInfo;<\/pre>\n<p>Not surprisingly, we get the results shown in the following table.<\/p>\n<table class=\"table--tight\">\n<thead>\n<tr>\n<td>EmpID<\/td>\n<td>SickLeave<\/td>\n<td>SalesYTD<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>274<\/td>\n<td>27<\/td>\n<td>559697.5639<\/td>\n<\/tr>\n<tr>\n<td>275<\/td>\n<td>39<\/td>\n<td>3763178.1787<\/td>\n<\/tr>\n<tr>\n<td>276<\/td>\n<td>33<\/td>\n<td>4251368.5497<\/td>\n<\/tr>\n<tr>\n<td>277<\/td>\n<td>32<\/td>\n<td>3189418.3662<\/td>\n<\/tr>\n<tr>\n<td>279<\/td>\n<td>34<\/td>\n<td>2315185.611<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Next, we\u2019ll query the table as <strong>user1<\/strong>:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">EXECUTE AS USER = 'user1';\r\n  SELECT TOP 5 EmpID, SickLeave, SalesYTD \u00a0\r\n  FROM EmpInfo;\r\n  REVERT;<\/pre>\n<p>Now the returned values fall within the specified ranges, as shown in the following table.<\/p>\n<table class=\"table--tight\">\n<thead>\n<tr>\n<td>EmpID<\/td>\n<td>SickLeave<\/td>\n<td>SalesYTD<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>274<\/td>\n<td>2<\/td>\n<td>335.2532<\/td>\n<\/tr>\n<tr>\n<td>275<\/td>\n<td>1<\/td>\n<td>814.2846<\/td>\n<\/tr>\n<tr>\n<td>276<\/td>\n<td>3<\/td>\n<td>142.2145<\/td>\n<\/tr>\n<tr>\n<td>277<\/td>\n<td>2<\/td>\n<td>428.1147<\/td>\n<\/tr>\n<tr>\n<td>279<\/td>\n<td>5<\/td>\n<td>290.1132<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Notice that for the <strong>SalesYTD<\/strong> column, the database engine preserves the decimal, but still returns values within the specified range.<\/p>\n<p>Be careful when using the <strong>random<\/strong> function so you don\u2019t create a situation in which users that need accurate data assume that the information they\u2019re seeing is correct. As with the <strong>email<\/strong> function, you might be better off using the <strong>default<\/strong> function. At least that way, users are seeing all zeroes and will likely realize that the data is inaccurate. On the other hand, if your intention is to deceive non-privileged users, then by all means, use the <strong>random<\/strong> function.<\/p>\n<h2>Applying a partial mask<\/h2>\n<p>The final function available to the masking rules is <strong>partial<\/strong>, which allows you to specify exactly which values are masked. This function works best for column data that is consistently formatted, such as credit card numbers or national IDs.<\/p>\n<p>To demonstrate how the <strong>partial<\/strong> function works, we\u2019ll start by applying it to the <strong>NatID<\/strong> column, which stores social security numbers, without the hyphens. For these values, we want to mask all but the last four digits, as shown in the following <strong>ALTER<\/strong> <strong>TABLE<\/strong> statement:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">ALTER TABLE EmpInfo\r\n  ALTER COLUMN NatID NVARCHAR(15)\r\n  \u00a0 \u00a0 MASKED WITH (FUNCTION = 'partial(0, \"xxxxx\", 4)') NOT NULL;<\/pre>\n<p>To implement a <strong>partial<\/strong> masking rule, we must specify three arguments: how many opening characters to reveal, how many middle characters to mask and how to mask them, and how many trailing characters to reveal. In this case, we are revealing no opening characters (<strong>0<\/strong>), masking the first five characters with <strong>x<\/strong> values (<strong>xxxxx<\/strong>), and revealing the last four characters (<strong>4<\/strong>).<\/p>\n<p>Now we\u2019ll do something similar for the <strong>PhoneNumber<\/strong> column, only this time, we\u2019ll reveal the first four characters, apply the value <strong>xxx-xxxx<\/strong> to the next seven characters, and reveal no trailing characters:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">ALTER TABLE EmpInfo\r\n  ALTER COLUMN PhoneNumber NVARCHAR(25)\u00a0\r\n  \u00a0 \u00a0 MASKED WITH (FUNCTION = 'partial(4, \"xxx-xxxx\", 0)') NULL;<\/pre>\n<p>To see what the data looks like, we\u2019ll query the table with our privileged account:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">SELECT TOP 5 EmpID, NatID, PhoneNumber\u00a0\r\n  FROM EmpInfo;<\/pre>\n<p>As the following table shows, the results include the same data that\u2019s stored in the database.<\/p>\n<table class=\"table--tight\">\n<thead>\n<tr>\n<td>EmpID<\/td>\n<td>NatID<\/td>\n<td>PhoneNumber<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>274<\/td>\n<td>502097814<\/td>\n<td>238-555-0197<\/td>\n<\/tr>\n<tr>\n<td>275<\/td>\n<td>841560125<\/td>\n<td>257-555-0154<\/td>\n<\/tr>\n<tr>\n<td>276<\/td>\n<td>191644724<\/td>\n<td>883-555-0116<\/td>\n<\/tr>\n<tr>\n<td>277<\/td>\n<td>615389812<\/td>\n<td>517-555-0117<\/td>\n<\/tr>\n<tr>\n<td>279<\/td>\n<td>716374314<\/td>\n<td>664-555-0112<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Next, let\u2019s query the table as <strong>user1<\/strong>:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">EXECUTE AS USER = 'user1';\r\n  SELECT TOP 5 EmpID, NatID, PhoneNumber \u00a0\r\n  FROM EmpInfo;\r\n  REVERT;<\/pre>\n<p>This time, the <strong>NatID<\/strong> values and <strong>PhoneNumber<\/strong> values are masked according to our masking rules.<\/p>\n<table class=\"table--tight\">\n<thead>\n<tr>\n<td>EmpID<\/td>\n<td>NatID<\/td>\n<td>PhoneNumber<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>274<\/td>\n<td>xxxxx7814<\/td>\n<td>238-xxx-xxxx<\/td>\n<\/tr>\n<tr>\n<td>275<\/td>\n<td>xxxxx0125<\/td>\n<td>257-xxx-xxxx<\/td>\n<\/tr>\n<tr>\n<td>276<\/td>\n<td>xxxxx4724<\/td>\n<td>883-xxx-xxxx<\/td>\n<\/tr>\n<tr>\n<td>277<\/td>\n<td>xxxxx9812<\/td>\n<td>517-xxx-xxxx<\/td>\n<\/tr>\n<tr>\n<td>279<\/td>\n<td>xxxxx4314<\/td>\n<td>664-xxx-xxxx<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>The <strong>partial<\/strong> function can be a particularly useful tool, as long as the column values are in a consistent format. If they\u2019re not, you might need to separate those values into different columns or come up with another strategy.<\/p>\n<h2>Verifying column masks, again<\/h2>\n<p>Now that we have all our masking rules in place, let\u2019s query the <strong>sys.masked_columns<\/strong> system view once more:<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true\">SELECT OBJECT_NAME(object_id) TableName,\u00a0\r\n  \u00a0 name ColumnName,\u00a0\r\n  \u00a0 masking_function MaskFunction\r\n  FROM sys.masked_columns\r\n  ORDER BY TableName, ColumnName;\u00a0<\/pre>\n<p>Our results should now look similar to those shown in the following table.<\/p>\n<table class=\"table--tight\">\n<thead>\n<tr>\n<td>TableName<\/td>\n<td>ColumnName<\/td>\n<td>MaskFunction<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>EmpInfo<\/td>\n<td>Birthdate<\/td>\n<td>default()<\/td>\n<\/tr>\n<tr>\n<td>EmpInfo<\/td>\n<td>CurrentFlag<\/td>\n<td>default()<\/td>\n<\/tr>\n<tr>\n<td>EmpInfo<\/td>\n<td>EmailAddress<\/td>\n<td>email()<\/td>\n<\/tr>\n<tr>\n<td>EmpInfo<\/td>\n<td>LastName<\/td>\n<td>default()<\/td>\n<\/tr>\n<tr>\n<td>EmpInfo<\/td>\n<td>NatID<\/td>\n<td>partial(0, &#8220;xxxxx&#8221;, 4)<\/td>\n<\/tr>\n<tr>\n<td>EmpInfo<\/td>\n<td>PhoneNumber<\/td>\n<td>partial(4, &#8220;xxx-xxxx&#8221;, 0)<\/td>\n<\/tr>\n<tr>\n<td>EmpInfo<\/td>\n<td>SalesLastYear<\/td>\n<td>default()<\/td>\n<\/tr>\n<tr>\n<td>EmpInfo<\/td>\n<td>SalesYTD<\/td>\n<td>random(101, 999)<\/td>\n<\/tr>\n<tr>\n<td>EmpInfo<\/td>\n<td>SickLeave<\/td>\n<td>random(1, 5)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>As pointed out earlier, the results include the function arguments, where applicable, making it easier to pinpoint where you might be having issues should your masked data not be exactly what you expect.<\/p>\n<h2>Masking columns data<\/h2>\n<p>The DDM feature in SQL Server 2016 is a handy way to mask data without needing to modify your queries or applications. The four functions supported by the masking rules\u2014<strong>default<\/strong>, <strong>email<\/strong>, <strong>random<\/strong>, and <strong>partial<\/strong>\u2014are simple to implement within your column definitions. And because masking is controlled through SQL Server permissions, you can determine exactly who gets to see the unmasked data and who does not. Again, just be sure that masking is part of a larger security strategy and not your sole solution for protecting sensitive data.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Dynamic Data Masking  is  a good way of rendering data unreadable  for such purposes as user-acceptance testing, or demonstrating an application. It doesn&#8217;t encrypt the data, and a knowledgeable SQL user can defeat it.  However it provides a simple way to administer from the database what data  the various users of a database application  can and can not see, making it a useful tool for the developer.&hellip;<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[],"coauthors":[6779],"class_list":["post-70216","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/70216","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=70216"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/70216\/revisions"}],"predecessor-version":[{"id":72102,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/70216\/revisions\/72102"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=70216"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=70216"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=70216"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=70216"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}