Uncategorized

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

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

M1

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

M2

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s