How will already existing users be affected by adding a masking rule- Dynamic Data Masking

Layan 0 Reputation points
2023-01-18T09:17:48.91+00:00

Hello all,

If I add a masking rule on an existing table using ALTER, would all the users that already had access to the

sensitive data lose their access and would require the UNMASK permission?

thank you.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,466 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Cristina Avallone 0 Reputation points
    2023-01-18T16:50:10.9133333+00:00

    The default behavior is masking data . Users will not lose their access (if you intend the capability of doing SELECT data from a table) but they will see masked data.

    Users will see data if you grant UNMASK permission explicitly.

    0 comments No comments

  2. Seeya Xi-MSFT 16,571 Reputation points
    2023-01-19T02:45:25.4233333+00:00

    Hi Layan,

    Please refer to this blog: Dynamic Data Masking in SQL Server

    I used the data from it to do a test for you.

    --create table
    CREATE TABLE EmployeeData
    (MemberID INT IDENTITY PRIMARY KEY,
    FirstName varchar(100),
    LastName varchar(100),
    Email varchar(100),
    Age int,
    JoinDate date,
    LeaveDays int
    )
    
    --insert data
    INSERT INTO EmployeeData
    (FirstName, LastName, Email,Age,JoinDate,LeaveDays)
    VALUES
    ('Dinesh','Asanka','******@gmail.com',35,'2020-01-01',12),
    ('Saman','Perera','******@somewhere.lk',45,'2020-01-01',1),
    ('Julian','Soman','******@uniersity.edu.org',37,'2019-11-01',1),
    ('Telishia','Mathewsa','******@rose.lk',51,'2018-01-01',6)
    
    --create the user
    CREATE USER TestUser WITHOUT Login;
    GRANT SELECT ON EmployeeData TO TestUser
    
    SELECT * from EmployeeData
    
    

    User's image

    
    --add a masking rule
    Alter table EmployeeData ALTER column FirstName NVARCHAR(10) MASKED WITH (FUNCTION = 'default()')
    
    --select
    EXECUTE AS USER = 'TestUser';
    SELECT * FROM EmployeeData;
    REVERT;
    
    

    User's image

    As you can see from the test result, the purpose of dynamic data masking is to limit exposure of sensitive data, preventing users who shouldn't have access to the data from viewing it. Dynamic data masking doesn't aim to prevent database users from connecting directly to the database and running exhaustive queries that expose pieces of the sensitive data.

    --UNMASK
    GRANT UNMASK TO TestUser
    
    --select
    EXECUTE AS USER = 'TestUser';
    SELECT * FROM EmployeeData;
    REVERT;
    
    

    User's image

    Hope this test would give you some help.

    Best regards,

    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.