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:
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:
Below is the script to test, open two or three sessions in SSMS, pass different userId's
Below is the script to test, open two or three sessions in SSMS, pass different userId's
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
1 comment:
I am preparing for Azure Data Engineer exams, and this code is quite useful.
Thank you
Vladimir
Post a Comment