Taking the Azure SQL Database row-level security preview for a spin

The security announcements around Azure SQL Database keep coming. Auditing was implemented a few months ago, and today it was followed by a preview of row-level security. There were also announcements around transparent data encryption (TDE) and a new dynamic data masking feature in future but these aren’t available, even in preview, yet. The row-level security announcements like the one in the SQL Server Team Blog preceded posting the documentation page, so if you originally got a “page not found” it’s up there now.

So I thought I’d provision-up a database (takes about 30 seconds if you have an Azure subscription) and take RLS for a spin. It’s quite important to read the documentation page first, not only because there’s a nice demo in there, but also to get a feel for the implementation details, i.e. what the feature is and what exactly it’s documented to do. I’ll try not to simply regurgitate the documentation here. That’s difficult because it’s quite a nice one-page summary with additional info in the “other resources” section.

RLS is implemented by means of a new schema-scoped object, the SECURITY POLICY object, and inline table-valued functions (TVFs) that implement the policy. The CREATE SECURITY POLICY DDL uses filter predicate clauses to tie the TVFs to individual tables, one filter predicate per table. The TVFs return a one-column table, with a column name of fn_securitypredicate_result. You can use any of the mechanisms and built-in security functions to determine which principals you’re dealing with and you can access which rows they can access with the TVF parameters can be column names. I’d stay away from security functions marked deprecated in books online. Remember, an inline TVF only contains a single SQL SELECT statement.

There a couple of nice things about using inline table-valued functions. First off, the functions need to be defined WITH SCHEMABINDING so there’s no way for someone to simply alter the function unless they drop and re-create the SECURITY POLICY object. In addition, the fact that it’s an inline TVF means that the function shows up as a specific “Filter” iterator in query plans because it’s inlined (unlike multi-statement and scalar TVFs which are not inlined, i.e. have a separate query plan of their own).

You can use ALTER SECURITY POLICY to turn the filters on and off, and to ADD and DROP filters. There are two new metadata tables, sys.security_policies and sys.security_predicates to allow visibility.

Naturally, DDL against SECURITY POLICY and the TVFs can be audited (I did try this out to check that it appeared in the audit file). Because the SECURITY POLICY object references the TVFs, I actually had to grant a number of permissions to allow “Manager” (from the first example in the docs) to create the policy (note: I changed the example a bit to define the SECURITY POLICY in the “Security” schema instead of dbo schema):

GRANT SELECT ON Security.fn_securitypredicate TO Manager;
GRANT REFERENCES ON Security.fn_securitypredicate TO Manager;
GRANT REFERENCES ON SCHEMA::dbo TO Manager;  — Needed because the table lives in DBO schema

— This allows ALTER or DROP, but not to CREATE unless ALTER ANY SECURITY POLICY is also given
— This may not be granular enough, unless the RLS objects are the only ones in the schema.

It looks like this feature has the potential to make it into the box-product SQL Server in future (the docs refer to it not being supported with memory-optimized tables for example, which Azure SQL Database doesn’t support yet), so even if you don’t use Azure SQL Database, the documentation page provides a good opportunity to CAREFULLY evaluate whether it will meet your needs, and get your comments/requests in now. There are four specific places in the documentation page to look at:

1. Limitations during the preview (e.g. limitation on views)
2. Limitations and restrictions (e.g. DBCC SHOW_STATISTICS)
3. The Description section (e.g. INSERT anywhere is allowed, not filtered)
4. A special section called “Security Note: Side-Channel Attacks”

I’d propose that the “INSERT anywhere not blocked” limitation is the biggest worry (modulo the side-channel attacks), especially in the multi-tenant (listed as a specific use-case) scenario. Although conceivably you might ameliorate this with an INSERT trigger. This doesn’t address the problem of someone changing CONTEXT_INFO though (as is mentioned in the doc), you’d need to set up different roles for each tenant as well.

Cheers, Bob (@bobbeauch)

3 thoughts on “Taking the Azure SQL Database row-level security preview for a spin

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.