Row-Level Security is now supported for Microsoft Azure SQL Data Warehouse
Today we’re announcing the general availability of Row-Level Security (RLS) for Microsoft Azure SQL Data Warehouse, an additional capability for managing security for sensitive data. Azure SQL Data Warehouse is a fast, flexible, and secure cloud data warehouse tuned for running complex queries fast and across petabytes of data.
As you move data to the cloud, securing your data assets is critical to building trust with your customers and partners. With the introduction of RLS, you can implement security policies to control access to rows in your tables, as in who can access what rows. RLS enables this fine-grained access control without having to redesign your data warehouse. This simplifies the overall security model as the access restriction logic is located in the database tier itself rather than away from the data in another application. RLS also eliminates the need to introduce views to filter out rows for access control management. In addition, RLS supports both SQL authentication and Azure Active Directory (AAD) authentication.
Here are a few scenarios where RLS could be leveraged today:
- A healthcare provider enforces a security policy that allows nurses to view only data rows for their own patients.
- A financial services firm restricts access to rows of financial data based on either the employee’s business division or employee’s role within the company.
- A multi-tenant application enforces logical separation of each tenant's data rows from every other tenant's rows.
RLS is a form of predicate-based access control that works by automatically applying a security predicate to all queries on a table. The predicate determines which users can access what rows. For example, a simple predicate might be, “WHERE SalesRep = SYSTEM_USER,” while a complicated predicate might include JOINs to look up information in other tables.
There are two types of security predicates:
- Filter predicates silently filter SELECT, UPDATE, and DELETE operations to exclude rows that do not satisfy the predicate.
- Block predicates explicitly block INSERT, UPDATE, and DELETE operations that do not satisfy the predicate.
In this release, Azure SQL Data Warehouse only supports filter predicates while support for block predicates will be released soon. Also, in this release, RLS doesn’t support external tables created via PolyBase.
To add a security predicate on a table, you first need an inline table-valued function that defines your access criteria. Then, you create a security policy that adds filter predicate on any tables you like, using this function. Here’s a simple example that prevents sales representatives from accessing rows in a customers table that are not assigned to them:
CREATE SCHEMA security;
CREATE FUNCTION security.customerPredicate(@SalesRepName AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS accessResult
WHERE @SalesRepName = SYSTEM_USER OR SYSTEM_USER = 'Manager';
go
CREATE SECURITY POLICY security.customerAccessPolicy
ADD FILTER PREDICATE security.customerPredicate(SalesRepName) ON dbo.Customers
WITH (STATE = ON);
go
This capability is available now in all Azure regions with no additional charge. The rollout has been completed in a few regions, with the goal to finish worldwide deployment within the next two weeks. Azure SQL Data Warehouse continues to lead in the areas of security, compliance, privacy, and auditing.
Next steps
- For more information on RLS in Azure SQL Data Warehouse, refer to our online documentation.
- For more information about Azure SQL Data Warehouse security capabilities, refer to the “Guide to enhancing privacy and addressing GDPR requirements with the Microsoft SQL platform” from the Microsoft Trust Center, or our online documentation.
- To get started today, create an Azure SQL Data Warehouse.
- To stay up-to-date on the latest Azure SQL Data Warehouse news and features, follow us on Twitter @AzureSQLDW.
- For feature requests, please vote on our UserVoice.
Source: Azure Blog Feed