How to dynamic data masking on Sql Server
While working on an external database I noticed something interesting, I was looking date in a transaction table, all of its values where ‘1900-01-01’. I start wondering what was happening there, until I realized that I didn’t have UNMASK database permission. I realized that this and other fields on the table where masked.
This masking property is made in order to protect data so other users without the current permissions will not be able to see the actual data.
There are 4 types of data mask functions.
- Default, Mask the field with the default mask of the data type.
- Email, Mask the email in a way that we are only able to see some meaningful values
- Random, Mask any numeric data type with random values defined into a range.
- Partial, Allows you to take values from the front and back of a value, replacing the center with a value that you give.
For displaying this property, we are going to create a table and insert some values.
/* DYNAMIC DATA MASKING CREATING TABLE */ CREATE TABLE DMASKING_TEST( FIRST_NAME VARCHAR(100) NULL, LAST_NAME VARCHAR(50) NULL, CREDIT_CARD_NUMBER VARCHAR(100) NULL, NUMBER_DEBIT_CARDS INT NULL, EMAIL_ADDRESS VARCHAR(100) NULL, BIRTHDAY DATE NULL, ) /*INSERTING VALUES INTO TABLE*/ INSERT INTO DMASKING_TEST VALUES ('ALVARO','PARRA','4321567890121234' ,5,'email@example.com','19890501')
DMASKING_TEST contains the following information
Data Mask Methods
After that we must apply dynamic data masking to columns in DMASKING_TEST table.
/*ALTERING TABLE*/ ALTER TABLE DMASKING_TEST ALTER COLUMN FIRST_NAME ADD MASKED WITH (FUNCTION = 'default()') ALTER TABLE DMASKING_TEST ALTER COLUMN LAST_NAME ADD MASKED WITH (FUNCTION = 'default()') ALTER TABLE DMASKING_TEST ALTER COLUMN CREDIT_CARD_NUMBER ADD MASKED WITH (FUNCTION = 'partial(4,"********",4)') ALTER TABLE DMASKING_TEST ALTER COLUMN EMAIL_ADDRESS ADD MASKED WITH (FUNCTION = 'email()') ALTER TABLE DMASKING_TEST ALTER COLUMN BIRTHDAY ADD MASKED WITH (FUNCTION = 'default()') ALTER TABLE DMASKING_TEST ALTER COLUMN NUMBER_DEBIT_CARDS ADD MASKED WITH (FUNCTION = 'random(1,3)')
Now changes are applied on the table. In my case if I SELECT the table, I am not going to be able to check the applied masking, because I am logged with ‘sa’ user and this user has all privileges including UNMASK.
What we need to do now is to create a user without that privilege, this query will help us for that.
/*CREATING USER*/ CREATE USER test_user WITHOUT LOGIN; GRANT SELECT ON dbo.DMASKING_TEST TO test_user;
and we are going to execute the query with a different user.
/*CHANGING USER AND EXECUTING SELECT QUERY*/ EXECUTE AS USER = 'test_user' SELECT * FROM DMASKING_TEST
Query is going to return the next results
- As you can see ‘FIRST_NAME’, ‘LAST_NAME’ and ‘BIRTHDAY’ fields are masked with the default function, this function returns ‘xxxx’ if the field is a kind of string and it returns ‘1900-01-01’ if the field is a date.
- ‘NUMBER_DEBIT_CARDS’ field is masked with random function, this function can be applied only to numeric types and it returns a random number between the range establish within the function. Ex random(1,3).
- ‘EMAIL_ADDRESS’ field is masked with email function, this function mask email addresses.
- ‘CREDIT_CARD_NUMBER’ field is masked with partial function, this function puts between characters a masked part and it is very useful, especially talking when you need to handle credit or debit card numbers.
When dealing with a database, specially an OLAP one, try to see if it contains masked values or if you have permission to read them unmasked, this can save you a lot of time, it can affect your analysis and in some way your results.