question

AndersM-3098 avatar image
0 Votes"
AndersM-3098 asked AndersM-3098 commented

Generating XML from SQL with Dynamic Data Masking

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?



sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AndersM-3098,

Any update for this thread? Did the response could help you? If the response helped, do "Accept Answer". If it is not work, please let us know the progress.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered AndersM-3098 commented

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')
´

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ErlandSommarskog, that does it. Thanks very much for your help!

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @AndersM-3098,

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AndersM-3098 avatar image
0 Votes"
AndersM-3098 answered AndersM-3098 commented

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;






· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Any chance you can reformat the above, so it is legible?

0 Votes 0 ·

@ErlandSommarskog today the formatting worked, so now it's hopefully easier to read. Thanks!

0 Votes 0 ·