Hi, here we know about how to show confidential data using Data Mask in SQL Server.
USE tempdb
GO
-- Create a sample table [dbo].[Customer] with masked columns:
CREATE TABLE dbo.Customer (
CustomerID INT IDENTITY PRIMARY KEY,
FirstName VARCHAR(250),
LastName VARCHAR(250) MASKED WITH (FUNCTION = 'default()') NULL,
PhoneNumber VARCHAR(12) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXXXX",0)') NULL,
Email VARCHAR(100) MASKED WITH (FUNCTION = 'email()') NULL,
CreditCardNo VARCHAR(16) MASKED WITH (FUNCTION = 'partial(4,"XXXXXXXXXX",2)') NULL,
);
-- Now insert some test records (fictitious figures):
INSERT INTO dbo.Customer (FirstName, LastName, PhoneNumber, Email, CreditCardNo) VALUES
('Arka', 'Gupta', '7001061958', 'arkagupto@gmail.com', '4563123456789012'),
('Rahul', 'Singh', '9842359873', 'rahul@gmail.com', '123456789092'),
('Visal', 'Sharma', '6700091129', 'visal@gmail.com', '567890123469'),
('Rajib', 'Agrawal', '9800002000', 'rajib@gmail.com', '345678901224');
-- 1. Now let’s create a Test Account and just Grant Read access to [dbo].[Customer] table:
CREATE USER user1 WITHOUT LOGIN;
GO
GRANT SELECT ON dbo.Customer TO user1;
GO
EXECUTE AS USER = 'user1';
SELECT * FROM dbo.Customer;
REVERT;
GO
-- 2. Removing Masking from a column by simple ALTER TABLE/COLUMN statement:
ALTER TABLE dbo.Customer
ALTER COLUMN LastName DROP MASKED;
GO
-- Let's check the table data again:
EXECUTE AS USER = 'user1';
SELECT * FROM dbo.Customer;
REVERT;
GO
-- 3. Granting the UNMASK permission to “user1”:
GRANT UNMASK TO user1;
GO
-- Let's check the table data again:
EXECUTE AS USER = 'user1';
SELECT * FROM dbo.Customer;
REVERT;
GO
-- 4. REVOLE UNMASK TO user1:
REVOKE UNMASK TO user1;
GO
EXECUTE AS USER = 'user1';
SELECT * FROM dbo.Customer;
REVERT;
GO
-- 5. FINAL CLEANUP:
DROP TABLE dbo.Customer;
GO
DROP USER [user1]
GO
