SQL Server 2016 Row Level Security

Play SQL Server 2016 Row Level Security

Description

In this episode of Data Exposed, Scott Welcomes not one, but TWO individuals to the show. Today, both Tommy Mullaney and Raul Garcia, Program Managers on the SQL Server SQL Security team, visit the show to talk about a new security feature in SQL Server 2016 called Row Level Security (RLS).

In the "Cloud First" era at Microsoft, RLS was first introduced in Azure SQL Database, and Data Exposed covered that on the show several months ago (https://channel9.msdn.com/Shows/Data-Exposed/Row-Level-Security-in-Azure-SQL-Database). RLS is feature that enables fine-grained control over access to rows in a table, allowing you to easily control which users can access which data with complete transparency to the application.

Both Tommy and Raul walk us through how this is implemented in SQL Server 2016, and they wrap up the episode by showing us an awesome demo of this great feature!

Embed

Download

The Discussion

  • User profile image
    BradCathey

    Great feature...Now if we could get claims-based authorization at the db level we could start implementing some really awesome end-to-end access control! Cough. Because my users aren't typically(ever) directly logged into the database. It is usually a service account acting "on behalf of" the end user. Not having claims-aware database security policy enforcement (along with the supporting driver/APIs) forces me to do fine-grained access control in the service tier. It would be nice to be able to enforce security policy such as RLS a bit closer to the metal to take advantage of what the db does well. :(

  • User profile image
    Vaccano

    I agree with BradCathey.  I cannot allow my users to have a login to my server or database.  Because then any of them can connect directly and bypass the logic in the middle (services) tier.

    So I end up with a "service user" that can do any read write action on the Database.  And then my middle tier handles the security.

    It would be nice if there was a way to get this closer to the database.

  • User profile image
    Marco Antonio Marquez Lafferte

    Realmente un aporte sustantivo para el manejo de office365

  • User profile image
    tmullaney

    @BradCathey and @Vaccano, if you have a mid-tier "app user" connecting to the database on behalf of your end users, you can use CONTEXT_INFO to pass along a user ID, which an RLS security policy within the database can then use for filtering. For instance, see the simple example in this post: https://azure.microsoft.com/blog/2015/03/02/building-more-secure-middle-tier-applications-with-azure-sql-database-using-row-level-security/

    We know CONTEXT_INFO isn't a complete solution, so we are working hard to provide more robust support for the mid-tier scenario before SQL Server 2016 ships. 

  • User profile image
    BradCathey

    @tmullaney:Thanks for the info. We were planning on working around the issue with custom stored procedures accepting flags based on user authorization, filtering from there...Not the most elegant or maintainable of solutions. We look forward to future advancement in the area.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.