21.06.2017

Using Row Level Security in a data warehouse dimension

Technical Value

In this Blog I’ll show you how to use the new SQL Server 2016 feature „Row Level Security“ in a Data-Warehouse-Environment where the Security is not directly applied to the operational table/ fact table but a Dimension-Hierarchy.
Last time at customer site I had to evaluate the new SQL Server 2016 feature „Row Level Security“ for his environment and use case.

To be prepared for this scenario I made up an example where we do have a normal star schema as used in Data-Warehouses extended with a User-Security Table.
I use a fact table with a date column, the StoreId as reference to DimStore, ArticleId as reference to DimArticle and a column with the fact „Amount“. So far this is a normal star schema.
In the DimStore-Table we have a hierarchy Store -> Region -> Country.
The new Table UserSecurity now has a row with the Username and the region the user shall see. So our goal is to reduce the lines of the DimStore and so the fact-rows depending on the connected user.
UsingRowLev1
Let’s start to build our scenario.

  1. CREATE DATABASE [RowLevelSecurity]
  2. go
  3. Use [RowLevelSecurity]
  4. go
  1. CREATE TABLE [dbo].[DimArticle]
  2. ([ArticleId] INTEGER IDENTITY(1, 1) NOT NULL,  
  3. [ArticleNumber] INTEGER NOT NULL,  
  4. [ArticleName]   NVARCHAR(100) NOT NULL,  
  5. CONSTRAINT [PKDimArticle] PRIMARY KEY CLUSTERED([ArticleId])
  6. );
  1. CREATE TABLE [dbo].[DimStore]
  2. ([StoreId]     INTEGER IDENTITY(1, 1) NOT NULL,
  3. [StoreNumber] INTEGER NOT NULL,
  4. [RegionId]    INTEGER NOT NULL,
  5. [RegionName]  NVARCHAR(100) NOT NULL,
  6. [CountryId]   INTEGER NOT NULL,
  7. [CountryName] NVARCHAR(100) NOT NULL,
  8. CONSTRAINT [PKDimStore] PRIMARY KEY CLUSTERED([StoreId])
  9. );
  1. CREATE TABLE [dbo].[Fakt]
  2. ([Date]      DATE NULL,
  3. StoreId]   INTEGER NULL,
  4. [ArticleId] INTEGER NULL,
  5. [Amount]    DECIMAL(18, 8) NULL
  6. );
  1. CREATE TABLE [dbo].[UserSecurity]
  2. ([UserSecurityId] INTEGER IDENTITY(1, 1) NOT NULL,
  3. [UserName]       NVARCHAR(100) NOT NULL,
  4. [RegionId]       INTEGER NOT NULL,
  5. CONSTRAINT [PKUserSecurity] PRIMARY KEY CLUSTERED([UserSecurityId])
  6. );

Now we add some sample data…

  1. -- Inserts for table dbo.DimArticle
  2. SET IDENTITY_INSERT [dbo].[DimArticle] ON;
  3. INSERT INTO [dbo].[DimArticle]
  4. ([ArticleId],
  5. [ArticleNumber],
  6. [ArticleName]
  7. )
  8. VALUES
  9. (1, 123, N'MyVeryFirstArticle');
  10. SET IDENTITY_INSERT [dbo].[DimArticle] OFF;
  1. -- Inserts for table dbo.DimStore
  2. SET IDENTITY_INSERT [dbo].[DimStore] ON;
  3. INSERT INTO [dbo].[DimStore]
  4. ([StoreId],
  5. [StoreNumber],
  6. [RegionId],
  7. [RegionName],
  8. [CountryId],
  9. [CountryName]
  10. )
  11. VALUES
  12. (1, 123, 1, N'Lower Saxony', 1, N'Germany'),
  13. (2, 456, 1, N'Lower Saxony', 1, N'Germany'),
  14. (3, 789, 2, N'NRW', 1, N'Germany');
  15. SET IDENTITY_INSERT [dbo].[DimStore] OFF;
  1. -- Inserts for table dbo.Fakt
  2. INSERT INTO [dbo].[Fakt]
  3. ([Date],
  4. [StoreId],
  5. [ArticleId],
  6. [Amount]
  7. )
  8. VALUES
  9. ('2017-06-19', 1, 1, 10),
  10. ('2017-06-19', 2, 1, 10),
  11. ('2017-06-19', 3, 1, 10),
  12. ('2017-06-18', 1, 1, 10),
  13. ('2017-06-18', 2, 1, 10),
  14. ('2017-06-18', 3, 1, 10);
  1. -- Inserts for table dbo.UserSecurity
  2. SET IDENTITY_INSERT [dbo].[UserSecurity] ON;
  3. INSERT INTO [dbo].[UserSecurity]
  4. ([UserSecurityId],
  5. [UserName],
  6. [RegionId]
  7. )
  8. VALUES
  9. (1, N'User1', 1),
  10. (2, N'User2', 1),
  11. (3, N'User3', 2);
  12. SET IDENTITY_INSERT [dbo].[UserSecurity] OFF;

As you can see we do have 3 users who have RegionIds assigned. There is one row for every user and region. If a user should see more than one region it has to appear one time for every region.

To demo the behavior I now add the 3 Users and grant them to read the database objects.

  1. create user User1 without login
  2. create user User2 without login
  3. create user User3 without login
  1. --Grant Data Reader Permission, this is not a text about object level permission ;-)
  2. ALTER ROLE [db_datareader] ADD MEMBER [User1]
  3. ALTER ROLE [db_datareader] ADD MEMBER [User2]
  4. ALTER ROLE [db_datareader] ADD MEMBER [User3]

So every user could now read every object (tables, views,…) in the database.
To achieve row level security before SQL Server 2016 you could implement a view and only grant select to this view for users.
This is such an example view:

  1. CREATE VIEW dbo.vFakt
  2. AS
  3.      SELECT Fakt.[Date],
  4.             Fakt.StoreId,
  5.             Fakt.ArticleId,
  6.             Fakt.Amount,
  7.             DimStore.StoreNumber,
  8.             DimStore.RegionId,
  9.             DimStore.RegionName,
  10.             DimStore.CountryId,
  11.             DimStore.CountryName,
  12.             UserSecurity.UserName
  13.      FROM dbo.Fakt
  14.           JOIN dbo.DimStore ON Fakt.StoreId = DimStore.StoreId
  15.           JOIN dbo.UserSecurity ON DimStore.RegionId = UserSecurity.RegionId
  16.      WHERE UserSecurity.UserName = USER_NAME();
  17. GO

Querying this view as user1 looks like that:

Tabelle User1

The user can only see his region. But as soon as he queries the fact-table itself, he can immediately see every row

  1. EXECUTE AS USER = 'User1';
  2.          select *
  3.          from dbo.vFakt
  4.          --But:
  5.          SELECT *
  6.          FROM dbo.Fakt
  7.           JOIN dbo.DimStore ON Fakt.StoreId = DimStore.StoreId
  8.           JOIN dbo.UserSecurity ON DimStore.RegionId = UserSecurity.RegionId
  9.          revert;

Tabelle User2

Now, with the new SQL Server 2016 feature we can define and apply row level security with a function and policy on the table itself:

  1.  create function fn_MySecurityFunction(@StoreId int)
  2.   returns table
  3.   with schemabinding
  4.   as return select 1 as fn_FunctionResult FROM dbo.Fakt
  5.           JOIN dbo.DimStore ON Fakt.StoreId = DimStore.StoreId
  6.           JOIN dbo.UserSecurity ON DimStore.RegionId = UserSecurity.RegionId
  7.      WHERE UserSecurity.UserName = USER_NAME() and DimStore.StoreId = @StoreId;

This function returns a table and when the expression evaluation is true, it returns 1 for the row. In this case we evaluate the connected username and we defined the evaluation column. In this case the StoreId of the DimStore.

Here I used a trick which is not directly described in books online but was necessary to test for the customer scenario. I did not apply the row level security to the fact table itself but used a double join via DimStore and UserSecurity.

Now we can use this function in a policy, which is applied on our fact table.

  1. create security policy MyFilterPolicy
  2.   add filter predicate dbo.fn_MySecurityFunction(StoreId)
  3.   on  dbo.Fakt
  4.   with (STATE = ON);

Using „with(STATE = ON)“ activates it just in the same statement.
Let’s test it:

  1. EXECUTE AS USER = 'User1';  
  2.   SELECT *
  3.      FROM dbo.Fakt
  4.   REVERT;  

Tabelle User3

  1. EXECUTE AS USER = 'User3';  
  2.   SELECT *
  3.      FROM dbo.Fakt
  4.   REVERT;  

Tabelle User4

The User can directly query the fact table itself or join it with any table she likes but gets only the allowed rows.
(Even Access with other Clients like Excel or PowerBI-Tools will always deliver only the rows the connected user is allowed to see)

What a great feature. :)

Now, let's tidy up:

  1. drop security policy MyFilterPolicy
  2. drop function fn_MySecurityFunction
  3. USE [master]
  4. GO
  5. ALTER DATABASE [RowLevelSecurity] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  6. GO
  7. DROP DATABASE [RowLevelSecurity]
  8. GO

Have much fun with this new feature.
The Full Script can be downloaded here: RowLevelSecurity_FullScript

Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden