#Limited Get a FREE SQL Server Performance Tuning Audit Now ➔

GeoPITS Logo
  • Services
    • SQL Server DBA Remote Support
    • SQL Server Managed Services
    • SQL on Cloud
    • Database Consulting
    • Data Engineering
    • Database Migration
    • Database Performance Tuning
    • Analytics & BI
    • Data Integration
    • Enterprise Application Development
    • SpeedCloud™
    • SQL Compare Tool
  • About
    • The startup story
    • Our Team
    • Clients
    • Recognition
    • Careers
  • Resources
    • Blog
    • Case Studies
    • White Papers
    • SQL Features Explorer
  • Contact Us
  • Get a Quote
  1. Home
  2. Blog
  3. Deciphering Row level Encryption in MS SQL Server

Deciphering Row level Encryption in MS SQL Server

Arunachalam C
Sep, 2022

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

  1. Filter predicates:

Screens the rows available for read operations (like SELECT,UPDATE,DELETE)

  1. 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.