Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
K2: Row Level Security with K2 and SQL ServiceBroker
--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
--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];
-- 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
--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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.