{"id":92797,"date":"2021-11-22T17:00:34","date_gmt":"2021-11-22T17:00:34","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=92797"},"modified":"2021-11-15T18:10:40","modified_gmt":"2021-11-15T18:10:40","slug":"dynamic-data-mask-is-now-useful-and-no-one-noticed-it","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/dynamic-data-mask-is-now-useful-and-no-one-noticed-it\/","title":{"rendered":"Dynamic Data Mask is now useful and no one noticed it"},"content":{"rendered":"

Dynamic data mask is a very interesting security feature allowing us to mask critical fields such as e-mail, phone number, credit card and so on. We can decide what users will be able to see the value of these features or not.<\/p>\n

This feature faced many flaws when it was released, but I believe it’s stable now, although It’s not the main security feature you should care about, it can still be very useful.<\/p>\n

However, until very recently, this feature was not very useful. If you mask many fields in many different tables, the fields may require different permission levels in order to be unmasked.<\/p>\n

The only permission control provided for Mask and Unmask fields where this: See everything masked or everything unmasked. In this way, the feature was not useful, because there is not doubt that phone number and credit card should have different permission levels.<\/p>\n

Finally, the granular permission control for Data Mask is available in Azure SQL Databases. The feature became way more useful and with so many news around these weeks, not many people noticed.<\/p>\n

Test Environment<\/h2>\n

Let’s test this feature using the AdventureWorksLT<\/strong>. We can provision this database in Azure SQL<\/strong> by choosing to provision a sample database.<\/p>\n

\nApplying the Data Mask<\/h2>\n
alter<\/span>\u00a0table<\/span>\u00a0SalesLT<\/span>.<\/span>Customer<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0alter<\/span>\u00a0column<\/span>\u00a0EmailAddress<\/span>\u00a0nvarchar<\/i><\/span>(<\/span>50<\/span>)<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0masked<\/span>\u00a0with<\/span>\u00a0(<\/span>function<\/span>=<\/span>’email()’<\/span>)<\/span><\/span><\/div>\n
go<\/span><\/span><\/div>\n
alter<\/span>\u00a0table<\/span>\u00a0SalesLT<\/span>.<\/span>Customer<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0alter<\/span>\u00a0column<\/span>\u00a0Phone<\/span>\u00a0nvarchar<\/i><\/span>(<\/span>25<\/span>)<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0masked<\/span>\u00a0with<\/span>(<\/span>function<\/span>=<\/span>‘partial(3,”XXXXXXXXX”,0)’<\/span>)<\/span><\/span><\/div>\n
go<\/span> <\/span><\/div>\n

\nCreate Roles to control Mask Permissions<\/h3>\n

\nControl permissions in a field level is something complex. A good practice to do this is using database roles. Let’s create database roles for this and set the permission of these roles as data reader to make the example easier.<\/p>\n

Create<\/span>\u00a0Role<\/span>\u00a0EmailView<\/span><\/span><\/div>\n
go<\/span><\/span><\/div>\n
Create<\/span>\u00a0Role<\/span>\u00a0PhoneView<\/span><\/span><\/div>\n
go<\/span><\/span><\/div>\n
Alter<\/span>\u00a0Role<\/span>\u00a0db_datareader<\/span>\u00a0add<\/span>\u00a0member<\/span>\u00a0EmailView<\/span><\/span><\/div>\n
go<\/span><\/span><\/div>\n
Alter<\/span>\u00a0Role<\/span>\u00a0db_datareader<\/span>\u00a0add<\/span>\u00a0member<\/span>\u00a0PhoneView<\/span><\/span><\/div>\n
go<\/span> <\/span><\/div>\n

\nGrant the unmask peremission<\/h3>\n

On our example, we will grant the unmask permission over each field for the different database roles. The statements will be like this:<\/p>\n

Grant<\/span>\u00a0UnMask<\/span>\u00a0on<\/span>\u00a0SalesLT<\/span>.<\/span>Customer<\/span>(<\/span>Phone<\/span>)<\/span>\u00a0to<\/span>\u00a0PhoneView<\/span>
\ngo<\/span>
\nGrant<\/span>\u00a0UnMask<\/span>\u00a0on<\/span>\u00a0SalesLT<\/span>.<\/span>Customer<\/span>(<\/span>EmailAddress<\/span>)<\/span>\u00a0to<\/span>\u00a0Emailview<\/span>
\ngo<\/span> <\/span><\/div>\n

\nThe granular unmask permission also would allow to grant permission schema level:<\/p>\n

Grant<\/span>\u00a0UnMask<\/span>\u00a0on<\/span>\u00a0Schema<\/span>::<\/span>SalesLT<\/span>\u00a0to<\/span>\u00a0CustomRole<\/span> <\/span><\/div>\n

Or also on table level:<\/p>\n

Grant<\/span>\u00a0UnMask<\/span>\u00a0on<\/span>\u00a0SalesLT<\/span>.<\/span>Customer<\/span>\u00a0to<\/span>\u00a0CustomRole<\/span> <\/span><\/div>\n

\nAnyway, now making the data mask feature really useful.<\/p>\n

Create<\/span>\u00a0user<\/span>\u00a0CanReadEmail<\/span>\u00a0with<\/span>\u00a0password<\/span>=<\/span>‘9646xpahmW’<\/span><\/span><\/div>\n
go<\/span><\/span><\/div>\n
\u00a0<\/div>\n
Create<\/span>\u00a0user<\/span>\u00a0CanReadPhone<\/span>\u00a0with<\/span>\u00a0password<\/span>=<\/span>‘9646xpahmW’<\/span><\/span><\/div>\n
go<\/span><\/span><\/div>\n
alter<\/span>\u00a0role<\/span>\u00a0EmailView<\/span>\u00a0add<\/span>\u00a0member<\/span>\u00a0CanReadEmail<\/span><\/span><\/div>\n
go<\/span><\/span><\/div>\n
alter<\/span>\u00a0role<\/span>\u00a0PhoneView<\/span>\u00a0add<\/span>\u00a0member<\/span>\u00a0CanReadPhone<\/span><\/span><\/div>\n
go<\/span> <\/span><\/div>\n
\u00a0<\/div>\n

Test the users and UnMask Feature<\/h3>\n
Execute<\/span>\u00a0as<\/span>\u00a0user<\/span>=<\/span>‘CanReadEmail’<\/span><\/span><\/div>\n
\u00a0<\/div>\n
select<\/span>\u00a0*<\/span>\u00a0from<\/span>\u00a0SalesLT<\/span>.<\/span>Customer<\/span><\/span><\/div>\n
\u00a0<\/div>\n
revert<\/span> <\/span><\/div>\n

This first user can read the phone, but not the e-mail:<\/p>\n

\"\"<\/p>\n

 <\/p>\n

Execute<\/span>\u00a0as<\/span>\u00a0user<\/span>=<\/span>‘CanReadPhone’<\/span><\/span><\/div>\n
\u00a0<\/div>\n
select<\/span>\u00a0*<\/span>\u00a0from<\/span>\u00a0SalesLT<\/span>.<\/span>Customer<\/span><\/span><\/div>\n
\u00a0<\/div>\n
revert<\/span> <\/span><\/div>\n

This 2nd user can read the e-mail but not the phone:<\/p>\n

\"\"<\/p>\n

 <\/p>\n

Conclusion<\/h2>\n

We have a new powerful security feature on our hands to work with and I hope this feature to be in SQL Server 2022 as well<\/p>\n","protected":false},"excerpt":{"rendered":"

Dynamic data mask is a very interesting security feature allowing us to mask critical fields such as e-mail, phone number, credit card and so on. We can decide what users will be able to see the value of these features or not. This feature faced many flaws when it was released, but I believe it’s……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,1],"tags":[5364,136322,145492,4619],"coauthors":[6810],"class_list":["post-92797","post","type-post","status-publish","format-standard","hentry","category-blogs","category-uncategorized","tag-azure","tag-azure-sql","tag-data-mask","tag-security"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92797","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=92797"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92797\/revisions"}],"predecessor-version":[{"id":92800,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92797\/revisions\/92800"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=92797"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=92797"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=92797"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=92797"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}