Thursday, May 31, 2018

Use ALTER COLUMN statement with a default() mask to apply data masking

Here is what I am going to do.

Here is a query that retrieves FirstName, LastName, City from Employees table in the Northwind database on my SQL Server 2016 SP2 named instance OHANA.

Use Northwind
Go
SELECT FirstName, LastName, City
FROM Employees


Now I will apply a datamask to the FirstName column with a default() datamask using the following Alter Table, Alter Column statements.
-----------------------
Use Northwind
Go
Alter table Employees
Alter Column FirstName nvarchar(10) Masked with (Function='default()')
-----------------------------
When I run the above query, the query runs without errors. whenever I run the query, the data is not masked for me, because I have permissions.

To test the masking I create a user, hacker3 as follows:
--
/* create a hacker3 user*/
CREATE USER hacker3 WITHOUT LOGIN;
GRANT SELECT ON EMPLOYEES TO hacker3;
EXECUTE AS USER='hacker3'
----

DataMask_01
 Now I run a SELECT query, the same one we used earlier and get this response.


DataMask_02

You will observe that the FirstName column is masked. Note that the FirstNames in the table ranges from having 4 to 8 letters but the default() masking function replaces them with 3 xxx's.

Note that if you run a new query, you are not running as hacker3.

I have used the 'hacker' a couple of times while testing.



No comments:

Post a Comment

Do you know what you installed while custom installing SQL Server 2022?

 When you install any software in Windows OS, the key information gets into the Registry and some of which you can look up in the control pa...