CODEWITHSUNDEEP
sql-serverdatabaset-sqlsecuritydata-masking
Jerry
Jerry

Reputation: 137

Data masking or security using Views?

Environment: SQL Server 2012 I am trying to help build a solution which includes data masking and encryption for our organisation.

Currently, we donot have any data masking in place and hence the need. We are in the process of identifying the data which could identify the data as sensitive or not or some combinations of non-sensitive data which could lead to the identity of a person.

One approach would be to use some kind of tool like Redgate Data generator or DataVeil, which could generate fictitious data for the database for the fields we want to for Dev or UAT environment. Other would be use some kind of function which would mask some characters as xxxx or **** based on the length.

In production environment, as per understanding, as masking is irreversible, encryption needs to happen which I will learn more about in coming weeks. This above scenario would work where every user would see same data in UAT and Dev when data is generated from a tool or masked using TSQl code and based on access to the key for production env. Please correct me on anything above you think doesn’t look right.

Next is user based access using views. There is not much material for security using views out there so asking on how we could implement if we take this route instead of the above mentioned.

I understand that the users could be granted access to the underlying tables using views. What about existing queries and SSRS reports and Cube? How could that work with views? Do I change every query ? I am little lost here.

Upvotes: 1

Views: 3301

Answers (2)

David Atkinson
David Atkinson

Reputation: 5909

You mentioned SQL Data Generator, which creates a fresh data set from scratch, but here at Redgate we also have Data Masker, which allows you to take an existing database and specify masking rules, which sounds like it might suit your scenario better.

Upvotes: 2

seagulledge
seagulledge

Reputation: 344

The View option can be done by creating a new 'mask' view that includes all the columns from the source tables, and replaces sensitive columns with a dummy fixed value.

For example: 

create view vMaskPeople
as
SELECT ID, DateCreated, 'Sample Name' as FullName, 'Sample Telephone' as Phone
FROM People

If you need more unique sample data, partially mask the columns, like:

SELECT ID, DateCreated, 
    Left(FullName,3)+'XXXXXX' as FullName, 
    'XXX-XXXX-'+Right(Phone,4) as Phone

If you are not able to somehow rig the Dev environments to use the new mask view, you could rename the source 'People' table to like 'People1' and then name the mask view 'People'

Upvotes: 3

Related Questions