Introduction

In previous levels of this stairway, we have looked at how Dynamic Data Masking (DDM) works, the various masks that can be configured, and the security implications of using this feature. Here we continue examining another aspect of security: data movement. Users that connect to SQL Server often have the ability to move data to new tables and files through queries, which can be a concern for maske data. Let's examine how this feature works with different types of data movement.

Temporary Tables

One of the easiest ways to move data inside a SQL Server database is to copy the data to a temporary table. Let's take a simple query and move some data. I'll execute this as a user that only sees masked data. I'll copy data into a temp table and then retrieve it from the same temp table.

As I see in the results, the product name and unit price are still masked in the temp table. This makes sense, but is the data being masked or is it actually stored as masked? Let's see.

I'll revert my security connection and then select both from the original table, which should be unmasked, and the temporary table. When I do that I see this:

The data in the temp table is masked, since this user can see the unmasked data in the original table. Thus, DDM provides us with seucre data if a user moves the data to a temporary table.

What if an administrator does the same thing? I'll execute the same code above, but use my administrative account to create and populate the temp table, then select from the temp table as the user, 'bsmith', who doesn't have the UNMASK permission. The code I'll run is:

SELECT top 10
 ProductName, UnitPrice
 INTO #Product
 FROM dbo.OrderDetail
GO
EXECUTE AS USER = 'bsmith'
GO
SELECT top 10
  *
  FROM #Product
SELECT top 10
 ProductName, UnitPrice
 FROM dbo.OrderDetail
GO
REVERT
GO
DROP TABLE #Product
GO

When I do this, I get these results.

In this case, the temporary table contains unmasked data. The raw data is copied, and none of the DDM masking is transferred to the temporary table. A regular user can see the data.

Regular Tables

We see the temporary tables behave as just raw containers for the new data, without any properties being transferred. However, if I create a new user table, does the same thing apply? Let's see.

SELECT TOP 10
 ProductName, UnitPrice
 INTO ProductStaging
 FROM dbo.OrderDetail
GO
EXECUTE AS USER = 'bsmith'
GO
SELECT top 10
  *
  FROM dbo.ProductStaging

When this runs, I get this result.

As expected, a new table created doesn't have any rights granted to other users. Let's fix that.

GRANT SELECT ON dbo.ProductStaging TO bsmith
GO

Now, let's query ProductStaging as user, bsmith. We see these results.

There is no masking applied. This makes sense. If I script the create statement for both tables, here's the code I receive.

CREATE TABLE [dbo].[ProductStaging](
[ProductName] [varchar](200) NULL,
[UnitPrice] [numeric](10, 4) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderDetail](
[OrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NULL,
[ProductName] [varchar](200) MASKED WITH (FUNCTION = 'partial(5, "xxxxxx", 0)') NULL,
[Qty] [int] NULL,
[UnitPrice] [numeric](10, 4) MASKED WITH (FUNCTION = 'random(0.1000, 0.9000)') NULL,
[ProductOPen] [varchar](200) NULL,
 CONSTRAINT [OrderDetail_PK] PRIMARY KEY CLUSTERED 
(
[OrderDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

This means that like triggers and other table properties, the masking properties are not copied to new tables. This is important for privileged users to understand. If you export data for a normal user to a new table, you will not be including the masked properties on columns. You will need to reapply those masks to sensitive columns.

Of course, if a low privileged used copies data from the original table, it will still be masked as the query follows the same results shown in previous levels of this Stairway Series.

BCP

One of the common ways to move data out of SQL Server, other than by query, is through bcp. Let's see how I'd do this as a privilged user. Since this user can see the raw data in the table, I don't expect any masking to be applied. This is exactly what we see.

Now let's do the same thing with the bsmith user. Note, I changed the password for this demo, and then changed it again ;).

Masking works, and bcp pulls data the same as another query. However, I'd used a query here. We'd expect that to work. What if I pull the whole table? Let's get this down to a reasonable number as there are a million rows in this table.

DELETE dbo.OrderDetail
WHERE OrderID > 100

Now run the bcp again.

Note: I had to reload the table as I accidentally deleted all the rows, so the IDs are different. However, the table structure and settings are the same.

I see masked data here, and once again, all prices are < 1. The raw data reloaded into the table has readable product names and prices mostly above 1.

Using Other Tools

All other tools, such as SSIS, will make a connection and use a query or bulk exporting to move data out of a SQL Server table. If these tools use a user account that doesn't have the UNMASK permission, they will have the same results as we've shown with SELECT queries in previous articles, or the bcp here. DDM will mask data for those columns where it is applied.

This is a good reason to not use generic accounts for ETL operations that may have db_owner permissions or UNMASK granted. Any exports performed by users should use normal, lower privileged accounts.

Summary

Dynamic Data Masking handles the obfuscation of data for any queries, including export queries, made by users. This ensures masking is maintained when adding data extraction capabilities to your application, or using a reporting tool, such as Reporting Services.

As shown in previous articles, this is not a security feature. This is a developer programming convenience feature that exists to consistently mask data for your application without front end developers writing code. However, if a user can run ad hod queries, they can use a variety of techniques to recover the original values from many fields. Do not count on this as a data security mechanism.