DP-203 Data Engineering on Microsoft Azure – Design and Implement Data Security Part 2

4. Azure Synapse – Customer Managed Keys

Hi, and welcome back. Now, in this chapter, I quickly want to go through the concept of being able to do encryption of your Azure Synapse workspace with the help of your keys defined in your Azure Key vault. So if you want to use Customer Manage keys when it comes to encryption for your Azure Synapse workspace, this is something that you can do when you create the Azure Synapse workspace itself. So, for example, if I go on to my Azure Keyword, if I go on to encryption keys, let me create a new encryption key first. I’ll give a name and hit on Create.

Now, once I have this key in place here, if I create a new resource, and if I choose a zero Synapse, I’ll hit on Create. So here, let me just quickly choose the resource group. I’ll just give a workspace name. I won’t create the workspace. I just want to go ahead and show you the option where you define the use of Customer managed keys. Yeah, I’ll choose my storage account. Leave everything as it is. Now, in security, when you scroll down, there is something known as workspace encryption. So this provides the ability to double the encryption configuration that you have for your Synapse workspace. And this can only be done during the creation of the Synapse workspace itself.

So here you can enable the use of the Customer Manage key. Here, you can select the keyword and the key. So here I can select my keyword, and here I can select my encryption encryption key. Hit on select, and then you can go ahead with the creation of the Synapse workspace. So here we just have the ability to ensure that you have double encryption for your Synapse workspace by using your own Customer Manage key that is defined in your Azure Key vault.

5. Azure Dedicated SQL Pool – Transparent Data Encryption

Hi, and welcome back. Now, in this chapter, I quickly want to go through the concept of being able to do encryption of your Azure Synapse workspace with the help of your keys defined in your Azure Key vault. So if you want to use Customer Manage keys when it comes to encryption for your Azure Synapse workspace, this is something that you can do when you create the Azure Synapse workspace itself. So, for example, if I go on to my Azure Keyword, if I go on to encryption keys, let me create a new encryption key first. I’ll give a name and hit on Create. Now, once I have this key in place here, if I create a new resource, and if I choose a zero Synapse, I’ll hit on Create. So here, let me just quickly choose the resource group. I’ll just give a workspace name. I won’t create the workspace. I just want to go ahead and show you the option where you define the use of Customer managed keys.

Yeah, I’ll choose my storage account. Leave everything as it is. Now, in security, when you scroll down, there is something known as workspace encryption. So this provides the ability to double the encryption configuration that you have for your Synapse workspace. And this can only be done during the creation of the Synapse workspace itself. So here you can enable the use of the Customer Manage key. Here, you can select the keyword and the key. So here I can select my keyword, and here I can select my encryption encryption key. Hit on select, and then you can go ahead with the creation of the Synapse workspace. So here we just have the ability to ensure that you have double encryption for your Synapse workspace by using your own Customer Manage key that is defined in your Azure Key vault.

6. Lab – Azure Synapse – Data Masking

Now, in this chapter we are going to look at Data Masking. So, if you want to hide information about data, let’s say in a particular column from your users, let’s say that the column is storing sensitive information like credit card information, etc. And you want to ensure that when queries are fired against a table in Azure Synapse, that information is not showed onto users. You can use the feature of Data Masking. So here the data in the table can be limited in its exposure onto non privileged users. And I’ll explain this when we go into our lab. You have to create a rule that can actually mask the data.

Based on the rule, you can decide on the amount of data that is actually exposed onto the user. Now, there are different masking rules in place. You have the credit card masking rule. This is used to mask the column that contains credit card details. Here, only the last four digits of the field are exposed. You also have the email masking column that is normally used for email addresses that are stored in the columns. You also have custom text. Here you can decide which characters to expose for a field. And then you have a random number. Here you can generate a random number for the particular field.

So let’s go on to Azure Synapse and let’s see an example on how we can work with Data Masking. Now, for the purpose of this demo, I am going to copy the table which is available in my Adventure Works database. So this is the email address table. So just as an example, here we have the email address. So let me copy it onto a table into Azure Synapse. So for that we can use the integrate path in Azure Synapse itself. Here I can create a pipeline based on the Copy Data tool. I’ll use the built in Copy task. Go on to next. Here. I’ll choose my connection as AdventureBOX. I’ll search for email. I’ll choose that. I’ll go on to next. I’ll go onto next. Here I’ll choose Synapse. So here it will automatically create my target table. I’ll go on to next. Now here I don’t need the rogue ID, so I’ll just delete that.

So we just have these four columns. I’ll go on to next. I’ll disable staging and let me just do a bulk insert. I’ll go on to next. I’ll go on to next. So it will create a pipeline and run it. I’ll hit on finish. I’ll go on to monitor just Filter. Based on my most recent pipeline, it’s Y one T. I can see it has already succeeded. So if I go on to SQL Server Management studio now, if I go on to the tables and refresh, which is in my dedicated SQL pool, I can see the email address. If I right click and I select the rows, I can see the rows in place. Now let’s go ahead and perform Data Masking. So now let’s say we want to apply a mask onto this email address column. So for this, I’ll go on to all resources. I’ll search for my dedicated SQL Pool. It’s a separate resource. I’ll go on to it. Now, here under security, I have something known as dynamic data masking. Let me just hide this. So here it is actually giving a recommendation on what are the possible columns that can be mass.

So it has gone ahead, it has read the different tables and based on its analysis, it’s saying that you can add a mask for these columns. You can also clearly see it’s asking us to mask the email address column in the email address table. So here I can also click on Add Mask and here I can choose my person schema my email address table. And then here I can choose the email address column and here in the masking I can choose the email masking function, I can click on Add and then I can click on Save. So now it will add that email masking function onto our email address column.

Now if I come onto SQL Server Management Studio and I execute my query, I can still see all of the information in the email address column. And that’s because we are looking at the information as an administrator. Has a SQL administrator. If you go on to your data masking rules here you can see that administrators are always excluded when it comes to the masking rules. So to see the masking rule in action, we are going to execute these set of commands. So I’ll just copy this onto SQL Server Management studio. So here what we are doing. First is we are creating a new user, but this time without the need of having a separate login. There’s something that you can do.

Then I am granting the select permission for this particular table onto that user. Then we will execute the below command. Now has user a So we’ll select star from the same table and then we’ll revert control back onto our main SQL administrator. So first let’s execute this command and then grant the select permission. Now let’s execute has a new user and let’s select star from the email address. And now here you can see the masking function in effect. So for your users, if you want to mask certain parts of your data, you can actually use the data masking rules or the functions that is available if you go ahead and add a mask. Let’s say I choose another column.

So let’s say I choose an email address ID. Yes, if I choose the default value masking function, if it’s a number, you will only see zero at all points in time. So for example, if I add this masking rule and again click on Save. And now let me again run this. So you can see the email address ID has been replaced by the number zero if you have a custom masking rule that needs to be in place. So let me add a mask. And here, let me choose another schema. Here, let me choose another table.

And here, if I, let’s say choose the customer name here, I can choose a custom string as well. Here I can decide how much I want to expose in terms of the beginning of the string, how much do I want to expose in terms of the end of the string and what should be my padding string. So for example, if I have seven characters in my string and I want to expose the first two characters and let’s say the last one character, and since I have seven characters in my string, then I can have my padding string. So it will show the first two characters, then it will show a star.

Star, star, star. And then the last character. So you can also have these custom masking rules in place as well if you want to ensure that you remove the masking rules. So just go on to the rule itself, hit on Delete and then ensure to click on Save. And here, if you run this again so you can see the data hazardous and then don’t forget to revert control back on to the SQL Server administrator, right, so in this chapter, want to go through the data masking feature that is available in Azure Synapse.

• Category: Uncategorized