Application.Configuration_ApplyRowLevelSecurity
Documentation | WideWorldImporters |
Schema | Application |
Name | Configuration_ApplyRowLevelSecurity |
Applies row level security to filter customers by sales territory related roles.
Script
CREATE PROCEDURE [Application].Configuration_ApplyRowLevelSecurity WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; DECLARE @SQL nvarchar(max); BEGIN TRY; SET @SQL = N'DROP SECURITY POLICY IF EXISTS [Application].FilterCustomersBySalesTerritoryRole;'; EXECUTE (@SQL); SET @SQL = N'DROP FUNCTION IF EXISTS [Application].DetermineCustomerAccess;'; EXECUTE (@SQL); SET @SQL = N' CREATE FUNCTION [Application].DetermineCustomerAccess(@CityID int) RETURNS TABLE WITH SCHEMABINDING AS RETURN (SELECT 1 AS AccessResult WHERE IS_ROLEMEMBER(N''db_owner'') <> 0 OR IS_ROLEMEMBER((SELECT sp.SalesTerritory FROM [Application].Cities AS c INNER JOIN [Application].StateProvinces AS sp ON c.StateProvinceID = sp.StateProvinceID WHERE c.CityID = @CityID) + N'' Sales'') <> 0 OR (ORIGINAL_LOGIN() = N''Website'' AND EXISTS (SELECT 1 FROM [Application].Cities AS c INNER JOIN [Application].StateProvinces AS sp ON c.StateProvinceID = sp.StateProvinceID WHERE c.CityID = @CityID AND sp.SalesTerritory = SESSION_CONTEXT(N''SalesTerritory''))));'; EXECUTE (@SQL); SET @SQL = N' CREATE SECURITY POLICY [Application].FilterCustomersBySalesTerritoryRole ADD FILTER PREDICATE [Application].DetermineCustomerAccess(DeliveryCityID) ON Sales.Customers, ADD BLOCK PREDICATE [Application].DetermineCustomerAccess(DeliveryCityID) ON Sales.Customers AFTER UPDATE;'; EXECUTE (@SQL); PRINT N'Successfully applied row level security'; END TRY BEGIN CATCH PRINT N'Unable to apply row level security'; PRINT ERROR_MESSAGE(); THROW 51000, N'Unable to apply row level security', 1; END CATCH; END; |
Exported: 2019-02-04 23:04, Last imported: 2018-08-10 09:51