SQL | Dynamic Data Masking | How to mask sensitive data | MS SQL

hello everyone and welcome to another sequel tutorial on our Channel learn at no star today we are going to be talking about Dynamic data masking Dynamic data masking is a technique to prevent exposure of sensitive information like credit cards to unprivileged users now there are other techniques as well which should be implemented along with Dynamic data masking like auditing like row level security and encryption which we are not going to go into details in this video but these need to be implemented for a stronger security level of the database the dynamic data masking is basically to prevent some other users who are not authorized to view the sensitive information so it will be masked and some forms let's see how we can implement this in SQL Server so we're going to be working with a table called the membership table and you can see it has some information like the first name the last name the phone the email uh there's a date of birth column and there is also some credit card information so there might be a need to hide or mask some of these information for some unauthorized users so we'll see how this can be implemented through SQL Server now most of the examples that we are using in this tutorial are already provided on the Microsoft site itself so I will provide the link to that page also and you can go through that link and you can go through that or navigate through the examples there as well so let's first try to mask the data in a string column so the first name column and we will also try to hide it in a column discount code which is a numeric column and I'll also try to hide it for the column date of birth which is the date time column so these are different data types which we are going to try to mask with the different arguments and different functions that are available in SQL Server so to mask the data in a column we are going to define the column as a mass column in the table structure so if it is an existing table then we are going to be needing the older table statement to alter the table structure and the column structure and Define it as a masked column so to alter the table we simply need the alt table statement so I'm going to use the alt table statement and give the table name and then I'm going to use the alt column statement and give the column name so I'm just first going to start with the first name column and then what you have to mention is ADD must with brackets function equal to and here is where you can provide the options for this function so the first option that we're going to explore is the default option so I'm just going to write default again bracket and this is going to be my ALT table statement I'm just going to execute this query and it should be altering the first name column and the table membership and marking it as a masked column in the format default so I'm just going to execute this query this query executes fine now there were two other columns on which I wanted to execute this query so I'm just going to copy and paste and do it so the other column I wanted to do it was on discount code because I just wanted to check how it would behave if the column is um numeric column so we are just going to execute this query as well and it should return fine and I'm just going to copy it again and execute it for a date of birth column which is a date time column so we would be able to see what would be the output for a daytime column with the option default for unprivileged users now if we execute this query again we just select star from membership you will see that there is no impact to the data the data is as it is and I can view the complete details this is because the user that I am using has access or privilege on this data so that I can view the data the original data but let's now go ahead and try to create a user who does not have access so we are going to create a test user and then we are going to execute as test user this particular query and we're going to see the mass data create the test user we are simply going to use the create user statement and let's call him test user and because we do not want to define a retail login credentials for this user I'm just going to say without login and then I'm going to provide a simple select access to a schema to this particular user so to Grant the select access I'm going to use the grant statement so Grant select on schema and this is the syntax you need to follow so in my case the schema is dbo within the database training uh to the user that we just created so this is test user so we have here granted a select access to this user so I want very too much about this or these statements of creating the user and running the access at this point this is more for the database admin guy but it is good to know how to create these test users so now we have created a user we have executed uh we have given this access let's see [Music] um okay so the user already exists we just need to pass on this excess which has been passed on to this user so now so the next step is to execute our select statement as this user because since we are logged in as admin user to execute any statements we have to write this statement execute as user equal to test user so once we have this statement we are going to execute the further queries as this user and we can see whether the data visible to this user is going to be must or unmasked so I'm just simply going to write my select statement for the membership table so select star from membership and if I run these statements after this executes user test user this is the output that we are going to get so you can see that the first name column has been replaced by four X's it was a string column so this is the default Behavior any number or any length of the string column it will be replaced by just four X's if it is of a length Which is less than 4 then it will be replaced by less than 4 X's uh the discount Port column you can see it has been replaced by zero so any number column will be replaced by zeros and for any date time columns there would be a default value default date value which is 1900 0101 which would be replacing the date values but there might be case this is where we would not be warning this default behavior and we would want to mask them differently so there are other options that can be used and we can mask these columns in a different way so let's go back to our queries for masking and here let's add one more masking query first to see another option that can be used with this Mass function so you can see that there's a column called email over here so there's an explicit option called email which is specifically for email values for masking email values so if I use that we'll see how it works instead of default you just need to say email over here I'm just going to execute this query for the email column and I'm going to go back and execute as test user here again and now we see you will see that the email has also been masked so you can see the first character it gets retained the.com gets retained and the remaining characters get replaced by XXX and there's an ad in between that you see so there is a finite number of x's always irrespective of what exactly is the email so there would be the first character then three X's then add and then forexes then.com and so on so this is the behavior of the email option now let's explore some other options as well so the other option that we're going to explore is again for a numeric value so like a value in discount code column so here you can actually specify a range of values that you want to replace these values with so we already have a mask on the discount code column here if we go we already put a mask now I want to alter this mask so I'm going to alter this mask I want in The Mask I already have the mask added on this column so this add over here has already added the mask so for just changing the mask on an already mask column what you need to do is give a statement like this you just do not need to provide this word add so what you need to do is say alter column column name mask width and then you'd need to Define your function you do not need the word add here because it is an already mask column so the mask has already been added on this column now the function is what we're going to change here so we are going to change it to random and then we are going to Define our range so let's say the range is going to be 20 to 30 so this is simply how you can Define the range over here and then we are going to execute this query once again so here you can see we've got an error message that there is an incorrect syntax this is because you need to mention the data type as well here so the data type for this column is small n and this is what we have mentioned over here and now you can see this is the correct format so we are going to order this column when you're going to order an already mask column then there's a little bit different format instead of this ad you need to provide the data type over here so I'm just going to execute this query now and they should work correctly now let's go back to our test user and try to execute the query again on the membership data so this is a test user I'm going to execute it again and you can see that the discount code has been replaced by random values falling between 20 and 30. so now let's try another option with the phone number column so in the phone number you can see that there is a format that we have got there are three digits then there's a DOT then again three digit standard Dot and four digits and so on so uh there is another option which kind of allows you to define a custom sort of masking it doesn't I mean it's not totally custom sorting it has its limitation but a basic custom sorting it would allow you to do so for that the option is called partial and we're going to partially make visible the prefix the and the suffix and then we're going to replace the portion within the prefix and suffix so anything in between by a definite string that we're going to provide so let's try to do this and see what are going to be our results so let me just copy this all to table statement and write it again for the phone number column is going to copy it and what we're going to do now is let's add four add Mass with function is equal to partial and to the arguments you provided this function are the prefix so prefix is the number of characters that you want to display so you if you say one one character would be displayed as it is and then the remaining part would be uh substituted by the substrate that you're going to provide here or if you say two then two characters would be two original characters would be displayed so let's say we want to display the two original characters and then we are going to provide a padding for in between so the padding is going to be something like X let's say x dot XXX dot x x and then let's see we want only one character from the end to be displayed so I'm just going to provide one over here so so now if I execute this statement the command has been executed successfully going back to my test user I'm going to execute this and you can see that the first two characters have been retained the remaining the string or the characters in between have been replaced by the substring that we provided and the last character has been retained as well so you can provide a custom sort of uh masking for some columns also like credit card number information that is also most of the times it is the last four digits which are visible and the other digits are mass so one two three four five six seven eight so eight digits we can mask uh in this case in our case so we let's go back and I'm just going to copy this again and include this for the credit card number column as well so let's call it CC number and then uh we want nothing from the prefix to be there so I am going to say 0 then I want how many X's I want is eight so one two three four five six seven eight and then I want the last four digits to be as it is displayed so that is what I'm going to say over here so just executing this part of the query and going back to a disk user and you can see that all the other digits have been replaced by x's and only the last four characters are visible for the credit card number as well now please note that this partial um masking technique is available only for string columns now there's another option called late time that is applicable for daytime columns like the date of birth column that we had but it is only applicable on SQL Server 2022 and later so I'm not going to discuss it in this video but on the link that I'm going to provide all the options would be there now let's say we want to provide this user finally access to this data then that can be done as well you can just provide the unmask access to this user so you can write Advantage statement and simply say Grant and mask to the user that we you have created so this user so if you run the statement he should be able to see the mass data but before doing that let's go and see how you can unmask a column so let's say now I do not want to have this uh particular let's say on the date of birth column I don't want to have that mass data on the date of birth column I want to unmask the data so all the users should be able to see the data from the date of birth column or the discount code column so what you can do in that case is simply drop the mask on that column so again for that you have to copy you have to write an alter table statement so what you need to do in this case is Alter table membership alter column and then simply say drop masked okay so we missed the column name and if you execute this query then the mask on this column would be dropped so if we go back to our user and execute the query and we have node yet provided the unmasked to this user and if you execute this you will see the original values with the discount code column so the mask has been dropped for this user now let's say I actually want to unmask the access for this user then you can just say Grant and mask to test user and so I cannot do it here because this particular thing is executing and stress user and you can not unmask or give an access to yourself using your own account so let's try opening a new query window and maybe it will allow us to do that I'm just going to do that and the command has been succeeded here going back to execute as test user if you execute it now you should be able to see all the original data as is because we have planted the unmasked access for this particular user so this was a brief overview of how you can implement the dynamic data masking in SQL server and what features and options are available with the mask with function I hope that this video was useful to you all the relevant information is provided in the links Below in the description box thanks a lot for watching stay tuned and please subscribe to our YouTube channel thanks a lot for watching goodbye

Channel: Learn at Knowstar

Views: 14,705

Keywords: best, training, tutorial, coding, knowstar

Id: KRXrxANuHnc

Length: 16min 47sec (1007 seconds)

Published: Wed Nov 09 2022