Blog - New data security features in SQL Server 2016

  • Share
  • Share
yellow server cables

New data security features in SQL Server 2016

Noteworthy data-security features in SQL Server 2016: Row-level Security and Dynamic Data Masking

Kevin Galbreath | November 19, 2015

There’s no shortage of stories of sensitive data getting into the wild—from Experian and T-Mobile to TalkTalk and the US government’s Office of Personnel Management.

Microsoft SQL Server 2016 provides database developers with new tools to address the ever-growing threat of data leaks. When talking about data security, two new features in SQL Server 2016 stand out: Row-level Security and Dynamic Data Masking.

Row-level Security

Row-level Security (RLS) has been a staple in SQL Server Analysis Services for years, but with SQL Server 2016, Microsoft is finally bringing it to the database engine.

RLS limits both read and write access based on attributes of the user, such as geography, organizational department, or simply who the user is. RLS essentially acts as a WHERE clause in all queries including the table(s) applied to—operating independently of developers’ code to allow for a much easier implementation.

RLS works by first creating a predicate function that limits the rows that a user can access and then using a security policy and security predicate to apply that function to a table.

For example, let’s say we have a dbo.Orders table:

dbo.OrdersSecurityPredicate function in SQL Server 2016

We want to return only the orders in which the user is the representative, so we need a predicate function that will make this comparison:

dbo.Orders table in SQL Server 2016

Now we need a security policy to apply our function to the dbo.Orders table:

dbo.Orders table security policy in SQL Server 2016

As a result, any queries written against the dbo.Orders will apply the dbo.OrdersSecurityPredicate function to each row and will only return the appropriate rows.

Important side note: Microsoft does acknowledge that side-channel attacks are still possible with RLS, either through security policies or well thought-out queries.

For more information, refer to Microsoft’s MSDN article on Row-level Security.

Dynamic Data Masking

Dynamic Data Masking, or DDM, enables developers and administrators to mask sensitive data, such as social security numbers or dates of birth, from non-privileged users.

Like Row-level Security, DDM is applied at the table level so all queries are affected by the masking. Masking rules are applied on individual columns.

There are four types of masks:

  1. Default: Provides full masking depending on the data type. For example: “XXXX” for string data types; zeros for numeric data types; “01.01.2000 00:00:00.0000000” for date and time; and a single byte ASCII 0 value for binary data types.

  2. Email: This mask shows the first letter of the email address followed by XXX[at]XXXX[dot]com—no matter what the actual domain suffix is.

  3. Custom String: This mask shows the first and last letters of the string, with a custom padding in between.

  4. Random: These masks are used to mask numeric data and provide a random value in a defined range.

Let’s say we have a dbo.Customers table:

dbo.Customers table in SQL Server 2016

Now we want to apply a default mask to FirstName; a custom mask to LastName so that we can see the first and last letters; an email mask to Email; a default mask to DOB; and a random numeric mask to NetWorth:

dbo.Customers table in SQL Server 2016 default mask to FirstName; a custom mask to LastName so that we can see the first and last letters; an email mask to Email; a default mask to DOB; and a random numeric mask to NetWorth

If we create a new non-privileged user and grant them access to the table, we’ll see the masking at work:

non privileged user in dbo.Customers table SQL Server 2016

Like Row-level Security, queries can be created that manage to skirt DDM, so always use caution.

For more information, refer to Microsoft’s MSDN article on Dynamic Data Masking.

Row-level Security and Dynamic Data Masking are just two of the new features in SQL Server 2016 that provide developers with powerful new tools to protect databases from security breaches. While these features aren’t silver data-protection bullets, they do strengthen overall database security.

Release note: SQL Server 2016 CTP 3.0 is currently available.

Computer screen with lines of code

Looking for more engineering tips?

Our engineers have a whole lot to say about custom software. They’re in the trenches every day, building, breaking, re-building, and sharing their hard-won wisdom along the way. Find their latest and greatest discoveries on Slalom’s new software engineering blog.

Read our engineering blog

Kevin Galbreath is an information management consultant in Slalom New York’s information management and analytics practice. Kevin is passionate about the power of data and is focused on delivering business intelligence solutions.

            

Start a conversation

What’s on your mind? Let’s explore the possibilities.