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