How To Implement Dynamic Data Masking in Fabric Data Warehouse

Hi, in this video, I'm going to show you how to implement dynamic data masking in Fabric

Data Warehouse. So, let's get started. Basically, a dynamic data masking is one of the security features in SQL Server and now

in Fabric Data Warehouse that helps to protect and limit the exposure of sensitive data to

non-privileged and unauthorized users. So in this video, quickly, I'm going to show you how we can implement that across different

users in our organization. Enough of talking, let's get started. In this case, I do have this data masking warehouse workspace created

And of course, I brought in the data from Excel using the Data Flow Gen 2

And of course, the data is loaded into the hld underscore info data warehouse

I'm going to come into the warehouse so we can see the M records table

And of course, you can see different kind of columns such as the M ID, full name, gender

marital status, email, date of hire, year in service. We can also see other columns such as the qualifications, the department, the national

insurance number, annual salary, and the pension. Now, I'm going to go back to the workspace

So basically, I share this workspace, the data warehouse, particularly to one of the

users in my organization. I click on this horizontal ellipsis and then manage access

There we go. So we can see Enoch Abiola, who is just a viewer

So I'm going to go to the Chrome. And this is users

I shared the data warehouse weight. So Enoch Abiola. And of course, you can see under the workspaces that data masking warehouse is visible to

this particular user. And of course, we can see the warehouse itself

There we go. We can see the M records table. And of course, we can see all the columns such as the M ID, full name, and so on and

so forth. Just like what I can see as the admin, as the owner of the particular workspace

So in this case, there is absolutely no masking applied to any of the columns

I'm going to go back to Edge and let's go to the warehouse itself

So we'll actually create a new query. So new query window. Now first, let's actually write a simple select top three star from the M records table

So let's run and just see the top three rows. So there we go

Now, I'm just going to move this down a little bit. So I'm going to put in a semicolon

And now let's apply data masking to the monitor status and the email address columns first

So I'm going to use the alter table. I want to alter the M records

So we'll alter the column, which column the, in this case, monitor status

So I'm going to get rid of this. Okay, so we'll actually add masked weights and then open a bracket

So I'm going to call functions. We want to apply the default data masking

So I'm just going to call it default with open and close in a bracket and I can close

this function. Now this is all done. So let's just go ahead and copy this

I'm going to copy this and put in a semicolon control V

So we want to apply masking to the email. So I'm going to get rid of this particular monitor status and let's replace with email

So I'm going to run the two queries. So there we go

So you can see succeeded just in 739 milliseconds. So of course, when I actually call the table, I can see all the records, the column because

I'm the admin. Okay, let's go to Chrome and in this case, I'm going to refresh the page

And after the refresh, you can see the monitor status, the email address columns are now masked

So the person can actually see those information and that is super cool

Let's go back to the admin, the edge. So let's apply the dynamic masking

So the annual salary, that's going to be annual salary and the pension. So control C, let's put in a semicolon control V, semicolon control V

So I'm going to replace this with annual salary and get rid of this and want to replace email

with pension. So we want to apply the default. So select the two queries again, click on run and oh, there's a problem

We already select the query. Okay. And then click on run and there we go

So we can see succeeded just in 598 milliseconds. That's fine. Let's go back to the Chrome and I'm going to refresh

And when I scroll to the right, I can see we have bunch of zeros for the annual salary

and then pension. So this is working fine. So this is a way of actually hiding those important information

So for the last part, we actually want to hide some of the national insurance number

Now in this case, we have the two letters to the left side of the cells and then we

have the separator which is the iPhone and of course we have about six numbers

So we want to actually hide these two letters and then the six numbers and they won't expose

only the last letters to the right. So let's go back to the user, the admin user

So I'm going to scroll down. Now I'm just going to copy this. Ctrl C. So let's Ctrl V

Now to get rid of this pension and NI underscore number. Now I want to apply the partial masking to this national insurance

So I'm going to get rid of this default. Okay. So first, I'm going to call the single quote and then I'm going to type in the partial

Now the partial requires three arguments, the starting index and the second argument

is the replacement string and the third one is the length of substring to be replaced

So let's call the function the partial open bracket and let's close the bracket for now

Now inside the bracket, we want to actually start from zero and then put in the comma

Now I'm going to open two double quotes. Now inside the double quotes, let's go back to the data

We actually want to hide the two letters to the left and then the six numbers in between

So that's going to be XX and then the separate to the iPhone XXX XXX six times

So and then of course, we have to put in the iPhone again and then I'm going to put in

a comma and then we want to expose the last letter to the right or the CYU and so on

So all things being equal, this is fine. So let's go ahead and execute or run the query

And let me scroll up. There we go. Succeeded just in one second, 516 milliseconds

So let's go back to the other user and let's refresh the URL

Okay, I'm going to scroll to the right and there we go. So we can see the national insurance number are masked

So we can only see the last letter to the right side of the cells

Basically how we can apply dynamic data masking in Fabric Data Warehouse

I trust you enjoyed this video. Do like, share with your friends and comment

Thank you and bye for now. Cheers