SQL Server Row Level Security

SQL Server Row Level Security

Row-level security (RLS) is a feature added as of SQL Server 2016. Instead of encrypting or decrypting a database’s table data, it restricts and filters a table’s row-level data in accordance with security policies defined by the user. This enables the database engine to limit the number of exposed data rows. This is a simple and powerful SQL Server security control that is transparent to both clients and user applications.

Permissions

Creating, altering, or dropping security policies requires the ALTER ANY SECURITY POLICY permission. Creating or dropping a security policy requires ALTER permission on the schema.

Additionally, the following permissions are required for each predicate that is added:

·         SELECT and REFERENCES permissions on the function being used as a predicate.

·         REFERENCES permission on the target table being bound to the policy.

·         REFERENCES permission on every column from the target table used as arguments.

-- Create databas RLS

CREATE DATABASE RLS

GO

-- Create schema

CREATE SCHEMA Sales

GO

-- Create table

CREATE TABLE Sales.Orders

    ( 

    OrderID int, 

    SalesRep nvarchar(50), 

    Product nvarchar(50), 

    Quantity smallint 

    );

-- Insert records into table

INSERT INTO Sales.Orders  VALUES (1, 'SalesRep1', 'Valve', 5);

INSERT INTO Sales.Orders  VALUES (2, 'SalesRep1', 'Wheel', 2);

INSERT INTO Sales.Orders  VALUES (3, 'SalesRep1', 'Valve', 4);

INSERT INTO Sales.Orders  VALUES (4, 'SalesRep2', 'Bracket', 2);

INSERT INTO Sales.Orders  VALUES (5, 'SalesRep2', 'Wheel', 5);

INSERT INTO Sales.Orders  VALUES (6, 'SalesRep2', 'Seat', 5);

-- View the  rows in the table 

SELECT * FROM Sales.Orders;

-- Create users

CREATE USER Manager WITHOUT LOGIN; 

CREATE USER SalesRep1 WITHOUT LOGIN; 

CREATE USER SalesRep2 WITHOUT LOGIN;

GO

-- Before granting read access to users

-- Grant read access on the table to each of the users.

GRANT SELECT ON Sales.Orders TO Manager; 

GRANT SELECT ON Sales.Orders TO SalesRep1; 

GRANT SELECT ON Sales.Orders TO SalesRep2;

Create a new schema, and an inline table-valued function. The function returns 1 when a row in the SalesRep column is the same as the user executing the query (@SalesRep = USER_NAME()) or if the user executing the query is the Manager user (USER_NAME() = 'Manager').

-- Create new schema as Security

CREATE SCHEMA Security; 

GO 

-- Create inline function

CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50)) 

    RETURNS TABLE 

WITH SCHEMABINDING 

AS 

    RETURN SELECT 1 AS tvf_securitypredicate_result

WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager'; 

GO

Create a security policy adding the function as a filter predicate. The state must be set to ON to enable the policy.

-- Create a security policy

CREATE SECURITY POLICY SalesFilter 

ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)

ON Sales.Orders

WITH (STATE = ON); 

GO

-- Allow SELECT permissions to the fn_securitypredicate function

GRANT SELECT ON Security.tvf_securitypredicate TO Manager; 

GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep1; 

GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep2; 

-- Now test the filtering predicate, by selected from the Sales table as each user

EXECUTE AS USER = 'SalesRep1'; 

SELECT * FROM Sales.Orders;

REVERT; 

EXECUTE AS USER = 'SalesRep2'; 

SELECT * FROM Sales.Orders;

REVERT; 

EXECUTE AS USER = 'Manager'; 

SELECT * FROM Sales.Orders;

REVERT;

-- Disable the policy

ALTER SECURITY POLICY SalesFilter 

WITH (STATE = OFF);

Now Sales1 and Sales2 users can see all six rows.

EXECUTE AS USER = 'SalesRep1'; 

SELECT * FROM Sales.Orders;

REVERT;

EXECUTE AS USER = 'SalesRep2'; 

SELECT * FROM Sales.Orders;

REVERT;

-- Connect to the SQL database to clean up resources

DROP USER SalesRep1;

DROP USER SalesRep2;

DROP USER Manager;

 

DROP SECURITY POLICY SalesFilter;

DROP TABLE Sales.Orders;

DROP FUNCTION Security.tvf_securitypredicate;

DROP SCHEMA Security;

DROP SCHEMA Sales;