MASKED WITH FUNCTION

What does MASKED WITH FUNCTION do?

Let’s assume that you want to hide the real names of your ninjas from the enemy (as you should, of course). Let’s further assume that you want to know them and store them in a database. I know that this assumption becomes slightly abstruse here, as of course every great master knows the names of his ninjas and would never store them. But let’s still assume that you wanted to save them. Of course, you would then leave no stone unturned to protect your warriors. And with dynamic data masking, SQL Server 2016 and later will help you protect the identity of your warriors.

How can you use MASKED WITH FUNCTION?

First of all, we have to create a table where we want to store the master data of the ninjas:

DROP TABLE IF EXISTS NinjaWarriors

CREATE TABLE NinjaWarriors 
(
     NinjaId int NOT NULL IDENTITY(1,1)
    ,RealFirstName varchar(250) NOT NULL
    ,NinjaName varchar(250) NOT NULL
    ,SecretPower varchar(250) NOT NULL
)

Next we will insert some rows into that table:

INSERT INTO NinjaWarriors 
(RealFirstName, NinjaName, SecretPower)
VALUES 
('Tobi', 'Blackclaw', 'Makes beer disappear'),
('Fred', 'Lethalstain', 'Talks to whales')

Let us now create a user in our database to query the ninja table:

DROP USER IF EXISTS Enemy

CREATE USER Enemy WITHOUT LOGIN;
GRANT SELECT ON NinjaWarriors TO Enemy;

The REVERT at the end of this statement is used to return to the original user context. Now let’s change the table definition to protect the data in the table. From the ninja real name we want to show only the first and last letter:

ALTER TABLE NinjaWarriors 
ALTER COLUMN RealFirstName ADD MASKED WITH (FUNCTION = 'partial(1,"XXX",1)')

You see that we change the definition of the column similar to how we would add a DEFAULT value.

When you query the table with your normal user, you will still see all the data you added to the table, but you will query the table again with the user Enemy:

EXECUTE AS USER = 'Enemy';
SELECT * FROM NinjaWarriors;  
REVERT;

Then you see that the first names have been masked to “Txxi” and “Fxxd”. Similar to what you used the Partial function here, you can use other functions to protect your data:

ALTER TABLE NinjaWarriors
ALTER COLUMN SecretPower ADD MASKED WITH (FUNCTION = 'default()')

If you now query the table as “Enemy”, you will see that instead of the secret powers of your ninja fighters, you will simply see “xxxx”.

How can you give a user the right to see the masked data? You can give a user the “umask” permission to see the masked data. Let’s create another user for this:

DROP USER IF EXISTS Friend
CREATE USER Friend WITHOUT LOGIN;
GRANT SELECT ON NinjaWarriors TO Friend;

EXECUTE AS USER = 'Friend';
SELECT * FROM NinjaWarriors;  
REVERT;

Your friend can’t read the data of the table, because he was created identically to the enemy user. Now let’s give him the right to read the masked data:

GRANT UNMASK TO Friend

EXECUTE AS USER = 'Friend';
SELECT * FROM NinjaWarriors;  
REVERT;

The user can now see all data of the table again. If you now want to undo the masking of a table, you can use DROP to remove the MASKED property:

ALTER TABLE NinjaWarriors
ALTER COLUMN SecretPower DROP MASKED

EXECUTE AS USER = 'Enemy';
SELECT * FROM NinjaWarriors;  
REVERT;

The enemy can now read all secret abilities again, but the names are still masked for him.

Now the enemy could try to be clever and bypass the masking and get the SQL Server to write the unmasked data to another table where it can retrieve it:

EXECUTE AS USER = 'Enemy';
SELECT * INTO #WantToKnow FROM NinjaWarriors;  
REVERT;

But even here the enemy will be disappointed, because the SQL Server is intelligent enough to notice that the user Enemy is not allowed to read the data and therefore only stores the masked data in the new table.

Remember: the ninja always keeps his mask on, even in the database…

To find out which columns in which tables of your database are masked, you can use the following query:

SELECT 
     c.name
    ,tbl.name as table_name
    ,c.is_masked
    ,c.masking_function  
FROM sys.masked_columns AS c  
JOIN sys.tables AS tbl   
    ON c.[object_id] = tbl.[object_id]  
WHERE is_masked = 1;  

References

• Dokumentation von Microsoft