Row-level security never got this easy with SQL Server 2016

By | In SQL Server | October 06th, 2015

Row-level security never got this easy with SQL Server 2016Security has been one of the focus area by Microsoft SQL Server product team. Almost all the releases either have some new security feature or enhancement of an existing features. The same story continues with SQL Server 2016 as well. There are many security features like “Always Encrypted”, “Dynamic Data Masking”, “enhancement of Transparent Data Encryption” and “Row-Level Security” added in SQL Server 2016. In this blog we would talk about Row-Level security feature.

Row-level security provides fine-grained access control over a particular row in a table in the database. While selecting the data from the table, rows are filtered based on execution context of every query. What would be filtered can be decided and implemented by security architect and can be implemented by new objects provided in SQL Server 2016. If you have ever implemented similar logic in application, it might have been via some views on the table having filter condition or stored procedures. The logic can be simplified and contained within the database using row-level security feature. Simplest way to understand is that there is an additional hidden filter has been applied before data is shown to the user. The filter can be designed in such a way that super user can see all the rows. This would mean that a user can run SELECT * FROM myTable without a WHERE clause and view the only that data to which they have access to.

Before we jump into the demo, here are the few new terms which one need to learn to understand this feature.

Security Predicate: This is not a new object but an inline table valued function (inline TVF) which contains the logic of filtering the rows.

Security Policy: This is a new object which can be CREATE’ed ALTER’ed and DROP’ed. You can imagine this as a container of predicates which can be applied to tables. One policy can contain security predicate to many tables. A policy can be in an ON or OFF state.

Let’s sail through the demo to understand how this can be implemented.

Step 1: Create two users to mimic two employees. As per security standard, they should see only their own data. In below script, we are also creating sample database, table and inserting some data.

Step 2: Create schema which contains objects related to row-level security.

Let’s do the testing.

As shown below, both employees can see their own data even if there is no filter which we have applied in the query.

Row-Level 1

This is one of the very basic implementation can based on requirement, the Security Predicate can be made more complex.

Whenever there is a discussion of implementation, one of the question which might come up would be that “is there any performance overhead due to this” and simple answer to this is – “it depends on the predicate defined”

In SQL Server Management Studio, we can see it under “Security” available under each database.

Row-Level 2

Here are few new DMVs which can help in looking at security policies and security predicates.

In summary, this feature would take care of custom filtering, in the form of a user defined table-valued function, and implementing it on the object for everyone. We can add more predicate in security policy and apply filter on multiple tables. Since we can turn the policy ON and OFF, it makes it easy to manage all filters at the same time. With this simple demo of RLS, hope this blog gives you an idea about this feature.

Contact Us
Eric Russo
Senior Vice President of Database Services
Eric Russo is SVP of Database Services overseeing all of Datavail’s database practices including project and managed services for MS SQL, Oracle, Oracle EBS, MySQL, MongoDB, SharePoint and DB2. He is also the Product Owner for Datavail Delta, a database monitoring tool. He has 21 years’ experience in technology including 16 years in database management. His management success and style has attracted top DBAs from around the world to create one of the most talented and largest SQL Server teams. He has been with Datavail since 2008: previous to that his work experiences include DBA Manager at StrataVia, Senior Web Developer at Manifest Information Systems and SQL Server DBA at Clark County, Nevada.

Leave a Reply

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

1 thought on “Row-level security never got this easy with SQL Server 2016”