WikiDBA

SQL Server 2016 has introduced Row Level Security (RLS) – it’s an enhanced security feature that enables control over access to rows in a table. For general security purpose there are also many features like “Always Encrypted”, “Dynamic Data Masking”, “enhancement of Transparent Data Encryption”, but RLS allows us to easily control which users can access which data with complete transparency to the application. This enables us to easily restrict the data based on the user identity or security context. Row-Level Security in SQL Server 2016 helps us to maintain a consistent data access policy and reduce the risk of accidental data leakage.
Actually, Row Level Security (RLS) is a concept that provides security at the row level within the database layer, instead of at the application layer.  RLS may be implemented by using a function and a new security policy feature without even changing application code.

Before implementing RLS, First, we should know the few new terms which one need to learn and understand this feature.

Security Predicate: This is not a new object but an inline table valued function -inline TVF -which contains the logic of filtering the rows.

Security Policy: This is a new object which can be CREATE, ALTER and DROP. It may be consider as a container of predicates which can be applied to tables. One policy can contain security predicate to many tables. A policy can be in an ON or OFF state.

RLS supports two types of security predicates.

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

Let’s take an example :

–Create a Database named “VirendraRLS_Test”

CREATE DATABASE VirendraRLS_Test; 
GO

USE VirendraRLS_Test;
GO

— Create users

CREATE USER Viren WITHOUT LOGIN;
CREATE USER Yaduvanshi WITHOUT LOGIN;
GO
— Create a table with sample data and grant SELECT to the new users
CREATE TABLE dbo.Employee
( [EmployeeName] VARCHAR(25),[BasicSalary] MONEY)
GO

INSERT INTO dbo.Employee
VALUES (‘Viren’,1000),(‘Yaduvanshi’,1200),(‘viren’,1450)
GO

GRANT SELECT ON dbo.Employee TO Viren
GRANT SELECT ON dbo.Employee TO Yaduvanshi
GO
— Create a filter predicate function as below
CREATE FUNCTION dbo.RLSPredicate (@EmployeeName as sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT 1 AS RLSPredicateResult WHERE @EmployeeName = USER_NAME();
GO
— Add filter predicate to the table
CREATE SECURITY POLICY RLSUserFilter
ADD FILTER PREDICATE dbo.RLSPredicate(EmployeeName)
ON dbo.Employee WITH (STATE=ON);
GO

EXECUTE (‘SELECT * FROM Employee’) AS USER=‘Viren’
EXECUTE (‘SELECT * FROM Employee’) AS USER=‘Yaduvanshi’