Row-Level Security block predicates are generally available on Azure SQL Database

Azure SQL Database’s Row-Level Security (RLS) feature now officially supports block predicates. In contrast to filter predicates, which limit the rows visible to a user, block predicates prevent a user from inserting, updating, or deleting rows that violate your access criteria. You should use filter and block predicates together to control both read and write access to… Read more

Tutorial: Row-Level Security and Entity Framework

In response to a common customer question, we’ve published a short tutorial showing how to use Row-Level Security with Entity Framework.  The tutorial extends an existing ASP.NET MVC app to support a “shared database, shared schema” tenancy model. In this model, a single database contains data for many tenants, and each row in each table is… Read more

Oil & Gas Security Demo with SQL Server 2016

At our security session today at PASS Summit 2015, we were extremely fortunate to be joined by Jamey Johnston, a Data Scientist at a large independent Oil & Gas company, who shared a comprehensive demo using Row-Level Security, Dynamic Data Masking, and Always Encrypted to control access to oil well production data. Jamey’s demo showcases… Read more

New Row-Level Security functionality: Block predicates (preview)

Block predicates are now available as a preview enhancement for Row-Level Security (RLS) on Azure SQL Database. Block predicates address a common point of customer feedback, by enabling security policies to prevent users from inserting, updating, and/or deleting rows that violate the predicate. You can try block predicates today on any Azure SQL Database (V12)… Read more

Row-Level Security for Azure SQL Database is Generally Available

Row-Level Security (RLS) for Azure SQL Database is now generally available. RLS enables you to store data for many users in a single database and table, while at the same time restricting row-level access based on a user’s identity, role, or execution context. RLS centralizes access logic within the database itself, which simplifies and reduces the… Read more

Optimizing RLS performance with the Query Store

In a previous post, we looked at best practices for optimizing the performance of Row-Level Security (RLS) predicates: adding relevant indexes, minimizing excessive joins, etc. This post shows you how to discover and verify empirically which configurations yield the best performance for your workload. We’ll do this by A/B testing different RLS configurations, and using… Read more

Apply Row-Level Security automatically to newly created tables

We have discussed before that applications with multi-tenant databases, including those using Elastic Scale for sharding, commonly have a “TenantId” column in every table to indicate which rows belong to each tenant.   In that previous post, we shared with you a SP that could help you to streamline the creation of a security policy… Read more

How to: Scale out multi-tenant apps using RLS and Elastic Database Tools

In response to a common customer ask, we’ve published guidance for developing multi-tenant applications on Azure SQL Database using row-level security (RLS) for tenant isolation and elastic database tools (formerly “Elastic Scale”) for sharding. These technologies can be used together to flexibly and efficiently scale the data tier of a multi-tenant application, with support for… Read more

Row-Level Security: Performance and common patterns

This post demonstrates three common patterns for implementing Row-Level Security (RLS) predicates: Rows assigned directly to users Row assignments in a lookup table Row assignments from a JOIN In addition, this post shows how RLS has performance comparable to what you’d get with view-based workarounds for row-level filtering. The benefits of using RLS instead of… Read more

Apply Row-Level Security to all tables — helper script

Developing multi-tenant applications with Row-Level Security (RLS) just got a little easier. This post makes available a script that will automatically apply an RLS predicate to all tables in a database. Applications with multi-tenant databases, including those using Elastic Scale for sharding, commonly have a “TenantId” column in every table to indicate which rows belong… Read more