How do you dynamically mask data that saved as json via Azure?

user_6198131 0
Viewed 37

I'm trying to mask sensitive data via an Azure SQL database. The data is saved as normal text and one column as XML and another saved as json. I've tried adding rules to the database but when I open SSMS and run a select statement it does not apply to any of the data in the columns (normal text, xml or json saved data)

There's no user excluded to see unmasked data. Just want to understand why the data is not masked when I perform a select on SSMS.

My rules look like the below:

XML Rule

XML Rule

JSON Rule:

Json Rule

Text Rule:

Text rule

My SQL statment:

SELECT TOP (1000) * from database_Name

data-masking masking azure dynamic sql
1 Answers

As mentioned in Microsoft Document it says,

The identities in Azure Active Directory (Azure AD) or SQL are included in the masking process and should have access to the unmasked sensitive data.

Maybe you are accessing data as SQL admin or Azure AD user because of that you can see sensitive data.

By hiding important information from unwanted users at multiple layers of the database, you may prevent access and gain control. You may give or remove UNMASK permission to a user.

The code taken from Microsoft-documentation it says,

Give UNMASK permission to user


GRANT UNMASK ON Data.Membership TO USER;

To Query the data under the context of user


EXECUTE AS USER='USER';

To revoke UNMASK permissions


REVOKE UNMASK ON Data.Membership FROM USER;

Data after granting permission to user

enter image description here

Data after removing permission from user

enter image description here

Taken Reference from:

SQL Database dynamic data masking with the Azure portal

Granting and Revoking the Permission

user_176238020
Related