SQL Server 2016 - Enhancing Securities with Row Level Security

Enhancing Securities with Row Level Security

SQL Server 2016 has introduced Row Level Securities. This is an enhanced security feature that enables control over access to rows in a table.

There are also other features for security purpose and they are “Always Encrypt”, “Dynamic Data Masking”, “Transparent Data Encryption”, but Row Level Security allows easily control which users can access which data with complete transparency to the application.

This enables to restrict and data based on the user identity or security context. Row-Level Security in SQL Server 2016 helps to maintain a consistent data access policy and minimize the risk of accidental data leakage.

It is a concept that provides security at row level within the database layer, instead at application layer.

We should know the few new terms which we need to learn and understand before understanding/ Implementing Row-Level Security

Security Predicate

This is an existing object but an inline table valued function –inline TVF- which contains the logic of filtering the rows.

Security Policy

It’s a new object which can be CREATE, ALTER and DROP. It may be consider as a container of predicates which can apply to tables. Single policy can contain security predicate to many tables. It can be in an ON or OFF state.

Row-Level Security Supports two types of predicates

1. 1)      Filter predicates silently filter the rows available for read operations [SELECT, UPDATE, and DELETE].
2. 2)      Block predicates explicitly block write operation [AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, and BEFORE DELETE] that violate predicate.

An Example of Row-Level Security

-- Create a Database named “mi_test”

CREATE DATABASE mi_test

GO

USE mi_test

GO

-- Create users

CREATE USER user1 WITHOUT LOGIN;

CREATE USER user2 WITHOUT LOGIN;

GO

-- Create a table with sample data and grant SELECT to the new users

CREATE TABLE Employee

       (

              EmployeeName VARCHAR(25),

              Age int

       )

GO

INSERT INTO Employee

VALUES ('user1',21),('user2',19),('USER1',22)

GO

GRANT SELECT ON Employee TO user1

GRANT SELECT ON Employee TO user2

GO

-- Create a filter predicate function

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='user1'

EXECUTE ('SELECT * FROM Employee') AS USER='user2'

To know more about Row-Level Security, please refer the below link:

https://msdn.microsoft.com/en-us/library/dn765131.aspx