How to ignore empty strings with SQL Server 2016 Dynamic Data Masking?

Question

I'm implementing SQL Server 2016 Data Masking as a test as part of an overarching effort to give an application a 'demo' mode which doesn't expose real data when showcased.

We have a mobile number field which follows the pattern 0411 222 333 (Australian mobile number), and the partial mask below correctly applies the standard mask we're after, which is to say 04XX XXX X33

ALTER TABLE Resource
ALTER COLUMN MobileNumber ADD MASKED WITH (FUNCTION='partial(2, "XX-XXX-X",2)')

However, if the value of the column is an empty string, (''), the mask is still applied, albeit with no data to mask:

Is there a way to ensure empty strings are ignored by the masking effort?

I don't think so. It seems to me that if the mask could become shorter depending on the length of the data, that itself could reveal information about the data. For example, what if the string is only two characters long? If you display XX, you are revealing that the number (if it is a number) is between 10 and 99. If you really need this to happen in the data layer, you can write a view that transforms XXXX to empty string and deny select on the table itself.. The problem with that is if there's a 4 digit number, it will appear that there's no value there. — Eric
– Eric, Commented Jul 1, 2016 at 1:17
@Eric - In our case it's less about what data can be inferred so much as what is displayed in a live/system demonstration. We're looking into SS2016's data masking as an alternative to setting up & maintaining a separate obfuscated database, so users can click around in a real/current/live system without exposing them to the 5% of data that they shouldn't see. In this case, 'empty string' is fine to see, but a shortened bunch of X's just looks weird. — Robotnik
– Robotnik, Commented Jul 1, 2016 at 1:43
Actually it would need to be an SP and not a view... all that work kinda defeats the purpose though. — Eric
– Eric, Commented Jul 1, 2016 at 1:53
@Eric Yeah. As I mentioned this is a test of SS2016's data masking capabilities, we may as well just go down the separate DB + obfuscation approach or just live with the weird 'X's if we're gonna be writing custom stored procs. — Robotnik
– Robotnik, Commented Jul 1, 2016 at 2:05

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

The MSDN article on this feature leaves a lot to be desired. But I did find another similar article pertaining to Azure that seems to answer your question.

Custom text:

Masking method which exposes the first and last characters and adds a custom padding string in the middle. If the original string is shorter than the exposed prefix and suffix, only the padding string will be used. prefix[padding]suffix

https://azure.microsoft.com/en-us/documentation/articles/sql-database-dynamic-data-masking-get-started/

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 1, 2016 at 2:07

Eric

7035 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robotnik Over a year ago

I've come to the same conclusion today :/. Oh well!

Collectives™ on Stack Overflow

How to ignore empty strings with SQL Server 2016 Dynamic Data Masking?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related