The new security features in SQL Server 2017 are something that every business could use.

In this 4 part series, i will be talking about the most important features in SQL Server 2017.

 

Dynamic data masking

Intro

Encrypt sensitive information on certain columns by using DDM.

Something to note with this, is that you need to make sure that ad hoc querying for these users is locked down, since they can still filter on these encrypted rows,

and find matching data based on predicates for example by adding a where clause to the queries.

 

Example

 

We create a table, apply a couple of masking functions on it , and fill it with some records,.

 

CREATE TABLE Kohera 
(ID int IDENTITY PRIMARY KEY, 
FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL, 
LastName varchar(100) NOT NULL, 
PhoneNumber varchar(12) MASKED WITH (FUNCTION = 'default()') NULL, 
EmailAddress varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);
INSERT Kohera (FirstName, LastName, PhoneNumber, EmailAddress) VALUES 
('Randi', 'Vertongen', '0488434015', 'randi.vertongen@kohera.be'), 
('Frederik', 'Bogaerts', '0476673854', 'frederik.bogaerts@kohera.be'), 
('Stijn', 'Wynants', '0476348910', 'stijn.wynants@kohera.be');

If I select everything in the table (sysadmin) everything is returned:

SELECT * FROM Kohera;

IDFirstNameLastNamePhoneNumberEmailAddress
1RandiVertongen0488434015randi.vertongen@kohera.be
2FrederikBogaerts0412345678frederik.bogaerts@kohera.be
3StijnWynants0412345678stijn.wynants@kohera.be

 

But if a basic user has select permissions:

 

CREATE USER TestUser WITHOUT LOGIN; 
GRANT SELECT ON Kohera TO TestUser;
EXECUTE AS USER = 'TestUser'; SELECT * FROM Kohera;
REVERT;

 

The result is different:

 

IDFirstNameLastNamePhoneNumberEmailAddress
1RXXXXXXXVertongenxxxxxxxxxrXXX@XXXX.com
2FXXXXXXXBogaertsxxxxxxxxxfXXX@XXXX.com
3SXXXXXXXWynantsxxxxxxxxxsXXX@XXXX.com

 

Final thoughts

As noted above, if the user can apply ad hoc queries to the database,

you will need to remember that they can apply filtersĀ  onĀ  the masked data.

But if handle accordingly, and restrict your user permissions, this can be a very useful tool.

Leave a Comment

Your email address will not be published. Required fields are marked *