In the previous article, I introduced a simple way to implement Row-Level-Security implementation for Microsoft SQL Server 2016 database with Active Directory.
Here, I’ll show you a more efficient way to implement RLS when you deal with millions of records in your database and thousands of users in your active directory.
Below is the original implementation of a RLS security predicate:
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
)
)
There are two performance issues in this filter predicate. First, 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 a function called IS_MEMBER, which evaluate whether the current user is a member of the specified Active Directory group. This function will execute poorly when we query tables with millions of rows and we have thousands of active directory users and groups in our domain.
In order to improve the performance, instead of calling IS_MEMBER function, I’m going to create a table in the database to keep Active Directory users and groups. This Active Directory user and group table will allow me to join the data with the table and remove the function IS_MEMBER from the security policy predicate. It will simplify and improve the performance of the query execution.
CREATE TABLE [dbo].[DimActiveDirectoryUser]( [LoginName] [varchar](50) NOT NULL, [GroupName] [varchar](150) NOT NULL, CONSTRAINT [PK_DimActiveDirectoryUser] PRIMARY KEY CLUSTERED ( [LoginName] ASC, [GroupName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] In order to populate this table, we can execute a Power Shell script or create a ADSI linked server. In this article, I’ll use ADSI linked server: USE [master] GO EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'domain\YourUser',@rmtpassword='Your Password' GO EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true' GO
Be sure to replace the values for the following parameters @rmtuser=N’domain\YourUser’,@rmtpassword=’Your Password’.
The following is the script used to synchronize our active directory with the new table DimActiveDirectoryUser in the AdventureWorks database. Notice that I only synchronize the groups that start with the wildcard: “AW*”, since they are the only groups used in my application.
USE AdventureWorksDW2014
GO
TRUNCATE TABLE DimActiveDirectoryUser
GO
DECLARE @GroupName VARCHAR(50)
DECLARE db_cursor CURSOR FOR
SELECT CN
FROM OpenQuery(ADSI,
'SELECT cn, ADsPath FROM ''LDAP://DC=domain,DC=com'' WHERE objectCategory=''group'' AND cn=''AW*'''
)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @GroupName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = 'ADSI'
SET @OPENQUERY = 'INSERT INTO DimActiveDirectoryUser(LoginName, GroupName) '
SET @OPENQUERY = @OPENQUERY + ' SELECT sAMAccountName, ''' + @GroupName + ''' FROM OPENQUERY('+ @LinkedServer + ','''
--SET @OPENQUERY = 'SELECT sAMAccountName as Login, CN as Name, GivenName as FirstName,SN as LastName, DisplayName as FullName, UserAccountControl FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = 'SELECT sAMAccountname,givenname,sn,displayname,useraccountcontrol,CN, department FROM ''''LDAP://domain.com/DC=domain,DC=com'''''
SET @TSQL = @TSQL + ' WHERE objectCategory=''''person'''' '
SET @TSQL = @TSQL + ' AND objectClass=''''user'''''
SET @TSQL = @TSQL + ' AND memberOf=''''CN=' + @GroupName + ',OU=AdventureWorks,DC=domain,DC=com'''' '
SET @TSQL = @TSQL + ' '')'
PRINT @TSQL
EXEC (@OPENQUERY+@TSQL)
PRINT @TSQL
FETCH NEXT FROM db_cursor INTO @GroupName
END
CLOSE db_cursor
DEALLOCATE db_cursor
GO
-- Update values to Match Predicate Conditions
UPDATE DimActiveDirectoryUser SET LoginName = 'DOMAIN\' + LoginName, GroupName = REPLACE(GroupName,'AW ','')
Notice that the last update to the LoginName and GroupName facilitates a better match in our security predicate.
Finally, the security predicate is simplified. No more OR conditions or IS_MEMBER function in the security predicate. GeographyAccessPredicate is a join between the DimGeography and the DimActiveDirectoryUser tables.
DROP SECURITY POLICY RLS.AWSecurityPolicy go DROP FUNCTION RLS.GeographyAccessPredicate go CREATE FUNCTION RLS.GeographyAccessPredicate(@GeographyKey AS INT) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS AccessResult FROM dbo.DimGeography G INNER JOIN dbo.DimActiveDirectoryUser U ON G.GeographyKey = @GeographyKey AND U.LoginName = SUSER_NAME() AND G.EnglishCountryRegionName = U.GroupName GO CREATE SECURITY POLICY RLS.AWSecurityPolicy ADD FILTER PREDICATE RLS.GeographyAccessPredicate(GeographyKey) ON dbo.DimCustomer
The performance was improved 2X compared with initial version of the security predicate. You’ll notice this difference when dealing with millions of records and thousand users in your active directory. It also depends of the SQL Server configuration.
The following is the query execution plan with the RLS security.
-- 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 implementing RLS keep in mind that there is always an overhead in performance when querying a secured table with RLS. The performance of the queries results depends on how you implement the WHERE condition logic and this logic can be optimized using indexes in the joined fields and avoiding complex functions in your RLS security policy.
