Lesson Learned #88: Implementing Data Masking with EXECUTE AS USER in Azure SQL Database

[Jose_Manuel_Jurado]

Our customer is using a legacy application that they cannot change the user that is connecting to the database but they want to use Data Masking and Row Level Security features in Azure SQL Database. Unfortunately, they cannot impersonate the user when they execute their application, for example, to use Azure Active Directory.

My first approach was: Adding a logic in their different TSQL that they have:

• Created an example table:

CREATE TABLE TableExample (Id INTEGER, 
			   UserType INTEGER,
			   DataCannotSeeUser1 VARCHAR(100),
			   DataCannotSeeUser2 VARCHAR(100))

INSERT INTO TableExample values(1,1,'Maria','Jose')
INSERT INTO TableExample values(2,2,'Jose','Maria')
INSERT INTO TableExample values(3,3,'Juan','Maria')
INSERT INTO TableExample values(3,2,'Juan','Maria')

• Our customer retrieve data in two ways the data, using Stored Procedure or Views:

CREATE PROCEDURE GiveData (@UserType INT)
AS
BEGIN
 IF(@UserType>=1)
  BEGIN
    IF(@UserType=1)
    BEGIN
       SELECT DataCannotSeeUser2, 
	          STUFF(DataCannotSeeUser1,2,LEN(DataCannotSeeUser1),REPLICATE('*',LEN(DataCannotSeeUser1))) AS DataCannotSeeUser1 FROM TableExample WHERE UserType = @UserType
    END
    IF(@UserType=2)
    BEGIN
       SELECT DataCannotSeeUser1, 
	          STUFF(DataCannotSeeUser2,2,LEN(DataCannotSeeUser2),REPLICATE('*',LEN(DataCannotSeeUser2))) AS DataCannotSeeUser2 FROM TableExample WHERE UserType = @UserType
    END
  END
 ELSE
   BEGIN
    SELECT DataCannotSeeUser1,
	       DataCannotSeeUser2 FROM TableExample
   END 
END 

create view ViewUser1
AS
SELECT DataCannotSeeUser2, 
       STUFF(DataCannotSeeUser1,2,LEN(DataCannotSeeUser1),REPLICATE('*',LEN(DataCannotSeeUser1))) AS DataCannotSeeUser1 FROM TableExample

create view ViewUser2
AS
       SELECT DataCannotSeeUser1, 
	          STUFF(DataCannotSeeUser2,2,LEN(DataCannotSeeUser2),REPLICATE('*',LEN(DataCannotSeeUser2))) AS DataCannotSeeUser2 FROM TableExample

The second approach was using EXECUTE AS based on, for example, a variable session of the application and session_context feature, for example:

• Connect to master database and create the login:

CREATE LOGIN login1 WITH PASSWORD = 'ComplexPassword99!'  

• Create database user, table example and give the permissions to the table to this user.

CREATE USER user1 FOR LOGIN login1;  

CREATE TABLE Contact
  (ID int IDENTITY PRIMARY KEY,
   Nombre varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,
   Apellido varchar(100) NOT NULL,
   NrTlf varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,
   Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);
 
INSERT Contact (Nombre, Apellido, NrTlf, Email) VALUES 
('Roberto', 'Torres', '91551234567', 'RTorres@contoso.com'),
('Juan', 'Galvin', '95551234568', 'JGalvin@contoso.com'),
('José', 'Garcia', '95551234569', 'Jgarcia@contoso.net');

GRANT SELECT ON Contact to User1;

• Add the value of the parameter UserToFilter by the name of the user, after stablishing the connection.

EXEC sp_set_session_context 'UserToFilter', 'User1'

• Execute AS example to filter data:

EXECUTE AS USER = convert(varchar(200),SESSION_CONTEXT(N'UserToFilter')) 
SELECT * FROM Contact;
REVERT; 

Enjoy!