Categories
Datawarehouse

SQL Server 2016 On-Premise with Row-Level Security with Active Directory (Part I)

In this article, I’ll explain a basic use case of Row-Level-Security implementation for Microsoft SQL Server 2016 database.

The AdventureWorks Database supports a fictitious, multinational manufacturing company called Adventure Works Cycles. This company sells products around the world. Sales are ordered from five different countries like Unites States, Germany, United Kingdom, Canada, France and Australia.

Adventure Works Cycles has Sales Teams in each of these countries. These teams should only access to data from their corresponding country.

In the picture below, we can see that Ginger Howell, a sales representative from the United States Sales Team, is trying to get information from the Customer Dimension table (Dim Customer).

When GHowell send the query: “SELECT * FROM DimCustomer” to the database. The DimCustomer table contains customer records not only from USA, but from other countries. The database should retrieve to GHowell only the records corresponding to United States Customers.

SQL Server 2016 introduced a new feature the database engine: Row-Level-Security. Row Level Security (RLS) is a concept that provides security at the table row level within the database layer, instead of at the application layer. RLS is accomplished by using a function (predicate) and the security policy that should be implemented by a database administrator.

We are going to use Adventure Works Database as a sample. You can download the database from this link: https://msftdbprodsamples.codeplex.com/releases/view/125550

Download and restore the Adventure Works DW 2014 Full Database Backup.zip

The following is a simplified diagram that includes only three tables from the Adventure Works database: DimCustomer, DimGeography and FactInternetSales

Now, we have Active directory groups corresponding to each country’s sales team.

We create a SQL Login for each of these Active Directory groups.

With the SQL Logins created, we can implement our security policy.

First, we create a new schema, just to keep SQL objects separate from the existing objects.

CREATE SCHEMA RLS

We create the function that we’ll use as a filter predicate for the security policy.

The predicate function is an inline table valued function. This function returns 1 when a row in the DimGeography table matches the corresponding CountryRegionCode (G.CountryRegionCode = ‘US’) and the user executing the query belongs to the corresponding Active Directory group (IS_MEMBER(‘DOMAIN\AW United States’) = 1).

CREATE FUNCTION RLS.GeographyAccessPredicate(@GeographyKey AS INT)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN SELECT 1 AS AccessResult

FROM dbo.DimGeography G

WHERE

G.GeographyKey = @GeographyKey AND

(
 (
    G.CountryRegionCode = 'US' AND IS_MEMBER('DOMAIN\AW United States') = 1

  )
  OR

 (
    G.CountryRegionCode = 'GB' AND IS_MEMBER('DOMAIN\AW United Kingdom') = 1
 )
  OR
 (
     G.CountryRegionCode = 'DE' AND IS_MEMBER('DOMAIN\AW Germany') = 1
 )
  OR
  (
     G.CountryRegionCode = 'FR' AND IS_MEMBER('DOMAIN\AW France') = 1
   )
  OR
   (
     G.CountryRegionCode = 'CA' AND IS_MEMBER('DOMAIN\AW Canada') = 1
   )
  OR
  (
    G.CountryRegionCode = 'AU' AND IS_MEMBER('DOMAIN\AW Australia') = 1
  )
)
 

Finally, we add the function as filter predicate on the DimCustomer table:

CREATE SECURITY POLICY RLS.AWSecurityPolicy

ADD FILTER PREDICATE RLS.GeographyAccessPredicate(GeographyKey) ON dbo.DimCustomer

After this our Row-Level-Security is implemented!

Now, we’ll test it by running some scripts. We impersonate a user, GHowell, which belongs to the United States Active Directory.

EXECUTE AS LOGIN = 'DOMAIN\GHowell' -- United States Group

SELECT SUSER_NAME()

SELECT FirstName, LastName, EnglishCountryRegionName

FROM dbo.DimCustomer C

INNER JOIN dbo.DimGeography G

ON C.GeographyKey = G.GeographyKey

The query returns only customers from United States region as expected.

Execute the Revert command to return the execution context back to your user.

REVERT;

Now let’s see what happens behind scenes when the query is executed.

-- Show Plan without Security
GRANT SHOWPLAN TO [DOMAIN\Administrator]
GO
ALTER SECURITY POLICY RLS.AWSecurityPolicy WITH (STATE=OFF)
GO
SET SHOWPLAN_ALL ON
GO
SELECT COUNT(*) FROM DimCustomer
GO
SET SHOWPLAN_ALL OFF

The execution plan shows a simple scan to the DimCustomer table, when the security policy is off.

Now, let’s see how is the execution plan when the security policy is applied.

-- Show Plan with Security

ALTER SECURITY POLICY RLS.AWSecurityPolicy WITH (STATE=ON)
GO
SET SHOWPLAN_ALL ON
GO
SELECT COUNT(*) FROM DimCustomer
GO
SET SHOWPLAN_ALL OFF

When the security policy applied, the execution plan shows an extra join. The filter predicate is being applied and joined to the DimCustomer table.

There is an overhead when Row-Level-Security is applied. This impact in the performance of the queries is affected by the implementation of the filter predicate function.

In our filter predicate function GeographyAccessPredicate, there is a WHERE condition that slows down the query performance. The GeographyAccessPredicate function contains OR conditions for each country, which it will denied the use of indexes in the tables.

Second, we use the function called IS_MEMBER, which evaluate whether the current user is a member of the specified Active Directory group. This also will execute poorly when we query tables with millions rows.

In the next article, I will show a better approach for the predicate function, to speed up the query performance.

Bitnami