KapilSQLgeek

SQL SERVER – Random function in Dynamic Data Masking in SQL Server 2016 CTP 3

Advertisements

Hi friends,

With new CTP version of SQL Server 2016 many new functionality are added and modified. As with introduction SQL 2016 new functionality Dynamic data masking get introduced which helps in protecting the confidential data from users who do not have access to view it. In earlier CTP version data masking were of three types:

  1. Default
  2. Custom
  3. Email

I have already written about these in my earlier blogs here :

https://kapilsqlgeek.com/2015/07/26/sql-server-2016-blogs/

In CTP 3.0 of SQL 2016 one more type gets added with name Random.

Random function can be used for any numeric datatype to mask the value with any random number defined within a specified range.

Random ([start range], [end range])

In this blog I will give you the overview of new Random function with an example.

Let’s first create a table Demo_Masking with some data in it.

Create table Demo_Masking

(

EmployeeId INT,

FirstName varchar(10),

LastName varchar(20),

Salary INT

)

GO

—-INSERT dummy data into Demo_masking table

INSERT INTO Demo_Masking (EmployeeId, FirstName, LastName, Salary)

SELECT 11, ‘Kapil’, ‘Singh’, 50000

UNION ALL

SELECT 12, ‘Ankit’,’Sharma’,35000

UNION ALL

SELECT 13, ‘Aditya’, ‘Sinha’, 45000

Here, I have created an employee with some columns, but I don’t want user to see the salary of other users so I will mask it with any random numbers.

Now, I am altering the table Demo_masking and adding masking function on columns Salary using this syntax:

ALTER TABLE Demo_Masking

ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = ‘random(1,90000)’);

After applying masking, I will create a user ‘Masking’ which will have only read access (SELECT) on table Demo_Masking.

CREATE USER Masking WITHOUT LOGIN

GRANT SELECT ON Demo_Masking to Masking

Now let me execute below query –

SELECT * FROM Demo_Masking

GO

EXECUTE AS USER = ‘Masking’

SELECT * FROM Demo_Masking

REVERT

After executing queries with current user and new user which has only SELECT rights we see that the salary column is showing random values for it and restricting the user to see the original salary of other users. In this way we can use random function to mask the data with any random numbers.

Hope you will like this post.

Advertisements

Advertisements