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
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 (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:
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:
Now we need a security policy to apply our function to the dbo.Orders table:
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:
- 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.
- 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.
- Custom String: This mask shows the first and last letters of the string, with a custom padding in between.
- 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:
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:
If we create a new non-privileged user and grant them access to the table, we’ll see the masking at work:
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.
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.