Data Masking

Question

Data Masking

liunit

Old Hand

Points: 327
More actions
December 2, 2021 at 1:39 pm

#3959553
Hello All,
I'm working on a project that the manager would like to mask some sensitive data. I know that there is a dynamic data masking function currently on the sql server 2019, but as far as I researched looks like it need to be done on a table column right? On my case the company have a portal that is a information consumer from another database and all the "retrieve" data is done using views.... I tried to use the masked with on the view column without success. We can't mask the origin table that is a part of a different database and the users with grant needs to see the data as it is. The only way I can see to achieve what they need is to create a user defined function passing the original value and retuning the column value masked.. But I have some questions for you:
1. Can I use the masked built in function on a view column ?
2. The user defined function always return one type right? but I was planning to create a function based on the column type like 'if varchar) mask this way, if number mask this way, if date, mask this way..... There is a way to return a value based on a function parameter ? like text, numeric or datetime ?
3. Someone have a way of doing that ?
I can't use MASKED WITH (FUNCTION= if I can't use it on a select statement (in my case view)
I really appreciate in advance any reply..............

BR,
Luiz Marques
- This topic was modified 2 years, 5 months ago by liunit.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24703 More actions · Answer 1

Quick check online, masks are applied at the table level, not the view level. So to answer question 1 no you cannot use the built-in masking function on a view column - only a table.

One approach you could do (maybe not ideal) would be to use a stored procedure to get your data. The stored procedure would pull the data from the source into a temp table or table variable which has the column(s) you want masked. Then the stored procedure would do a select from the temp table/table variable and present it to the application.

This does mean you need to use a stored procedure to get the data rather than the view mind you. The only other way I can think to do this would be to mask the data at the source tables.

ALTERNATELY - you could mask it outside of SQL - mask it at the application layer.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

frederico_fonseca SSCoach Points: 16041 More actions · Answer 2

and do be aware that Data Masking in SQL server can be broken very easily if you can do queries directly to the DB - so avoid at all cost.

ZZartin SSC-Dedicated Points: 30900 More actions · Answer 3

Well if the people you need to mask the data for are only using a view just mask the data in the view and give the people who need access to the unmasked data access to the base table(s) or a different view.

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 4

You could also define the SELECT statement of the view so that it masks the data actively as the query runs. Just and idea.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning