This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.
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!