Building More Secure Middle-Tier Applications with Azure SQL Database using Row-Level Security

已于 三月 2, 2015 发布

Principal PM Manager, Azure SQL Database
Row-Level Security (RLS), a new programmability feature for Azure SQL Database, is now available in preview for all databases on the latest version of SQL Database (V12). Row-Level Security enables developers to implement fine-grained access control over rows in a database table. This can help prevent unauthorized access when multiple users share the same tables, and can help you implement connection filtering in multi-tenant applications. This post will help you get started building secure middle-tier and multi-tenant applications with Azure SQL Database using RLS. Let's assume we have a three-tier ecommerce SaaS solution that our customers use to track their sales. Our customers can only access sales data through a middle-tier application, so we adopt a multi-tenant architecture in our data tier for efficiency. Sales data for all tenants (our customers) are thus stored in the same database and table: dbo.Sales
OrderId SKU Price TenantId
1 Book001 10 1
2 Movie001 15 2
3 Movie002 12 2
We want to ensure that tenants can only see their own sales data. We could, for instance, manually append a "WHERE TenantId = 1" predicate every time our application queries this table while Tenant 1 is logged in. But rewriting queries with filtering logic in our application like this can be error-prone and difficult to maintain whenever we modify our codebase. Using RLS, we can centralize the filtering logic within the database itself, which helps simplify maintenance and reduce the risk of error as we continue developing our application.

Step 1: Set the application user context with CONTEXT_INFO

We make just one change to our application. Because our application uses connection pooling and all application users share the same database login, the application needs to tell the database which tenant is currently logged in so that RLS knows which rows to filter out. To do this we’ll use CONTEXT_INFO, a connection-scoped global variable now accessible in the SQL Database V12 Update. In SSMS or SSDT, let's create a stored procedure in T-SQL that our application can use to set CONTEXT_INFO to a TenantId:
CREATE PROCEDURE sp_setContextInfoAsTenantId(@TenantId int)
After opening a connection, our application simply executes this stored procedure with the TenantId of the logged in user.

Step 2: Encode the access logic in a predicate function

Now we'll create an inline table-valued function for our row access logic. This predicate function can be arbitrarily complex (for instance, joining with other tables or checking multiple parameters to enforce hierarchical access control), but for our application it's really simple. As a best practice, we’ll put our predicate functions and security policies in a separate schema.
CREATE FUNCTION rls.fn_tenantAccessPredicate(@TenantId int)
RETURN SELECT 1 AS fn_accessResult 
WHERE DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser') -- the shared application login
AND CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())) = @TenantId
When a user queries our Sales table, this function will be applied to every row, filtering out rows where the TenantId does not match the current value of CONTEXT_INFO (or if a SQL login other than the application has somehow connected directly to the database). The query optimizer will treat this function apply as equivalent to appending the above WHERE predicate to all SELECT, UPDATE, and DELETE queries on the Sales table.

Step 3: Bind the function to our tables with a security policy

Finally, we'll create a security policy that binds this function as a filter predicate on our Sales table.
CREATE SECURITY POLICY rls.tenantAccessPolicy
ADD FILTER PREDICATE rls.fn_tenantAccessPredicate(TenantId) ON dbo.Sales
That's it! Rows are now automatically filtered from queries on the Sales table based on the current value of CONTEXT_INFO. To verify this, we can simulate the application setting CONTEXT_INFO through our stored procedure:
EXECUTE sp_setContextInfoAsTenantId 1
Only rows for Tenant 1 are returned (in this case, just Book001).

Best practices and next steps

Note that passing the application user context to the database through CONTEXT_INFO assumes that your application users cannot execute arbitrary T-SQL (for instance, through SQL injection) to change the value of CONTEXT_INFO or to insert rows that belong to other tenants. Built-in support for blocking inserts is not included in this version of RLS, but you can achieve equivalent functionality using triggers or check constraints (covered in future blog topic), or by controlling inserts through stored procedures. Also note that while your predicate functions can be as complex as you need, including more than a few table joins within the function will degrade query performance. So what’s next? For more detailed information about RLS, check out our online preview documentation. Otherwise, please post your questions and comments below – we're eager to hear your feedback to help us iterate and drive the feature forward. Full demo script: RLS-Middle-Tier-Apps-Demo.sql can be found here.