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
Users with administrator privileges are always excluded from masking, and see the original data without any mask.
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.
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.
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
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With