infoTECH Feature

March 10, 2017

The Row-Level Security Secrets All DBA Experts Should Know

By Special Guest
Sujain Thomas, Senior DBA Expert

We love the SQL Server 2016, and for more than one reason. The 2016 version came with a number of added features and upgrades to its existing features that enhance the SQL Server security aspects like never seen before. One of the newest features of the 2016 version is Row-Level Security.

As the name states, Row-Level refers to accessing the rows level of the database table. This eliminates the entire process of complex coding at the application layer and is usually completed within the DB layer itself. It is completely transparent to the whole application process.

What are the functions of RLS?

Row-Level Security (RLS) effectively provides the DBAs with a way to confine all user activity to the limited data they have direct entry to. Quite like the shipment information on an e-commerce website, the RLS restricts access of a user to his/her profile. Take for example – if you have an incoming shipment of products from an e-commerce site, you can access the information and details of the products that you have ordered. You do not have access to someone else’s shipment information without a secure login. So, each time the data access will be limited and monitored by the SQL Server engine following data access from any tier. This reduces the security system surface area.

At the moment, there are two separate types of security systems at the row-level:

  1. Filter predicate
  2. Block predicate

Filter Predicate 

This is a security option that filters the row silently (without any error messages) for read operations. This essentially translates to the fact that the application will not be aware of the data filtration process. All null values are to be returned upon filtration of all rows without emanating any error message.

Block predicate

This predicate averts any and all write operation that may violate any of the defined predicates. This is returned with an error message due to the action of the active block predicate. This predicate can be switched On or Off with the following four blocking types:

  1. AFTER INSERT
  2. AFTER UPDATE
  3. BEFORE UPDATE
  4. BEFORE DELETE

While the first two prevent the access of any and all write operation from updating any of the rows that violate the defined predicates, the latter two prevent users from updating rows in current violation of any predicate and deleting rows on the database respectively.

Row-Level Security can be accomplished on an active SQL Server by creating a security state that has the power to restrict all the rows depending on specific filtering logic. This is both invoked and imposed by a security policy that is essentially created using a T-SQL statement. The SQL Server allows all remote DBA experts to create more than one security policy that can be both activated and used at the same time without overlapping any predicates.

However, not all is sunny with the SQL ServerRLS. It is a new feature and it comes with its own limitations. Here’s a comprehensive list of the limitations we have explored so far:

  • Both Filestream and Polybase are not friendly towards the RLS feature of 2016.
  • While this feature is active you will be unable to create an indexed view on a table with RLS.
  • If you are looking forward to configuring Change Data Capture, Change Tracking and/or Temporal Tables, we’d suggest you skip turning the Row-Level Security feature on.
  • The blocked predicate has displayed no compatibility with partitioned views, so far.
  • The RLS feature uses a function that can cause the queries using Column store indices to be re-written. This will prevent the use of batch-mode during table modification.

Let us see how the RLS feature actually works in the practical DBMS world. Here’s a demo table that will exemplify the implementation of RLS as per the 2016 updates:

After the creation of the table, you can insert a few rows and insert three users and grant exclusive access on the Courier_Shipments table for the three specific users only:

Now, as per the requirement of all RLS, a separate schema should be created for each database object. After this, we should make the Filter Predicate function and then check their access on the database:

And then define the security policy on the Courier_Shipments table with the predicate function defined before:

At this stage, we can consider the Row-Level Security fully configured. It can now filter any new data access on the Courier_Shipments table, as defined. The newly formed Security Predicate can be seen in the snapshot below:

A query as follows: 

Will elicit the following result:

Similar results will be seen if ‘John’ in query is replaced by Ghezil and/or Mark.

Now, it is understandable from the previous results that the RLS feature can be used to filter data that a particular user can see based on the filtering criteria in the predicate function only. In case you manage to stop using the RLS predicate, you need to be able to disable the security feature using the standard ALTER SECURITY POLICY statement:

Alter Security Policy CourierShipments with (State = off)

In order to remove the Row-Level Security attribute completely from the current table you may want to switch-off the security level and filter predicate function:

A persistent question

Now, a question that may be plaguing the minds of many expert and novice DBAs by now is: “Does the use of RLS feature have any side effects?” In reality, that answer depends on a lot of factors. For example – when a user defines a simple direct predicate filter he/she may not even notice any fall of performance quality or overhead in the concerned database.

The SQL Server 2016 indeed comes with an interesting and useful feature that is now well known as the RLS feature. This is currently used to secure access at the row level, by usually filtering data that the user cannot access. The users are selectively given access to the rows he/she has access to and not the entire database.

About the Author

Sujain Thomas has been a senior DBA expert for quite a few years who takes a keen interest in the evolution of DBMS. She has helped a number of remote DBA experts find their calling with her inspirational and informational DBMS and RDBMS blogs.




Edited by Alicia Young
FOLLOW US

Subscribe to InfoTECH Spotlight eNews

InfoTECH Spotlight eNews delivers the latest news impacting technology in the IT industry each week. Sign up to receive FREE breaking news today!
FREE eNewsletter

infoTECH Whitepapers