SQL Server Portal

In SQL Server 2022, a new enhancement was introduced in dynamic data masking known as Granular Permissions which was a long-awaited enhancement. This new enhancement gives us control to provide unmasking permission at lowest level (table’s column) which we could not achieve in the earlier version of SQL Server. In other words, we can give permissions to unmask columns, tables, schemas & databases to different users based on their accessibility levels.

Let me create a sample to demonstrate the functionality of Dynamic data masking granular permissions in which, I will create a Sample database, two schemas and each schema will have one table as shown below.

Sample:

--Create Sample Database
CREATE DATABASE SampleDB; 
GO

USE SampleDB
GO

--Create HumanResources Schema 
CREATE SCHEMA HumanResources;
GO

--Create Accounts Schema 
CREATE SCHEMA Accounts;
GO

--Create Employee Table
CREATE TABLE HumanResources.Employee
(
 EmployeeID    INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
 EmployeeName  VARCHAR(250),
 Birthdate     DATE MASKED WITH (FUNCTION = 'default()') NULL,
 Salary        DECIMAL(10,2) MASKED WITH (FUNCTION = 'default()') NULL 
);
GO

--Create BankAccount Table
CREATE TABLE Accounts.BankAccount
(
 AccountID     INT IDENTITY(1,1) PRIMARY KEY, 
 EmployeeID    INT NOT NULL,
 AccountTitle  VARCHAR(250) MASKED WITH (FUNCTION = 'default()') NULL,
 AccountNumber VARCHAR(250) MASKED WITH (FUNCTION = 'partial(3,"xxxxxxxxx", 4)') NULL
);
GO

--Insert few records into Employee Table
INSERT INTO Humanresources.Employee(EmployeeName,Birthdate,Salary)
VALUES ('Nancy','1988-12-08','200000'),
       ('Andrew','1994-07-16','120000'),
	   ('Janet','1994-07-16','75000');
GO

--Insert few records into BankAccount table
INSERT INTO Accounts.BankAccount(EmployeeID,AccountTitle,AccountNumber)
VALUES(1,'Nancy','9922-0105664197'),
      (2,'Andrew','0010-0107605278'),
	  (3,'Janet','4010-3568743987');
GO

Configuration:

Step 1:

Let’s create few users, which will be used to provide relevant permissions to unmask data as shown below.

USE SampleDB
GO

--Create User HRAdministrator 
CREATE USER HRAdministrator WITHOUT LOGIN; 
GO

--Create User PayrollAdministrator 
CREATE USER PayrollAdministrator WITHOUT LOGIN; 
GO  

--Create User Accountant
CREATE USER Accountant WITHOUT LOGIN;  
GO

--Create User HRManager
CREATE USER HRManager WITHOUT LOGIN;  
GO

Step 2:

Let’s add the db_datareader role in each user created above.

USE SampleDB
GO

--Grant db_datareader role to HRAdministrator
ALTER ROLE db_datareader ADD MEMBER HRAdministrator; 
GO

--Grant db_datareader role to PayrollAdministrator 
ALTER ROLE db_datareader ADD MEMBER PayrollAdministrator ; 
GO

--Grant db_datareader role to Accountant
ALTER ROLE db_datareader ADD MEMBER Accountant; 
GO

--Grant db_datareader role to HRManager
ALTER ROLE db_datareader ADD MEMBER HRManager;
GO

Step 3:

Let’s provide UNMASK permissions to above created users. The details of the access are as shown below:

• HRAdministrator : can view Birthdate column Data only in Employee table.
• PayrollAdministrator : can view Salary column Data only in Employee table .
• Accountant : can view the entire tables data in Account schema only.
• HRManager : can view the entire data in the SampleDB database.

USE SampleDB
GO

--Grant Birthday column unmask permission to HRAdministrator;
GRANT UNMASK ON Humanresources.Employee(Birthdate) TO HRAdministrator;
GO

--Grant salary column unmask permission to PayrollAdministrator
GRANT UNMASK ON Humanresources.Employee(Salary) TO PayrollAdministrator;
GO

--Grant Accounts schema unmask permission to Accountant
GRANT UNMASK ON SCHEMA::Accounts TO Accountant;  
GO

--Grant entire database unmask permission to HRManager
GRANT UNMASK TO HRManager;
GO

Testing:

• HRAdministrator

Let’s access the Employee table under the context of HRAdministrator user.

USE SampleDB
GO

EXECUTE AS USER='HRAdministrator';  
SELECT EmployeeID
     , EmployeeName 
	 , Birthdate
	 , Salary 
FROM   Humanresources.Employee; 
REVERT;
GO
--OUTPUT

As we can see above, the HRAdministrator can view the Birthdate column data in Employee table but cannot view Salary column data in Employee table.

• Let’s access the BankAccount table under the context of HRAdministrator user.

USE SampleDB
GO

EXECUTE AS USER='HRAdministrator';  
SELECT EmployeeID
     , AccountTitle
	 , AccountNumber 
FROM   Accounts.BankAccount;  
REVERT;
GO
--OUTPUT

As we can see above, the HRAdministrator can’t view the data of BankAccount Table since unmask permission is not given.

• PayrollAdministrator

Let’s access Employee table under the context of PayrollAdministrator user.

USE SampleDB
GO

EXECUTE AS USER='PayrollAdministrator';  
SELECT EmployeeID
     , EmployeeName
	 , Birthdate
	 , Salary 
FROM   Humanresources.Employee;  
REVERT;
GO
--OUTPUT

As we can see above, the PayrollAdministrator can view the Salary column data in Employee table but cannot view Birthdate column data in Employee table.

• Let’s access the BankAccount table under the context of PayrollAdministrator user.

USE SampleDB
GO

EXECUTE AS USER='PayrollAdministrator';  
SELECT EmployeeID
     , AccountTitle
	 , AccountNumber 
FROM   Accounts.BankAccount;
REVERT;
GO
--OUTPUT

As we can see above, the PayrollAdministrator can’t view the data of BankAccount Table since unmask permission is not given.

• Let’s access the Employee table under the context of Accountant user.

USE SampleDB
GO

EXECUTE AS USER='Accountant';  
SELECT EmployeeID
     , EmployeeName
	 , Birthdate
	 , Salary 
FROM   Humanresources.Employee;  
REVERT;
GO
--OUTPUT

As we can see above, the Accountant cannot view the Salary, Birthdate column data in Employee table since unmask permission is not given.

• Let’s access the BankAccount table which is in Accounts schema under the context of Accountant user.

USE SampleDB
GO

EXECUTE AS USER='Accountant';  
SELECT EmployeeID
     , AccountTitle
	 , AccountNumber 
FROM   Accounts.BankAccount;
REVERT;
GO
--OUTPUT

As we can see above, the Accountant can view the data of BankAccount Table.

• Let’s access the Employee table which is in HumanResources schema under the context of HRManager user.

USE SampleDB
GO

EXECUTE AS USER='HRManager';  
SELECT EmployeeID
     , EmployeeName
	 , Birthdate
	 , Salary 
FROM   Humanresources.Employee;  
REVERT;
GO
--OUTPUT

As we can see above, the HRManager can view the data of Employee Table.

• Let’s access the BankAccount table which is in Accounts schema under the context of HRManager user.

USE SampleDB
GO

EXECUTE AS USER='HRManager';  
SELECT EmployeeID
     , AccountTitle
	 , AccountNumber 
FROM   Accounts.BankAccount; 
REVERT;
GO
--OUTPUT

As we can see above, the HRManager can view the data of BankAccount Table.

Conclusion:

Granular Permission in dynamic data masking is a very handy enhancement, it gives us the control to unmask column, table, schema, database data to different users. Do let me know if you use it and found it helpful.

Read Full Post »