Yesterday I’ve writen about dynamic data masking in Azure SQL (works with SQL Server 2016 too). It is very interesting and simple feature. But can we use it with our beloved Entity Framework?

It’s strongly recommended to know some basics about dynamic data masking, so if you don’t know what it is or how to use it, just take a look at my previous article that covers this topic before continuing.

Scenario

Lets start with our scenario. We’ll have very simple console demo application (you can clone code from this repo) with one entity class and two DbContext derived classes using two different connection strings that will simulate two separate client applications fetching data from shared database.

Demo is very simple and it’s role is just to ilustrate some things I’ll write about, feel free to add some code and play with it as you see fit. I’ve done it in few minutes so it just prints stuff to console but with this little sample and any Azure SQL or 2016 SQL database you can try it out and decide if you have any use for data masking in your applications.

You just need to fill in connection strings in app.config and run few sql statements againsts master and test database. You have some simple instructions in Program.cs and sample_azuresql.sql comments.

So we have those thwo DbContexts. First one is UnmaskedModel which runs on connection string that have user name and password of someone with administration privileges. It will simulate some administration app with full access to data.

Second DbContext is MaskedModel that should have login data of user with basic permissions. It will simulate application without access to some super confidential data like my last name and date of birth.

Database

In demo code I’m providing sample scripts to run against you database. You can recognize some of this code from previous post, which I recomend to read before going further. I’ll paste sample sql below for reference.

-- Execute on master CREATE LOGIN testuser WITH PASSWORD = N'Abcd1234' GO --Execute on your db CREATE TABLE [dbo].[Person] ( [Name] nvarchar(100) NOT NULL, [LastName] nvarchar(100) MASKED WITH (FUNCTION = 'partial(1, "***", 0)') NOT NULL, [DateOfBirth] date MASKED WITH (FUNCTION = 'default()') NOT NULL, [Rating] bigint MASKED WITH (FUNCTION = 'default()') NOT NULL ) GO INSERT INTO [dbo].[Person] VALUES ('Rafal', 'Hryniewski', '1988-05-16', 100) GO 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 -- Execute on master CREATE LOGIN testuser WITH PASSWORD = N 'Abcd1234' GO --Execute on your db CREATE TABLE [ dbo ] . [ Person ] ( [ Name ] nvarchar ( 100 ) NOT NULL , [ LastName ] nvarchar ( 100 ) MASKED WITH ( FUNCTION = 'partial(1, "***", 0)' ) NOT NULL , [ DateOfBirth ] date MASKED WITH ( FUNCTION = 'default()' ) NOT NULL , [ Rating ] bigint MASKED WITH ( FUNCTION = 'default()' ) NOT NULL ) GO INSERT INTO [ dbo ] . [ Person ] VALUES ( 'Rafal' , 'Hryniewski' , '1988-05-16' , 100 ) GO

In code above we’re creating simple table with masks and insert one row of basic data into it. Remember that first statement should be executed on master, it will create login and soon we’ll create our test user from this login. Here’s some more code

--Execute on your db CREATE USER testuser FROM LOGIN testuser GO ALTER ROLE db_datareader ADD MEMBER testuser GO ALTER ROLE db_datawriter ADD MEMBER testuser GO --test permissions with this statement, results should be masked EXECUTE AS USER = 'testuser' SELECT * FROM [dbo].[Person] GO 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 --Execute on your db CREATE USER testuser FROM LOGIN testuser GO ALTER ROLE db_datareader ADD MEMBER testuser GO ALTER ROLE db_datawriter ADD MEMBER testuser GO --test permissions with this statement, results should be masked EXECUTE AS USER = 'testuser' SELECT * FROM [ dbo ] . [ Person ] GO

Next we’re creating user from login and assigning it to some basic read/write allowed roles. It’s pretty basic database security stuff. After that we can run our test SELECT statement with impersonation on our newly created user. If after that your result is masked you done everything right.

Demo is rather simple and just prints person data to console. However each context will present different result from same table in same database.

Console.WriteLine($"Unmasked - Name: {person.Name} LastName: {person.LastName} Date of Birth: {person.DateOfBirth} Rating: {person.Rating}"); 1 Console . WriteLine ( $ "Unmasked - Name: {person.Name} LastName: {person.LastName} Date of Birth: {person.DateOfBirth} Rating: {person.Rating}" ) ;

Result in console will look like that:

Unmasked - Name: Rafal LastName: Hryniewski Date of Birth: 1988-05-16 00:00:00 Rating: 100 Masked - Name: Rafal LastName: H*** Date of Birth: 1900-01-01 00:00:00 Rating: 0 1 2 Unmasked - Name: Rafal LastName: Hryniewski Date of Birth: 1988-05-16 00:00:00 Rating: 100 Masked - Name: Rafal LastName: H*** Date of Birth: 1900-01-01 00:00:00 Rating: 0

Before I’ll conclude this post with possible real life use case let’s see where is the difference between two DbContexts.

If you haven’t figured it out its user identity in connection strings. UnmaskedModel have user with admin permissions and will return unmasked data, however MaskedModel have our testuser data who can’t see confidential stuff. So the only difference is logged in user provided in connection string. It so simple it hurts.

Real life case?

You can use this knowledge as you see fit. If you don’t see any application for thins in real life cases let me provide you with two simple ones.

You’re developing application for company’s department that needs to operate on entire model but shouldn’t see everything. Same database, same entities, maybe even same DAL code – but different connection string and it’s done. Company exposing it’s production database for developers for any reason(tracing a bug related to data in db, analyzing DAL layer or something) without doing backups, copies or inserting some fake data. This way you’ll operate on data from production environment without seeing sensitive information.

Those ideas are first that’ve came into my head, they could be simple and there’s probably a lot of more complicated cases one out there. But most important thing from this post is – you can control what you’re database spitting out with simple connection string, use that.

Link