locked
Dynamic Data Masking for two users on same table column with different Functions. RRS feed

  • Question

  • Hello Team,

    I am implementing Dynamic Data Masking, but i am facing some issue ,below is the details.

    CREATE DATABASE [Masking]
    GO
    USE [Masking]
    GO
    CREATE TABLE SensitiveData
    (
    	ID INT,
    	Name VARCHAR(50),
    	EMAIL VARCHAR(50),
    	PHONE VARCHAR(10),
    	Gender CHAR(1),
    	AGE INT
    )
    GO
    INSERT INTO SensitiveData SELECT 1,'Chetan','mailforchetan@gmail.com','9821808988','M',30
    INSERT INTO SensitiveData SELECT 1,'Aniket','mailforaniket@gmail.com','9821808989','M',29
    INSERT INTO SensitiveData SELECT 1,'Yatish','mailforyatish@gmail.com','9821808990','M',28
    GO
    SELECT * FROM SensitiveData
    GO
    CREATE USER User1 
    WITHOUT LOGIN
    GO
    GRANT SELECT ON dbo.SensitiveData TO [User1]
    GO
    GRANT INSERT ON dbo.SensitiveData TO [User1]
    GO
    
    --Column is not yet masked
    EXECUTE AS USER = 'User1'
    SELECT * FROM SensitiveData
    REVERT
    
    --Masking Name Columns with Default option
    ALTER TABLE SensitiveData
    ALTER COLUMN Name ADD MASKED WITH (FUNCTION = 'Default()')
    GO
    --- Now name has been masked with defalut function
    EXECUTE AS USER = 'User1'
    SELECT * FROM SensitiveData
    REVERT
    
    

    The above code is working properly as expected , now i have another users and i wanted to mask same column of same table with random() function. could you please tell me how can i achieve this for 2nd user?

    Br

    ChetanV

    Wednesday, December 5, 2018 5:17 PM

Answers

  • You can only assign one masking function to a column. The only way, I think, is to alter the mask on existing column but that could get messy real fast.
    Wednesday, December 5, 2018 6:28 PM
  • Hi ChetanV,

     

    According to your description, I know that you want to have another user to see the same column value of same table returned from random() function.

     

    If the another user have the ALTER ANY MASK permission and ALTER permission on the table, as a workaround, you can judge the current user and then edit the mask on the column before query.

     

    EXECUTE AS USER = 'User1'
    if (SELECT CURRENT_USER) = 'User1'
    ALTER TABLE SensitiveData
    ALTER COLUMN [AGE] ADD MASKED WITH (FUNCTION = 'random(1, 12)')
    
    SELECT * FROM SensitiveData
    REVERT

     

    If the another user doesn't have the ALTER permission, as mentioned by Bremell, a column can be assigned to only one masking function.

     

    Best Regards,

    Emily


    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

    Thursday, December 6, 2018 6:43 AM

All replies

  • You can only assign one masking function to a column. The only way, I think, is to alter the mask on existing column but that could get messy real fast.
    Wednesday, December 5, 2018 6:28 PM
  • Hi ChetanV,

     

    According to your description, I know that you want to have another user to see the same column value of same table returned from random() function.

     

    If the another user have the ALTER ANY MASK permission and ALTER permission on the table, as a workaround, you can judge the current user and then edit the mask on the column before query.

     

    EXECUTE AS USER = 'User1'
    if (SELECT CURRENT_USER) = 'User1'
    ALTER TABLE SensitiveData
    ALTER COLUMN [AGE] ADD MASKED WITH (FUNCTION = 'random(1, 12)')
    
    SELECT * FROM SensitiveData
    REVERT

     

    If the another user doesn't have the ALTER permission, as mentioned by Bremell, a column can be assigned to only one masking function.

     

    Best Regards,

    Emily


    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

    Thursday, December 6, 2018 6:43 AM