Understanding Dynamic Data Masking in Azure SQL DB

Published 03-13-2019 07:25 PM 687 Views
Not applicable
First published on MSDN on Nov 02, 2018
Understanding Dynamic Data Masking.

When dynamic data masking is applied, the main objective is to limit the exposure of confidential information to users who do not have the necessary privileges to observe confidential information.

Problem Summary.

Once the masking is done in a column that shares tables and different queries are made using the UNION command, the masking will be reflected in both tables. We can observe the following scenario where the environment was replicated.



In the following screenshot you will see the creation of the primary table.



In the following screenshot you will see the table that has the PersonId and FirstName column linked using the INTO command to create the new table.



When run SELECT * FROM Person, you will observe the data in Person table.





When run: Select * from Student, you will see the Student table created with the two shared columns of the first table.



Once having the tables created with the column that is going to be masked, we go to the azure portal.



In Add Mask we create the masking rule and click on Save.





Once the column is masked users who do not have privilege to view masking will not be able to visualize the data.

Execute SELECT * FROM Person, in PersonId you will notice the data encrypted.





When we execute the following query Select * from Student, you will see the PersonID data since not masked in this table.



When I finally execute the two queries followed by a UNION , both tables will be masked.



The observed behavior is by design, data masking works by observing the output columns for a query and determining which columns of physical origin are used to produce the output data for that column. If any of those columns of physical origin has a mask, then that output column for the query will have its data masked. In the example provided, the query has two output columns. The sources for the first output column are Person.PersonID (which has a mask defined) and Student.PersonID, so all the output data for that column will be masked.
%3CLINGO-SUB%20id%3D%22lingo-sub-369111%22%20slang%3D%22en-US%22%3EUnderstanding%20Dynamic%20Data%20Masking%20in%20Azure%20SQL%20DB%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-369111%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Nov%2002%2C%202018%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20Understanding%20Dynamic%20Data%20Masking.%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20When%20dynamic%20data%20masking%20is%20applied%2C%20the%20main%20objective%20is%20to%20limit%20the%20exposure%20of%20confidential%20information%20to%20users%20who%20do%20not%20have%20the%20necessary%20privileges%20to%20observe%20confidential%20information.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20Problem%20Summary.%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Once%20the%20masking%20is%20done%20in%20a%20column%20that%20shares%20tables%20and%20different%20queries%20are%20made%20using%20the%20%3CSTRONG%3E%20UNION%20%3C%2FSTRONG%3E%20command%2C%20the%20masking%20will%20be%20reflected%20in%20both%20tables.%20We%20can%20observe%20the%20following%20scenario%20where%20the%20environment%20was%20replicated.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20In%20the%20following%20screenshot%20you%20will%20see%20the%20creation%20of%20the%20primary%20table.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89062iBA37C8DBB210CA16%22%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20In%20the%20following%20screenshot%20you%20will%20see%20the%20table%20that%20has%20the%20%3CEM%3E%20PersonId%20%3C%2FEM%3E%20and%20%3CEM%3E%20FirstName%20%3C%2FEM%3E%20column%20linked%20using%20the%20%3CEM%3E%20INTO%20%3C%2FEM%3E%20command%20to%20create%20the%20new%20table.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89063i3CE65472A22604B2%22%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20When%20run%20SELECT%20*%20FROM%20Person%2C%20you%20will%20observe%20the%20data%20in%20Person%20table.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CA%20href%3D%22https%3A%2F%2Fmsdnshared.blob.core.windows.net%2Fmedia%2F2018%2F11%2F3.png%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20%3C%2FA%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89064i13F6A3A8BD91834A%22%20%2F%3E%20%3CA%20href%3D%22https%3A%2F%2Fmsdnshared.blob.core.windows.net%2Fmedia%2F2018%2F11%2F3.png%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20%3C%2FA%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20When%20run%3A%20Select%20*%20from%20Student%2C%20you%20will%20see%20the%20Student%20table%20created%20with%20the%20two%20shared%20columns%20of%20the%20first%20table.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89065iE19945BD7C2123BB%22%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Once%20having%20the%20tables%20created%20with%20the%20column%20that%20is%20going%20to%20be%20masked%2C%20we%20go%20to%20the%20azure%20portal.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89066i179909CA2280119A%22%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20In%20Add%20Mask%20we%20create%20the%20masking%20rule%20and%20click%20on%20Save.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89067i4036C5E84F5076CC%22%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Once%20the%20column%20is%20masked%20users%20who%20do%20not%20have%20privilege%20to%20view%20masking%20will%20not%20be%20able%20to%20visualize%20the%20data.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Execute%20SELECT%20*%20FROM%20Person%2C%20in%20PersonId%20you%20will%20notice%20the%20data%20encrypted.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89068iC53350491F73BB97%22%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20When%20we%20execute%20the%20following%20query%20Select%20*%20from%20Student%2C%20you%20will%20see%20the%20PersonID%20data%20since%20not%20masked%20in%20this%20table.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89069i19B4D6BF205B2CDC%22%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20When%20I%20finally%20execute%20the%20two%20queries%20followed%20by%20a%20%3CSTRONG%3E%20UNION%20%3C%2FSTRONG%3E%20%2C%20both%20tables%20will%20be%20masked.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89070i5BBFED26D869F041%22%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20The%20observed%20behavior%20is%20by%20design%2C%20data%20masking%20works%20by%20observing%20the%20output%20columns%20for%20a%20query%20and%20determining%20which%20columns%20of%20physical%20origin%20are%20used%20to%20produce%20the%20output%20data%20for%20that%20column.%20If%20any%20of%20those%20columns%20of%20physical%20origin%20has%20a%20mask%2C%20then%20that%20output%20column%20for%20the%20query%20will%20have%20its%20data%20masked.%20In%20the%20example%20provided%2C%20the%20query%20has%20two%20output%20columns.%20The%20sources%20for%20the%20first%20output%20column%20are%20Person.PersonID%20(which%20has%20a%20mask%20defined)%20and%20Student.PersonID%2C%20so%20all%20the%20output%20data%20for%20that%20column%20will%20be%20masked.%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-369111%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Nov%2002%2C%202018%20Understanding%20Dynamic%20Data%20Masking.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-369111%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20SQL%20DB%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EColumns%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Edata%20masking%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETables%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eunion%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 13 2019 07:25 PM
Updated by: