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.
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
Screens the rows available for read operations (like SELECT,UPDATE,DELETE)
It prevents the write operations (like AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) that violate the 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.
In filter Predicate, there is a chance for the user to insert new row incorrectly or modify it incorrectly. In order to overcome this, one can use block predicate.
With the same table created above, the After insert and update actions can be blocked as seen below.
Step 6: Grant insert and update privilege to below ID’s
Use GeoPITS_RLS
GRANT UPDATE, INSERT ON [Nurse_ Patient_details] TO [7864];
GRANT UPDATE, INSERT ON [Nurse_ Patient_details] TO [7865];
GRANT UPDATE, INSERT ON [Nurse_ Patient_details] TO [7867];
GO
Step 7: Alter the security policy to add block predicate by using executing below SQL statement.
Use GeoPITS_RLS
GO
Alter SECURITY POLICY PNDFilter
ADD BLOCK PREDICATE dbo.fn_PND_Security(Nurse_Login_ID)
ON [dbo].[Nurse_ Patient_details] AFTER UPDATE,
ADD BLOCK PREDICATE dbo.fn_PND_Security(Nurse_Login_ID)
ON [dbo].[Nurse_ Patient_details] AFTER INSERT
WITH (STATE = ON);
GO
Step 8: Try to update the row by using the below statement.
EXECUTE AS USER = '7864';
UPDATE [Nurse_ Patient_details]
SET Nurse_Login_ID = '78941'
WHERE Patient_ID = '3';
Step 9: It will throw below error and will not allow to update row as it does not satisfied the block predicate.
Step 10: Now try to insert a row and it will also throw the same error.
EXECUTE AS USER = '7864';
INSERT [dbo].[Nurse_ Patient_details] ([Patient_ID], [Patient Name], [Date of Join], [Problem], [Doctor_ID], [Nurse_Login_ID], [Nurse_name], [Status]) VALUES (9, N'Aditiya Arunachalam', N'3-8-2020', N'Corona', 48, 7867, N'Latha Arokiasamy', NULL)
REVERT;
Block predicate will restrict user from updating the existing row and inserting a new row, if it does not satisfie the block predicate.
Row level security is practically used in many real life scenarios. To comply with data protection policies and norms, lot of businesses need to protect personally identifiable information in a holistic manner. This is practically very useful in scenarios where businesses thriving in the medical industry need to comply with regulations like HIPAA. This is just one of the many ways to implement data security in an application. Multi-tenant applications would need logical separation between each tenant's data.
To understand more about database security features, get in touch with us for a security audit, any security implementation and compliance management.
