Row level security on your SQL database
One of the cool new features of SQL 2016 or SQL Azure is that you can add row level security to your database directly.
So you don't need to use a cube or a tabular model to implement row level security. Let me show you how you can use it. The example is based on Adventureworks, the sample database from Microsoft. In this example I am going to show how you can restrict acces to certain colors of products (not really a realistic scenario I guess, but it's done pure as an example).
First we are going to create a table where we store for each user what colors he can see.
CREATE TABLE [dbo].[UserRights]( [id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL, [login] [nvarchar](100) NULL, [color] [nvarchar](15) NULL)
second, we are going to create a function that checks wether a user can see a certain color:
Make sure to add yourself to the userrights table, or add an exception in the function below to make sure certain roles can see al colours regardless.
Create Function dbo.fn_securitypredicateColor (@color nvarchar(15)) returns table with Schemabinding as return select 1 as [fn_securityPredicateColorresult] from dbo.userrights rr where( rr.login = SUSER_name() and rr.color = @color )
and lastly, we need to enable row level security on the products table:
CREATE SECURITY POLICY [dbo].[fn_security] ADD FILTER PREDICATE dbo.fn_securitypredicateColor([color]) ON [SalesLT].[Product] WITH (STATE = ON);
There you go!
You may want to restrict acces based on roles rather than users, but you can build from this example and try it out yourself 🙂