SQL Server Row level security

In SQL Server, implementing Row-Level Security enables to control access to rows in a database table based on the characteristics of the user executing a query (e.g., group membership or execution context). 

In this post, let us see an example for RLS with Session_Context, advantage of this technique is we don't need to create separate users to enforce row level security.

We can implement RLS by creating SECURITY POLICY and predicates created as inline table valued functions. 

Let us consider this use case, an Insurance provider company database has all the agency company, its adviser, policy and commission details. Insurance provider has the application dashboard where any agency / individual adviser from an agency company can login (using single sign on) and view their details.

Requirement for Insurance provider is one agency company data should not be exposed to other company and only admins can modify the commission data within their company.

Using RLS and by following industry standard data model for Insurance & User role access,
above requirement can be handled effectively.



Below is the sample data script:






DROP DATABASE IF EXISTS InsuranceProvider
CREATE DATABASE InsuranceProvider
GO
 
USE InsuranceProvider
GO

CREATE TABLE Company(Id INT,Name VARCHAR(100),
CONSTRAINT PK_Company PRIMARY KEY (Id))
INSERT Company SELECT 1,'company 1'
INSERT Company SELECT 2,'company 2'

--SSO user table, during user registration all user information like username, password will be stored in this table
--For this example, I have this trimmed version of table. User in this example are referred as Agents
CREATE TABLE [User] (Id BIGINT IDENTITY(1,1),Name VARCHAR(100),
CONSTRAINT PK_User PRIMARY KEY(Id))
INSERT [User] (Name) SELECT 'Agency company 1'
INSERT [User] (Name) SELECT 'Advisor - Agency 1'
INSERT [User] (Name) SELECT 'Advisor - Agency 1'
INSERT [User] (Name) SELECT 'Agency company 2'
INSERT [User] (Name) SELECT  'Advisor - Agency 2'
INSERT [User] (Name) SELECT 'Advisor - Agency 2'

--Relationship between party and party members relationship (Insurance industry standard data model)
CREATE TABLE UserCompanyRelationship(UserId BIGINT,ParentId BIGINT, CompanyId INT,
CONSTRAINT PK_UserCompanyRelationship PRIMARY KEY(UserId,CompanyId),
CONSTRAINT FK_UserCompanyRelationship_User FOREIGN KEY (UserId) 
    REFERENCES [User] (Id),
CONSTRAINT FK_UserCompanyRelationship_Company FOREIGN KEY (CompanyId) 
    REFERENCES Company (Id)
 )
INSERT UserCompanyRelationship SELECT 1,NULL,1
INSERT UserCompanyRelationship SELECT 2,1,1
INSERT UserCompanyRelationship SELECT 3,1,1
INSERT UserCompanyRelationship SELECT 4,NULL,2
INSERT UserCompanyRelationship SELECT 5,2,2
INSERT UserCompanyRelationship SELECT 6,2,2

CREATE TABLE Permission(Id INT,Name VARCHAR(100),
CONSTRAINT PK_Permission PRIMARY KEY (Id))
INSERT Permission SELECT 1,'Agency super User'
INSERT Permission SELECT 2,'Advisor normal User'

CREATE TABLE UserPermission (Id BIGINT IDENTITY(1,1),UserId BIGINT,PermissionId INT,
CONSTRAINT PK_UserPermission PRIMARY KEY (Id),
CONSTRAINT FK_UserPermission_User FOREIGN KEY (UserId) 
    REFERENCES [User] (Id),
CONSTRAINT FK_PK_UserPermission_Permission FOREIGN KEY (PermissionId) 
    REFERENCES Permission (Id))
INSERT UserPermission SELECT 1,1
INSERT UserPermission SELECT 2,2
INSERT UserPermission SELECT 3,2
INSERT UserPermission SELECT 4,1
INSERT UserPermission SELECT 5,2
INSERT UserPermission SELECT 6,2


CREATE TABLE Commission (Id BIGINT IDENTITY(1,1),UserId BIGINT, Amount NUMERIC(18,0),
CONSTRAINT FK_Commission_User FOREIGN KEY (UserId) 
    REFERENCES [User] (Id))
INSERT Commission SELECT 1,300
INSERT Commission SELECT 1,50
INSERT Commission SELECT 2,100
INSERT Commission SELECT 3,100
INSERT Commission SELECT 4,300
INSERT Commission SELECT 4,75
INSERT Commission SELECT 5,100
INSERT Commission SELECT 6,100



Filter predicates silently filter the rows available to read operations (SELECT, UPDATE, and DELETE). 

Block predicates explicitly block write operations (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) that violate the predicate.

Below is the script to create inline predicate function and security policy:





DROP SECURITY POLICY IF EXISTS Security.AgentCommissionAccessSecurityPolicy
DROP FUNCTION IF EXISTS Security.AgentCommissionAccessPredicate
GO

DROP SCHEMA IF EXISTS Security
GO
CREATE SCHEMA Security
GO

--predicate function 
CREATE FUNCTION Security.AgentCommissionAccessPredicate(@UserId INT)
 RETURNS TABLE
 WITH SCHEMABINDING
AS  

RETURN
 SELECT 1 AS CommAccess 
 FROM dbo.UserCompanyRelationship UCR
 WHERE UCR.CompanyId = CAST(SESSION_CONTEXT(N'CompanyId') AS INT)
  AND UCR.UserId = @UserId


GO

--Security policy to enforce RLS
CREATE SECURITY POLICY Security.AgentCommissionAccessSecurityPolicy
 ADD FILTER PREDICATE Security.AgentCommissionAccessPredicate(UserId) ON dbo.Commission
   ,ADD BLOCK PREDICATE Security.AgentCommissionAccessPredicate(UserId) ON dbo.Commission
GO



Below is the script to test, open two or three sessions in SSMS, pass different userId's



--Below is the test script, we can pass userid from a company
--Test - one company cannot view other company data

--Consider UserId is captured through single sign on and application passes the userId to backend
DECLARE @UserId INT = 5, -- input
        @CompanyId INT,
  @ParentId INT

--Below code is to capture the companyid based on the userid and set the session context 
--so that people from one company cannot see other company data
--another logic here is capturing the parentid, in case of super user login, they can view all the data
SELECT @CompanyId = UCR.CompanyId, @UserId = CASE WHEN UCR.ParentId IS NULL THEN NULL ELSE UCR.UserId END
FROM UserCompanyRelationship UCR
WHERE UCR.UserId = @UserId

EXEC sp_set_session_context N'CompanyId',@CompanyId


SELECT * FROM Commission C
WHERE UserId = @UserId
OR @UserId IS NULL




Below is the script to test, open two or three sessions in SSMS, pass different userId's





--Below is the test script, we can pass userid from a company
--Test - only user with admin privilege can insert/update/delete commission data within their company
--Consider UserId is captured through single sign on and application passes the userId to backend
DECLARE @UserId INT = 1, -- input
        @UpdateUser INT = 6, -- agent for whom commission amount needs to be modified
        @CompanyId INT,
  @PermissionId INT
  

SELECT @CompanyId = UCR.CompanyId, @PermissionId = UP.PermissionId
FROM UserCompanyRelationship UCR
JOIN UserPermission UP
ON UP.UserId = UCR.UserId
WHERE UCR.UserId = @UserId

EXEC sp_set_session_context N'CompanyId',@CompanyId

--Only admin user can insert/update/delete a record in commission table

IF @PermissionId = 1
BEGIN
UPDATE Commission SET Amount = -10
WHERE UserId = @UpdateUser
END


Reference:

See Also:

1 comment:

Unknown said...

I am preparing for Azure Data Engineer exams, and this code is quite useful.

Thank you
Vladimir