Generating XML from SQL with Dynamic Data Masking

Question

I'm trying to generate XML from a database and using the Dynamic Data Masking for hiding sensitive information but when I execute my stored procedures that generate the XML as the user who should only see the masked data, the finished XML lacks the output from subqueries and only generates <masked /> instead of the usual tag and, in this case, masked contents of that tag. When I run the stored procedures as my regular db user I get the XML-results I need.

I've tried changing the rights for my "masked db user" but I can't manage to resolve the above issue.

This is the content of my stored procedure:
WITH XMLNAMESPACES (DEFAULT 'urn:svsys:export:user') SELECT u.userId ,u.identityNumber ,u.firstName ,u.lastName ,(SELECT ur.role FROM dbo.UserRole ur WHERE ur.userId = u.id FOR XML PATH (''), ROOT ('userRoles'), TYPE, ELEMENTS) FROM dbo.[User] u INNER JOIN dbo.LegalCareUnit lcu ON u.legalCareUnitId = lcu.id WHERE lcu.legalCareUnitId = @LegalCareUnitId FOR XML PATH ('user'), ROOT ('users')

and an example of output when executed by my regular db user, ie. without Dynamic Data Masking:

<users xmlns="urn:svsys:export:user"> <user> <userId>2</userId> <identityNumber>111</identityNumber> <firstName>Paddy</firstName> <lastName>Smith</lastName> <userRoles xmlns="urn:svsys:export:user"> <role>testRole</role> </userRoles> </user> </users>

and how the XML looks when executing as my masked db user (with Dynamic Data Masking):

<users xmlns="urn:svsys:export:user"> <user> <userId>2</userId> <identityNumber>xxx</identityNumber> <firstName>Paxxx</firstName> <lastName>Smxxx</lastName> <masked xmlns="" /> </user> </users>

what I would like to get when executing as my masked db user (with Dynamic Data Masking):
<users xmlns="urn:svsys:export:user"> <user> <userId>2</userId> <identityNumber>xxx</identityNumber> <firstName>Paxxx</firstName> <lastName>Smxxx</lastName> <userRoles xmlns="urn:svsys:export:user"> <role>texxxxxx</role> </userRoles> </user> </users>
As you can see the tag in my first example <userRoles xmlns="urn:svsys:export:user"> is replaced by <masked xmlns="" /> in the second example.

Any idea how I can get the tag <userRoles> with masked information inside the child tag <role>, as in my last example?

Answer 1

Sorry for the delay with answering. There are some on-going problems with the notifications in the forums right now, so I did not get a mail about your last comment, and then I saw it too late last night.

Anyway, it seems that you can get it to work if you bounce data over a temp table:

CREATE OR ALTER PROCEDURE [dbo].TestGetUserRecordXml  AS
      SELECT * INTO #testis FROM TestUserRole
      ;WITH XMLNAMESPACES (DEFAULT 'urn:svsys:export:user')
      SELECT
      u.userId
         ,u.identityNumber
         ,u.firstName
         ,u.lastName
         ,(SELECT
      ur.userRole
      FROM #testis ur
      WHERE ur.userId = u.id
      FOR XML PATH (''), ROOT ('userRoles'), TYPE, ELEMENTS)
      FROM dbo.TestUser u
      FOR XML PATH ('user'), ROOT ('users')
`´`

Answer 2

Hi @Anders M ,

Only default dynamic data masking function can be used to mask the data with XML type. The masked value will show on only one tag <masked />.

Please check your steps with below blogs to find that if you missed some steps.

Step 1. Create a Security Rule Set to Specify the Procedure Call and Process the Result Set
Step 2. Create a Rule Set or Rule Sets to Process the Result Set
Step 3. Create the XML Masking Rule Set

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

Answer 3

Hi @CathyJi-MSFT !

Thank you for your reply. Trying to understand how your links should help me but can't really understand as I've set up my masking in my create table-scripts. I'm including a test setup so that anyone can have the same setup as I have if they want to try and help.

CREATE TABLE dbo.[TestUser]  
(  
 id INT PRIMARY KEY IDENTITY(1,1),  
 userId INT NOT NULL,  
 identityNumber NVARCHAR(50) MASKED WITH (FUNCTION = 'partial(4, "0101", 0)')  NOT NULL,  
 firstName NVARCHAR(50) MASKED WITH (FUNCTION = 'partial(2, "xxxxxxxxx", 0)') NOT NULL,  
 lastName NVARCHAR(50) MASKED WITH (FUNCTION = 'partial(2, "xxxxxxxxx", 0)') NOT NULL  
)  
  
  
CREATE TABLE dbo.TestUserRole  
(  
 id INT PRIMARY KEY IDENTITY(1,1),  
 userId INT NOT NULL,  
 userRole NVARCHAR(50) MASKED WITH (FUNCTION = 'partial(2, "xxxxxxxxx", 0)') NOT NULL  
)  
  
ALTER TABLE [dbo].[TestUserRole]  WITH CHECK ADD CONSTRAINT [FK_TestUser_UserRole] FOREIGN KEY([userId])  
REFERENCES [dbo].[User] ([id])  
  
SET IDENTITY_INSERT TestUser ON  
  
INSERT INTO TestUser (id, userId, identityNumber, firstName, lastName)  
 VALUES (1, 200, N'19520102', N'Paddy', N'Smith'),  
 (2, 300, N'19500609', N'Trevor', N'Bolder'),  
 (3, 400, N'19460526', N'Mick', N'Ronson')  
  
SET IDENTITY_INSERT TestUser OFF  
  
INSERT INTO TestUserRole (userId, userRole)  
 VALUES (1, N'Roadie'),  
 (2, N'Bass player'),  
 (3, N'Guitarist'),  
 (3, N'Pianist')  
GO  
  
CREATE PROCEDURE [dbo].TestGetUserRecordXml  
AS  
 WITH XMLNAMESPACES (DEFAULT 'urn:svsys:export:user')  
 SELECT  
 u.userId  
    ,u.identityNumber  
    ,u.firstName  
    ,u.lastName  
    ,(SELECT  
 ur.userRole  
 FROM dbo.TestUserRole ur  
 WHERE ur.userId = u.id  
 FOR XML PATH (''), ROOT ('userRoles'), TYPE, ELEMENTS)  
 FROM dbo.TestUser u  
 FOR XML PATH ('user'), ROOT ('users')  
GO  
  
CREATE USER [UserForMaskedData] WITHOUT LOGIN;  
GRANT EXECUTE ON SCHEMA::dbo TO UserForMaskedData;  
GRANT SELECT ON SCHEMA::dbo TO UserForMaskedData;