SQL Server 2016 Row Level Security

Play SQL Server 2016 Row Level Security

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.