en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
יונ14

Written by: ronen ariely
14/06/2016 08:05 RssIcon

Introduction

Row-Level Security enables customers to control access to rows in a database table. Access to row-level data in a table is restricted by security predicate, defined as an inline table-valued function. The function is then invoked and enforced by a security policy.

According to the BOL, RLS filter predicates are functionally equivalent to appending a WHERE clause. The predicate can be as sophisticated as business practices dictate, or the clause can be as simple as WHERE TenantId = 42. Is this accurate?

Well, the answer is yes, the statement is accurate, but in my opinion a bit confusing and maybe even misleading in some cases, as I saw in forum's questions. For example in most common question people compare using simple filter by column value (where Column = 'A'), to dynamically filter by RLS.

Yes if we create UDF inline table-valued function, and we use it in our query, then we get the same behavior, but in lot of cases we do not need that function since the application has the information and can use different query / different approach.

Important!Row-Level Security (RLS) main purpose is not to give us performance, but to give security on the database level!

In this blog I want to test a common case, where we can get the same filter using information that the application already have.

Case study 01 - filter rows by the user that executes the query

We have a table with column that store user name. We want to filter rows and return only rows with our user name in that column.  Using RLS we can simply create a table function, which get name as input and compare it to the user that execute the query using the function USER_NAME(). But using our application, we already know what is the user name, therefore we can use simple filter "where name = 'XXXX'".

Important!Row-Level Security (RLS) filter is transparent to the client (the application that send the query). It simplifies the design and coding of security, and gives us much faster developing procedure for cases that we need full Row Level Filtering.

Using RLS we do not need to change the query in our case. The filtered will be done dynamically in the SQL Server side. The developer does not need to develop anything in the apps side.

Using the RLS approach behind the scene the server executes a more complex query using the inline table-valued function that we create for the security predicate. If we execute the same approach manually using inline table-valued function, we will get the same performance as the RLS execution, but in lot of cases we can use direct filter instead.

I have tested several cases for performance. As expected (especially the CPU), in some cases where we can use different information in the application (like we already know the column value for filtering), it cost much more performance using RLS comparing to simple row level filter (assuming that you have the right indexes). In this blog I will add several tests, starting from the basic case.

Preparations

Let's create first table which uses RLS:

USE QQ
GO
 
CREATE SCHEMA MySecurity; 
GO 
   
CREATE FUNCTION MySecurity.fn_securitypredicate(@SalesRep AS sysname) 
    RETURNS TABLE 
WITH SCHEMABINDING 
AS 
    RETURN SELECT 1 AS fn_securitypredicate_result  
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager'
GO
 
CREATE USER Manager WITHOUT LOGIN; 
CREATE USER Sales1 WITHOUT LOGIN; 
CREATE USER Sales2 WITHOUT LOGIN;
GO
 
CREATE TABLE Sales 
    
    OrderID int
    SalesRep sysname, 
    Product varchar(10), 
    Qty int 
    ); 
GO
 
INSERT Sales VALUES  
(1, 'Sales1', 'Valve', 5),  
(2, 'Sales1', 'Wheel', 2),  
(3, 'Sales1', 'Valve', 4), 
(4, 'Sales2', 'Bracket', 2),  
(5, 'Sales2', 'Wheel', 5),  
(6, 'Sales2', 'Seat', 5); 
-- View the 6 rows in the table 
SELECT * FROM Sales; 
GO
 
GRANT SELECT ON Sales TO Manager; 
GRANT SELECT ON Sales TO Sales1; 
GRANT SELECT ON Sales TO Sales2;
GRANT INSERT ON Sales TO Manager; 
GRANT INSERT ON Sales TO Sales1; 
GRANT INSERT ON Sales TO Sales2;
GRANT UPDATE ON Sales TO Manager; 
GRANT UPDATE ON Sales TO Sales1; 
GRANT UPDATE ON Sales TO Sales2;
GRANT SHOWPLAN TO Manager; 
GRANT SHOWPLAN TO Sales1; 
GRANT SHOWPLAN TO Sales2;
GO
 
CREATE SECURITY POLICY SalesFilter 
ADD FILTER PREDICATE MySecurity.fn_securitypredicate(SalesRep)  
ON dbo.Sales 
WITH (STATE = ON); 
GO

Off-Topic: You can examine the RLS and what each user see:

EXECUTE AS USER = 'Sales1'
SELECT * FROM Sales;  
REVERT; 
   
EXECUTE AS USER = 'Sales2'
SELECT * FROM Sales;  
REVERT; 
   
EXECUTE AS USER = 'Manager'
SELECT * FROM Sales;  
REVERT;

Now We can create another table without RLS with the same data

CREATE TABLE Sales2 
    
    OrderID int
    SalesRep sysname, 
    Product varchar(10), 
    Qty int 
    ); 
GO
 
INSERT Sales2 VALUES  
(1, 'Sales1', 'Valve', 5),  
(2, 'Sales1', 'Wheel', 2),  
(3, 'Sales1', 'Valve', 4), 
(4, 'Sales2', 'Bracket', 2),  
(5, 'Sales2', 'Wheel', 5),  
(6, 'Sales2', 'Seat', 5); 
-- View the 6 rows in the table 
SELECT * FROM Sales2; 
GO


OK, time to fill the tables with 60k more rows

/************************* Fill tables with 600k rows */
 
--truncate table Sales2
;With MyCTE as (
    SELECT top 100000 1 a
    FROM SYS.all_objects a
    CROSS JOIN SYS.all_objects b
)
INSERT Sales2 (OrderID, SalesRep, Product, Qty)
SELECT OrderID, SalesRep, Product, Qty
FROM Sales2
CROSS JOIN MyCTE
GO
 
;With MyCTE as (
    SELECT top 100000 1 a
    FROM SYS.all_objects a
    CROSS JOIN SYS.all_objects b
)
INSERT Sales (OrderID, SalesRep, Product, Qty)
SELECT OrderID, SalesRep, Product, Qty
FROM Sales
CROSS JOIN MyCTE
GO
-- (0 row(s) affected)
-- We do not see any row as we don't have permission
EXECUTE AS USER = 'Manager';
    ;With MyCTE as (
        SELECT top 100000 1 a
        FROM SYS.all_objects a
        CROSS JOIN SYS.all_objects b
    )
    INSERT Sales (OrderID, SalesRep, Product, Qty)
    SELECT OrderID, SalesRep, Product, Qty
    FROM Sales
    CROSS JOIN MyCTE
    GO
REVERT;

 

Test 01: Filter with Clustered index vs RLS without Index

* All the statistics are average of several executions.

/********************************* index for the second table */
CREATE clustered INDEX ix_Sales_02 ON Sales2 (SalesRep); 
GO
 
/********************************* Test */
SET STATISTICS TIME ON
GO
 
EXECUTE AS USER = 'Sales1'
SELECT * FROM Sales;
REVERT;
/*
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 695 ms, elapsed time = 695 ms.
 
(300003 row(s) affected)
 
 SQL Server Execution Times:
   CPU time = 6656 ms,  elapsed time = 7814 ms.
*/
 
SELECT * FROM Sales2
WHERE SalesRep = 'Sales1'
GO
/*
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 8 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
 
(300003 row(s) affected)
 
 SQL Server Execution Times:
   CPU time = 218 ms,  elapsed time = 5194 ms.
*/

* Tests in the Azure SQL Server (the smallest database available) worked for more then 01:08 minute for RLS, while direct filter executed in 14 seconds.

 

Test 02: Filter on Clustered index vs RLS with Clustered Index

Was the index the reason for the differences?!? The next test show that this is not the case.

/********************************* index for the second table */
CREATE clustered INDEX ix_Sales_01 ON Sales (SalesRep); 
GO
 
EXECUTE AS USER = 'Sales1'
SELECT * FROM Sales;
REVERT;
/*
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 9 ms.
 
(300003 row(s) affected)
 
 SQL Server Execution Times:
   CPU time = 5860 ms,  elapsed time = 9046 ms.
*/

* Tests in the Azure SQL Server (the smallest database available) worked for more then 01:05 minute for RLS, while direct filter executed in 14 seconds. The CPU did not show a significant differences.

  

Conclusions

In lot of cases we can find a better solution in performance prescriptive in the application side, where we do not need to use any dynamic information in the SQL Server side, or we can use simpler filter conditions in the server side, in order to get better performance.

resources and more information