다음을 통해 공유

Row-Level Security for Middle-Tier Apps – Using Disjunctions in the Predicate

  • 아티클

In Building More Secure Middle-Tier Applications with Azure SQL Database using Row-Level Security, we discussed how CONTEXT_INFO could be used for middle-tier based RLS predicate definitions.

In many occasions it is necessary to introduce a disjunction to the predicate definition for scenarios that need to distinguish between filtered queries for some users and cases where a user must not be subject to filtering (i.e. administrator, etc.), and such disjunctions may potentially affect performance significantly.

The reason for this performance impact is that, once the RLS predicate is applied to a query, it will be applied as a predicate to the query. Because of the disjunction, the query may result in a scan. For details on the difference between scan and seek, I would recommend reading Craig Freedman’s “scans vs. seeks” article.

We are working on trying to optimize some of these scenarios for RLS usage, but we also know we may not be able to address all possible scenarios right away. Because of that, we would like to share an example on how to improve performance under similar circumstances on your own.

The scenario we will analyze is a slight modification to the scenario from the previous RLS blog post, but with one addition: The application needs to allow a super-user/administrator to access all rows.

The way we will identify the super-user in our application, is when CONTEXT_INFO returns null. On SQL Server, CONTEXT_INFO returns null if it has not been set to another value; on Azure SQL Database, CONTEXT_INFO is pre-populated with a unique connection GUID, so you would need to execute SET CONTEXT_INFO 0x to reset it to null for this 'admin' mode. To enable this behavior, we decide to modify the SECURITY POLICY to add the new logic:

 CREATE FUNCTION [rls].[fn_userAccessPredicate_with_superuser](@TenantId int) 
 RETURNS TABLE 
 WITH SCHEMABINDING 
 AS
 RETURN SELECT 1 AS fn_accessResult 
 WHERE DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID ('AppUser')
 AND 
 ( CONVERT(int, CONVERT( varbinary(4), CONTEXT_INFO())) = @TenantId 
 OR CONTEXT_INFO() is null )
 GO 
 
 ALTER SECURITY POLICY [rls].[tenantAccessPolicy] 
 ALTER FILTER PREDICATE [rls].[fn_userAccessPredicate_with_superuser]([TenantId]) on [dbo].[Sales]
 GO 

Unfortunately, this seemingly simple change seems to have triggered a regression in your application performance, and you decide to investigate, comparing the plan for the new predicate against the old one.

 
Fig 1. Plan when using [rls].fn_userAccessPredicate] as a predicate.

 
Fig 2. Plan when using [rls].fn_userAccessPredicate_with_superuser] as a predicate.

And after the analysis, the reason seems obvious: the disjunction you just added is transforming the query from a seek to a scan.  

You also realized that this particular disjunction has a particularity: one side would expect a seek (i.e. TenantId = value ) and the other side of the disjunction would result in a scan (Administrator case), so in this case it may be possible to get better performance by trying to change this particular characteristic and transform both sides of the disjunction into seeks.

How to address this problem? One possibility in a scenario like this one is to transform the disjunction into a range. How would we accomplish it? By transforming the notion of null into a range that encompasses all values.

First, we alter the security policy to use the older version, after all we don’t want to leave our table unprotected while we fix the new predicate:

 ALTER SECURITY POLICY [rls].[userAccessPolicy] 
 ALTER FILTER PREDICATE [rls].[fn_userAccessPredicate]([TenantId]) on [dbo].[Sales]
 GO

Then we create a couple of functions that will help us define the min and max for our range based on the current state of CONTEXT_INFO. Please notice that these functions will be data type-specific:

 -- If context_info is not set, return MIN_INT, otherwise return context_info value as int
 CREATE FUNCTION [rls].[int_lo]() RETURNS int
 WITH SCHEMABINDING
 AS BEGIN
 RETURN CASE WHEN context_info() is null THEN -2147483648 ELSE convert(int, convert(varbinary(4), context_info())) END
 END
 GO
 
 -- If context_info is not set, return MAX_INT, otherwise return context_info value as int
 CREATE FUNCTION [rls].[int_hi]() RETURNS int
 WITH SCHEMABINDING
 AS BEGIN
 RETURN CASE WHEN context_info() is null THEN 2147483647 ELSE convert(int, convert(varbinary(4), context_info())) END
 END
 GO

And then we proceed to redefine the predicate function and security policy using a range:

 -- Now rewrite the predicate
 ALTER FUNCTION [rls].[fn_userAccessPredicate_with_superuser](@TenantId int) 
 RETURNS TABLE 
 WITH SCHEMABINDING 
 AS 
 RETURN SELECT 1 AS fn_accessResult 
 WHERE DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID ('AppUser') -- the shared application login
 AND 
 -- tenant info within the range:
 -- If context_info is set, the range will point only to one value
 -- If context_info is not set, the range will include all values
 @TenantId BETWEEN [rls].[int_lo]() AND [rls].[int_hi]() 
 GO 
 
 -- Replace the predicate with the newly written one
 ALTER SECURITY POLICY [rls].[tenantAccessPolicy] 
 ALTER FILTER PREDICATE [rls].[fn_userAccessPredicate_with_superuser]([TenantId]) on [dbo].[Sales]
 GO 

To finalize let’s look at the new actual execution plans:

 
Fig 3. Plan when using [rls].fn_userAccessPredicate_with_superuser] as a predicate.

This new function will allow a ranged scan in both circumstances. In the case of CONTEXT_INFO being set, this range will be “between @min_value and @max_value”, which will allow the query optimizer to take advantage of the index on TenantID.

NOTE: When you test this functionality with a small table, you may see a scan instead of a seek, even though you have a nonclustered index on the tenantId Column. The reason for this is that the query optimizer may be calculating that for a particular table a scan may be faster than a seek. If you hit this behavior, try using “WITH (FORCESEEK)” at the end of your SELECT statement to give the optimizer a hint that a seek is preferred.

Obviously this is not the only scenario where you may need to rewrite a security predicate in order to improve performance, and this is certainly not the only workaround, but hopefully this example will serve to give you an example to follow for similar scenarios and ideas for other scenarios.

To conclude, I would like to reiterate that we are currently investigating how to improve performance on predicates similar to the one I showed here with a disjunction being used to distinguish between filtered queries and cases where a user must not be subject to filtering. We will update you with news on the potential solution once it becomes available.

*** Update. Sample source code available at https://rlssamples.codeplex.com/SourceControl/latest#RLS-Middle-Tier-Apps-Demo-using_disjunctions.sql

Comments

  • Anonymous
    August 30, 2016
    The comment has been removed
  • Anonymous
    September 02, 2016
    does disjunctive predicate contribute to query time?
    • Anonymous
      September 02, 2016
      please i wil like to know if the disjunctive predicates also contribute to query time. thank you
      • Anonymous
        September 20, 2016
        Hi Moses,Is your question whether having a disjunction in the predicate can increase query time?In general, the cost of evaluating a disjunction is small and should not affect query time. However, if the disjunction causes a significant plan change, like the one described in this blog post, then the difference in query time can be big.In the scenario described in this post, the disjunction causes the plan to change from an index seek to a full table scan which can increase query time by orders of magnitude. Using a scalar UDF as a workaround allows us to help the query optimizer generate a more efficient plan.Other disjunction that don't affect the query plan wouldn't cause query times to increase.Please let me know if you have further questionsThanks,Panagiotis Antonopoulos
  • Anonymous
    November 30, 2016
    Here's what I did which seems cleaner to me and also doesn't require the two additional functions:RETURN SELECT 1 AS fn_accessResult WHERE DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID ('AppUser') -- the shared application login AND @TenantId = IsNull(CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())), @TenantId )(and for Florent's question, this would also work when the tenant id was a uniqueidentifier.)