Row level security is a new feature introduced in SQL server 2016 to impose restricted viewing of rows based on the user. This is an excellent alternative to encrypting or decrypting a database’s table data as this method helps to restrict and filter row-level data in a table based on the security polcies imposed on the user. By this way, the database engine will be able to control the amount of data exposed to a specific logged in user. This is an excellent SQL Server security protocol which is simple yet powerful to limit unneeded data exposure.
Please continue to read on to understand how row level encryption can be achieved.
Quick tutorial of row level security implementation
For example consider a hospital in which nurse should be able to see the patient details taken care by her and not others’ details. Or lets say, in an organization, the company manager should see details of employees who only report to him.
This can be achieved in MS SQL Server, using a security predicate defined as an inline table-valued function and enforced using a security policy.
Two types of security predicates are supported
- Filter predicates:
Screens the rows available for read operations (like SELECT,UPDATE,DELETE)
- Block Predicates:
It prevents the write operations (like AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) that violate the predicate.
Filter predicate
Below scenario is an example of how the filter predicate works.
Step 1: Execute the below statement to create table named [Nurse_ Patient_details] and insert some details about patient and nurse.
USE [GeoPITS_RLS]
GO
/****** Object: Table [dbo].[Nurse_ Patient_details] Script Date: 19-06-2022 15:21:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Nurse_ Patient_details](
[Patient_ID] [int] NOT NULL,
[Patient Name] [varchar](50) NOT NULL,
[Date of Join] [varchar](50) NULL,
[Problem] [varchar](50) NULL,
[Doctor_ID] [int] NOT NULL,
[Nurse_Login_ID] [int] NOT NULL,
[Nurse_name] [varchar](50) NOT NULL,
[Status] [nvarchar](max) NULL,
CONSTRAINT [PK_Nurse_details] PRIMARY KEY CLUSTERED
(
[Patient_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
USE [GeoPITS_RLS]
GO
INSERT [dbo].[Nurse_ Patient_details] ([Patient_ID], [Patient Name], [Date of Join], [Problem], [Doctor_ID], [Nurse_Login_ID], [Nurse_name], [Status]) VALUES (1, N'George Smith', N'17-06-2017', N'Diabetics', 12, 7864, N'Stella Kim', NULL)
GO
INSERT [dbo].[Nurse_ Patient_details] ([Patient_ID], [Patient Name], [Date of Join], [Problem], [Doctor_ID], [Nurse_Login_ID], [Nurse_name], [Status]) VALUES (2, N'Martin bell', N'02-12-2019', N'Cancer', 24, 7865, N'Teresa bless', NULL)
GO
INSERT [dbo].[Nurse_ Patient_details] ([Patient_ID], [Patient Name], [Date of Join], [Problem], [Doctor_ID], [Nurse_Login_ID], [Nurse_name], [Status]) VALUES (3, N'John robin', N'19-05-2022', N'Dengu fever', 12, 7864, N'Stella Kim', NULL)
GO
INSERT [dbo].[Nurse_ Patient_details] ([Patient_ID], [Patient Name], [Date of Join], [Problem], [Doctor_ID], [Nurse_Login_ID], [Nurse_name], [Status]) VALUES (4, N'Peter right', N'13-01-2021', N'Bone Fracture', 36, 7866, N'Mariyam rose', NULL)
GO
INSERT [dbo].[Nurse_ Patient_details] ([Patient_ID], [Patient Name], [Date of Join], [Problem], [Doctor_ID], [Nurse_Login_ID], [Nurse_name], [Status]) VALUES (5, N'Siva prakasham', N'06-06-2021', N'Heart Attack', 24, 7865, N'Teresa bless', NULL)
GO
INSERT [dbo].[Nurse_ Patient_details] ([Patient_ID], [Patient Name], [Date of Join], [Problem], [Doctor_ID], [Nurse_Login_ID], [Nurse_name], [Status]) VALUES (6, N'Parker Nice', N'19-10-2021', N'Fire Burn', 36, 7866, N'Mariyam rose', NULL)
GO
INSERT [dbo].[Nurse_ Patient_details] ([Patient_ID], [Patient Name], [Date of Join], [Problem], [Doctor_ID], [Nurse_Login_ID], [Nurse_name], [Status]) VALUES (7, N'Aditiya Arunachalam', N'3-8-2020', N'Corona', 48, 7867, N'Latha Arokiasamy', NULL)
GO
INSERT [dbo].[Nurse_ Patient_details] ([Patient_ID], [Patient Name], [Date of Join], [Problem], [Doctor_ID], [Nurse_Login_ID], [Nurse_name], [Status]) VALUES (8, N'Shakthi Kumarasamy', N'5-4-2022', N'Cancer', 48, 7867, N'Latha Arockiasamy', NULL)
GO
Step 2: Create login based on the Nurse_login_id column and grant select privilege.
Use [GeoPITS_RLS]
CREATE USER [7864] WITHOUT LOGIN;
CREATE USER [7865] WITHOUT LOGIN;
CREATE USER [7867] WITHOUT LOGIN;
Use [GeoPITS_RLS]
GRANT SELECT ON [Nurse_ Patient_details] TO [7864];
GRANT SELECT ON [Nurse_ Patient_details] TO [7865];
GRANT SELECT ON [Nurse_ Patient_details] TO [7867];
Step 3: Create a filter predicate using the below TSQL statement.
Use GeoPITS_RLS
GO
CREATE FUNCTION dbo.fn_PND_Security(@UserName AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_PND_Security_Result
WHERE @UserName = USER_NAME()
GO
Step 4 : Create and apply the policy by running the below TSQL.
Use GeoPITS_RLS
GO
Create SECURITY POLICY PNDFilter
ADD FILTER PREDICATE dbo.fn_PND_Security(Nurse_Login_ID)
ON [dbo].[Nurse_ Patient_details]
WITH (STATE = ON);
GO
Step 5: Execute the below statement to check the screening of rows based on the Nurse_Login_ID
Use GeoPITS_RLS
GO
EXECUTE AS USER = '7864';
SELECT * FROM [dbo].[Nurse_ Patient_details];
REVERT;
Now you can see what data can nurse with ID 7864 (Stella Kim) see. Rest all data is hidden.