locked
DDM behaviour with UNION RRS feed

  • Question

  • Hi experts,

      I am looking at the new Dynamic Data Masking in 2016 and have come across some unexpected behaviour.

      In some of my data I want to mask only a small number of rows in a very large table. My thinking is that I can split the data into two tables, one without a mask and the other with a mask. I can then put a view across the top and union the two tables together.

      I tried this with some test data. When I select from each individual table the data comes back as I expect (masked or unmasked), but when I union the data together ALL data is masked. Is this what should be happening?

    Ian

    Example  


    SELECT @@VERSION ;

    --  create a table with no masked column
    CREATE TABLE securitytest_defaultaccess (
      RecordID  INT,
      Name      VARCHAR(20) NULL,
      Details1  VARCHAR(30) NULL,
      Details2  VARCHAR(30) NULL
     );

    -- populate with some example data
    INSERT INTO securitytest_defaultaccess( RecordID, Name, Details1, Details2) values
    (1,'Record 1','Test line 1','Test line 1'),
    (2,'Record 2','Test line 2','Test line 2'),
    (3,'Record 3','Test line 3','Test line 3');

    -- create a table with Details1 column masked
    CREATE TABLE securitytest_restrictedaccess (
      RecordID  INT,
      Name      VARCHAR(20) NULL,
      Details1  VARCHAR(30) MASKED WITH (FUNCTION = 'default()') NULL,
      Details2  VARCHAR(30)
     );

    -- populate with some different data
    INSERT INTO securitytest_restrictedaccess( RecordID, Name, Details1, Details2) values
    (4,'Record 4','Test line 4','Test line 4'),
    (5,'Record 5','Test line 5','Test line 5'),
    (6,'Record 6','Test line 6','Test line 6');
    GO

    -- create a view that joins the two tables together
    CREATE VIEW securitytest_view AS
      SELECT * from securitytest_defaultaccess
      UNION ALL
      SELECT * FROM securitytest_restrictedaccess;
    GO

    -- create a user  with limited capabilities
    CREATE USER TestUser WITHOUT LOGIN;
    GRANT SELECT ON securitytest_defaultaccess TO TestUser;
    GRANT SELECT ON securitytest_restrictedaccess TO TestUser;
    GRANT SELECT ON securitytest_view TO TestUser;

    GO

    EXECUTE AS USER = 'TestUser';
    SELECT * FROM securitytest_defaultaccess;
    SELECT * FROM securitytest_restrictedaccess;
    SELECT * FROM securitytest_view;
    REVERT;
    GO

    Results

    Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016 23:23:58   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )

    RecordID Name Details1 Details2
    1 Record 1 Test line 1 Test line 1
    2 Record 2 Test line 2 Test line 2
    3 Record 3 Test line 3 Test line 3

    RecordID Name Details1 Details2
    4 Record 4 xxxx Test line 4
    5 Record 5 xxxx Test line 5
    6 Record 6 xxxx Test line 6

    RecordID Name Details1 Details2
    1 Record 1 xxxx Test line 1
    2 Record 2 xxxx Test line 2
    3 Record 3 xxxx Test line 3
    4 Record 4 xxxx Test line 4
    5 Record 5 xxxx Test line 5
    6 Record 6 xxxx Test line 6

    Tuesday, January 16, 2018 8:54 AM

Answers

  • I think this is due to that masking is a property of a column, not a single cell. You can see the same thing with coalesce (or any other form of CASE) or isnull. Basically, when two types meet, the type with lower predence is converted to the other. I don't know whether that is the exact mechanism used here, but it carries the general idea.

    To achieve what you are looking for, you would need to use a multi-statement function where you insert data from the two tables separately. It goes without saying that this would not be very efficient.

    Below a script that demonstrates what I'm talking about above.

    CREATE TABLE securitytest_defaultaccess (
      RecordID  INT,
      Name      VARCHAR(20) NULL,
      Details1  VARCHAR(30) NULL,
      Details2  VARCHAR(30) NULL
     );

    -- populate with some example data
    INSERT INTO securitytest_defaultaccess( RecordID, Name, Details1, Details2) values
    (1,'Record 1','Test line 1','Test line 1'),
    (2,'Record 2',NULL,'Test line 2'),
    (3,'Record 3','Test line 3','Test line 3'),
    (4,'Record 4','Test line 4','Test line 4'),
    (5,'Record 5', NULL,'Test line 5'),
    (6,'Record 6','Test line 6','Test line 6');

    -- create a table with Details1 column masked
    CREATE TABLE securitytest_restrictedaccess (
      RecordID  INT,
      Name      VARCHAR(20) NULL,
      Details1  VARCHAR(30) MASKED WITH (FUNCTION = 'default()') NULL,
      Details2  VARCHAR(30)  );

    -- populate with some different data
    INSERT INTO securitytest_restrictedaccess( RecordID, Name, Details1, Details2) values
    (1,'Record 1', NULL,'Test line 1'),
    (2,'Record 2',  'Hidden test line 2', 'Test line 2'),
    (3,'Record 3', 'Hidden Test line 3','Test line 3'),
    (4,'Record 4', 'Hidden test line 4','Test line 4'),
    (5,'Record 5', 'Hidden test line 5','Test line 5'),
    (6,'Record 6', NULL,'Test line 6');
    GO

    -- create a view that joins the two tables together
    CREATE VIEW securitytest_view AS
      SELECT * from securitytest_defaultaccess
      UNION ALL
      SELECT * FROM securitytest_restrictedaccess;
    GO

    CREATE FUNCTION securitytest_fun () RETURNS @t TABLE ( RecordID  INT,
                       Name      VARCHAR(20) NULL,
                       Details1  VARCHAR(30) NULL,
                       Details2  VARCHAR(30) NULL)
    BEGIN    INSERT @t SELECT * FROM securitytest_defaultaccess
       INSERT @t SELECT * FROM securitytest_restrictedaccess
       RETURN
    END
    go
    -- create a user  with limited capabilities
    CREATE USER TestUser WITHOUT LOGIN;
    GRANT SELECT ON securitytest_defaultaccess TO TestUser;
    GRANT SELECT ON securitytest_restrictedaccess TO TestUser;
    GRANT SELECT ON securitytest_view TO TestUser;
    GRANT SELECT ON securitytest_fun TO TestUser
    GO

    EXECUTE AS USER = 'TestUser';
    SELECT * FROM securitytest_defaultaccess
    SELECT * FROM securitytest_restrictedaccess;
    SELECT * FROM securitytest_view;
    SELECT * FROM securitytest_fun()
    SELECT a.RecordID, a.Name, coalesce(a.Details1, b.Details1), isnull(a.Details1, b.Details1),
           coalesce(b.Details1, a.Details1), isnull(b.Details1, a.Details1)
    FROM   securitytest_defaultaccess a
    JOIN   securitytest_restrictedaccess b ON a.RecordID = b.RecordID

    go
    REVERT;
    GO

    go
    DROP VIEW securitytest_view
    DROP TABLE securitytest_defaultaccess, securitytest_restrictedaccess
    DROP USER TestUser

    • Marked as answer by PointyClicky Monday, January 29, 2018 7:45 AM
    Tuesday, January 16, 2018 10:13 AM
  • My current thinking is that it is just not possible to do what I want with the built in Dynamic Data Masking.

    I have written a bespoke masking solution which essentially uses a function which checks the current users permitted access levels against  each row access level. Users will be given access to the data via a view that calls this function rather than direct to the table.

    • Marked as answer by PointyClicky Monday, January 29, 2018 7:45 AM
    Friday, January 26, 2018 11:55 AM

All replies

  • I think this is due to that masking is a property of a column, not a single cell. You can see the same thing with coalesce (or any other form of CASE) or isnull. Basically, when two types meet, the type with lower predence is converted to the other. I don't know whether that is the exact mechanism used here, but it carries the general idea.

    To achieve what you are looking for, you would need to use a multi-statement function where you insert data from the two tables separately. It goes without saying that this would not be very efficient.

    Below a script that demonstrates what I'm talking about above.

    CREATE TABLE securitytest_defaultaccess (
      RecordID  INT,
      Name      VARCHAR(20) NULL,
      Details1  VARCHAR(30) NULL,
      Details2  VARCHAR(30) NULL
     );

    -- populate with some example data
    INSERT INTO securitytest_defaultaccess( RecordID, Name, Details1, Details2) values
    (1,'Record 1','Test line 1','Test line 1'),
    (2,'Record 2',NULL,'Test line 2'),
    (3,'Record 3','Test line 3','Test line 3'),
    (4,'Record 4','Test line 4','Test line 4'),
    (5,'Record 5', NULL,'Test line 5'),
    (6,'Record 6','Test line 6','Test line 6');

    -- create a table with Details1 column masked
    CREATE TABLE securitytest_restrictedaccess (
      RecordID  INT,
      Name      VARCHAR(20) NULL,
      Details1  VARCHAR(30) MASKED WITH (FUNCTION = 'default()') NULL,
      Details2  VARCHAR(30)  );

    -- populate with some different data
    INSERT INTO securitytest_restrictedaccess( RecordID, Name, Details1, Details2) values
    (1,'Record 1', NULL,'Test line 1'),
    (2,'Record 2',  'Hidden test line 2', 'Test line 2'),
    (3,'Record 3', 'Hidden Test line 3','Test line 3'),
    (4,'Record 4', 'Hidden test line 4','Test line 4'),
    (5,'Record 5', 'Hidden test line 5','Test line 5'),
    (6,'Record 6', NULL,'Test line 6');
    GO

    -- create a view that joins the two tables together
    CREATE VIEW securitytest_view AS
      SELECT * from securitytest_defaultaccess
      UNION ALL
      SELECT * FROM securitytest_restrictedaccess;
    GO

    CREATE FUNCTION securitytest_fun () RETURNS @t TABLE ( RecordID  INT,
                       Name      VARCHAR(20) NULL,
                       Details1  VARCHAR(30) NULL,
                       Details2  VARCHAR(30) NULL)
    BEGIN    INSERT @t SELECT * FROM securitytest_defaultaccess
       INSERT @t SELECT * FROM securitytest_restrictedaccess
       RETURN
    END
    go
    -- create a user  with limited capabilities
    CREATE USER TestUser WITHOUT LOGIN;
    GRANT SELECT ON securitytest_defaultaccess TO TestUser;
    GRANT SELECT ON securitytest_restrictedaccess TO TestUser;
    GRANT SELECT ON securitytest_view TO TestUser;
    GRANT SELECT ON securitytest_fun TO TestUser
    GO

    EXECUTE AS USER = 'TestUser';
    SELECT * FROM securitytest_defaultaccess
    SELECT * FROM securitytest_restrictedaccess;
    SELECT * FROM securitytest_view;
    SELECT * FROM securitytest_fun()
    SELECT a.RecordID, a.Name, coalesce(a.Details1, b.Details1), isnull(a.Details1, b.Details1),
           coalesce(b.Details1, a.Details1), isnull(b.Details1, a.Details1)
    FROM   securitytest_defaultaccess a
    JOIN   securitytest_restrictedaccess b ON a.RecordID = b.RecordID

    go
    REVERT;
    GO

    go
    DROP VIEW securitytest_view
    DROP TABLE securitytest_defaultaccess, securitytest_restrictedaccess
    DROP USER TestUser

    • Marked as answer by PointyClicky Monday, January 29, 2018 7:45 AM
    Tuesday, January 16, 2018 10:13 AM
  • Erland,

    Thankyou for your suggestion. I have been looking into it a bit further for last few days. I am thinking that the performance impact might not be too bad since most of my data is default access, so I might do something along the lines of

    CREATE FUNCTION securitytest_fun2() RETURNS @t TABLE (
      RecordID  INT,
      Name      VARCHAR(20) NULL,
      Details1  VARCHAR(30) NULL,
      Details2  VARCHAR(30) NULL
     )
    BEGIN

    -- Do not include the larger table

    --  INSERT @T SELECT * FROM securitytest_defaultaccess
      INSERT @T SELECT * FROM securitytest_restrictedaccess
      RETURN
    END
    GO

    -- create a view that joins the larger table with the smaller function 
    CREATE VIEW securitytest_view2 AS
      SELECT * from securitytest_defaultaccess
      UNION ALL
      SELECT * FROM securitytest_fun2();
    GO

    Not an expert on performance but I suspect this might be quicker.

    The main problem I have with this as a solution though is that the view/function is part of a reporting solution and select statements on the obfuscated columns return different data using this method. Example


    -- Direct search on masked data finds this row
    EXECUTE AS USER='TestUser';
    select * from securitytest_restrictedaccess where Details1='Test Line 4';

    4 Record 4 xxxx Test line 4

    -- Identical search criteria using the function finds no rows
    EXECUTE AS USER='TestUser';
    select * from securitytest_fun2() where Details1='Test Line 4';


    -- Search using the function for the obfuscated value returns rows 4 to 6
    EXECUTE AS USER='TestUser';
    select * from securitytest_fun2() where Details1='xxxx';

    4 Record 4 xxxx Test line 4
    5 Record 5 xxxx Test line 5
    6 Record 6 xxxx Test line 6

    So in terms of searchability the function behaves differently to the query on the masked table.

    I have found that I can get all of the data with a full join

    EXECUTE AS USER = 'TestUser';
    SELECT * FROM securitytest_defaultaccess d
     FULL OUTER JOIN securitytest_restrictedaccess r
     ON d.RecordID=r.RecordID;

    1 Record 1 Test line 1 Test line 1 NULL NULL NULL NULL
    2 Record 2 Test line 2 Test line 2 NULL NULL NULL NULL
    3 Record 3 Test line 3 Test line 3 NULL NULL NULL NULL
    NULL NULL NULL NULL 4 Record 4 xxxx Test line 4
    NULL NULL NULL NULL 5 Record 5 xxxx Test line 5
    NULL NULL NULL NULL 6 Record 6 xxxx Test line 6

    but my attempts to date to return this as the simplified set of data are still scuppered by the masking override behaviour

     SELECT isnull(d.Details1,r.Details1), isnull(d.Details2,r.Details2)
     FROM securitytest_defaultaccess d
     FULL OUTER JOIN securitytest_restrictedaccess r
     ON d.RecordID=r.RecordID;

    xxxx Test line 1
    xxxx Test line 2
    xxxx Test line 3
    xxxx Test line 4
    xxxx Test line 5
    xxxx Test line 6

    Still puzzling with this one!

    Monday, January 22, 2018 11:12 AM
  • My current thinking is that it is just not possible to do what I want with the built in Dynamic Data Masking.

    I have written a bespoke masking solution which essentially uses a function which checks the current users permitted access levels against  each row access level. Users will be given access to the data via a view that calls this function rather than direct to the table.

    • Marked as answer by PointyClicky Monday, January 29, 2018 7:45 AM
    Friday, January 26, 2018 11:55 AM
  • Hi PointyClicky,

    Based on my researching, I met a similar problem, as mentioned by Erland, due to that masking is a property of a column.

    Could you please tell us if you have solved this problem? If it is, could you please mark your own and Erland's reply as Answered?

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Sunday, January 28, 2018 12:29 PM
  • I have nothing further to add to Erlands reply except that I would think that this is even by design. As I remember from from discussions in very early stage DDM was not even supported in views. So this was purposely changed.

    What I would really like to point out is the great repro that PointClicky prepared for this Forum question. If all those seeking for answers would prepare such... cheers for that!

    Andreas


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Tuesday, January 30, 2018 12:48 AM