K2: Row Level Security with K2 and SQL ServiceBroker

01 - Sample Tables and Data.sql

 --Create a simple table to hold data. 
 CREATE TABLE [dbo].[Sales](
     [OrderId] INT PRIMARY KEY,
     [Product] NVARCHAR(50) NULL,
     [Qty] INT NULL,
     [GroupId] INT NULL
 )
GO
--Create a simple data to hold User/Group relations.
CREATE TABLE [dbo].[GroupUsers](
     [GroupId] [int] NULL,
     [GroupName] NVARCHAR(50) NULL,
     [MemberFQN] NVARCHAR(448) NULL,
)
GO
-- Inserting users and groups
INSERT [dbo].[GroupUsers] VALUES
  (1, N'Group 1', N'K2:DENALLIX\Bob'), 
  (2, N'Group 2', N'K2:DENALLIX\Jonno'),
  (3, N'Group 3', N'K2:DENALLIX\Markus')
GO
-- Inserting some Sales Values
INSERT [dbo].[Sales] VALUES
  (1, N'Product 1', 15, 1), 
  (2, N'Product 2', 10, 1),
  (3, N'Product 3', 10, 2),
  (4, N'Product 4', 15, 2), 
  (5, N'Product 5', 10, 3),
  (6, N'Product 6', 10, 3), 
GO 
                    

02 - K2 Service Permissions.sql

--I am granting low priveleges to K2Service account, which is used to connect to the DB. 
  GRANT SELECT, INSERT, UPDATE, DELETE, VIEW Definition ON Sales TO [DENALLIX\K2Service];

03 - New Schema and Inline Function.sql

-- Create a new schema, and an inline table-valued function.
-- The function returns 1 when a row is associated with a Group of a user executing the query

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_securitypredicate(@GroupId INT)  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
RETURN SELECT 1 AS fn_securitypredicate_result   
   WHERE @GroupId IN (
      SELECT GroupId
      FROM [dbo].[GroupUsers]
      --For List method
      WHERE MemberFQN = CAST((SELECT SESSION_CONTEXT(N'__k2_user_fqn')) AS NVARCHAR(MAX))
      --For Create/Read/Update/Delete methods
      OR MemberFQN = CAST((SELECT CONTEXT_INFO()) AS NVARCHAR(MAX))
)
GO

04 - Apply Security Policy.sql

--Create a security policy adding the function as a filter predicate.
CREATE SECURITY POLICY SalesFilter  
ADD FILTER PREDICATE Security.fn_securitypredicate(GroupId)   
ON dbo.Sales  
WITH (STATE = ON);