Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server Data Masking Minimum permissions needed to mask

I am in the process of masking some data in a SQL Server 2017 database. I have masked a column with this statement:

ALTER TABLE lEmployee
    ALTER COLUMN FirstName nvarchar(160) 
          MASKED WITH (FUNCTION = 'partial(2,"xxx",2)')

And I can see in sys.columns that this is now masked.

Now in the application, the data is not showing as masked (neither is it in the db when logged in as that user). I have also run the below to ensure the user does not have this permission.

REVOKE UNMASK TO testuser

Can anyone advise what the minimum permissions are needed so that the data masking works as expected.

Thank you in advance

like image 728
user3482471 Avatar asked Nov 17 '17 06:11

user3482471


People also ask

Which type of users can you exclude from masking in the data masking policy?

Users with administrator privileges are always excluded from masking, and see the original data without any mask.

How do you mask data in SQL Server?

The syntax is “Alter table [Table Name] ALTER [Column Name] ADD MASKED WITH (FUNCTION = 'partial (prefix, [padding], suffix)')”. Once the masking is applied, we can execute as the Testuser and see that the phone column is masked according to the given custom padding as 3 prefix and 0 in suffix.

Why data masking is required?

Data masking protects your information from accidental and intentional threats by ensuring that sensitive information is NOT available beyond the production environment. It is a way of creating a similar version of data that can be used for purposes such as software testing and user training.


1 Answers

You are not able to DENY UNMASK on sysadmin and db_owner. This is build-in limitation.

Also, as noted in the documentation, this is not a security feature if the user can query the table.

As it in the example of the documentation shows, you do not need to grant REVOKE UNMASK as it is set by default:

DROP TABLE IF EXISTS  [dbo].[StackOverflow];

CREATE TABLE [dbo].[StackOverflow]  
  (MemberID int IDENTITY PRIMARY KEY,  
   FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,  
   LastName varchar(100) NOT NULL,  
   Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,  
   Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);  

INSERT [dbo].[StackOverflow] (FirstName, LastName, Phone#, Email) VALUES   
('Roberto', 'Tamburello', '555.123.4567', '[email protected]'),  
('Janice', 'Galvin', '555.123.4568', '[email protected]'),  
('Zheng', 'Mu', '555.123.4569', '[email protected]');  
SELECT * FROM [dbo].[StackOverflow]  ;  



CREATE USER TestUser WITHOUT LOGIN;  
GRANT SELECT ON [dbo].[StackOverflow]   TO TestUser;  

EXECUTE AS USER = 'TestUser';  
SELECT * FROM [dbo].[StackOverflow]  ;  
REVERT; 

and you cannot REVOKE UNMASK to sysadmin and db_owner.

like image 134
gotqn Avatar answered Sep 24 '22 16:09

gotqn