SQL Server 2016 : Implement Row Level Security using Predicate Function and Security Policy
Introduction
Information Security had always been an important aspect in SQL Servers. At times we only want a set of users to view the information in a table. Creating views that shows only the desired subset of rows to the respective users is one way to abstract away information that is not related to a particular user. However if we have a target audience of say 20 groups, we will end up creating 20 views. If there are 40 such SQL Server tables, the views will increase exponentially making it difficult to maintain. It is at this time Row Level Security comes to our help.
Row level security is a new feature that was introduced in SQL Server 2016. Row Level Security enables us to define the restrictions at the row level of the table such, which we can define with which the users can view the rows. We can define the filter condition as a Predicate function, which can be applied on the table, using a Security Policy.
Row Level Security Implementation
Row Level Security (RLS) Implementation contains two parts, which are given below.
- Creating a Predicate Function
A Predicate function is an Inline Table Valued function, which checks whether the user who is executing a particular query on the table has the permission to the rows in the table or not. It will return the value 1 for each row in the table, where the user has an access, else returns no result.
*
* - Security Policy
A Security Policy, once created will attach the Predicate function filter to the table. This ensures that whenever a user runs some SQL commands, which references the table in the Security Policy, it runs the Predicate function as well.
Prerequisite
We will go over a practical demo scenario to understand RLS in action. Let’s create a table named Departments, which contains the Department details for different departments like SQL Server, SharePoint, Java, Python etc.
CREATE TABLE[Department](
[FirstName][varchar](50) NOT NULL, [LastName][varchar](50), [Experience][int], [UserDepartment][varchar](20))
GO
INSERT INTO[Department]
VALUES('Rajesh', 'Pillai', 4, 'SQLServer')
INSERT INTO[Department]
VALUES('John', 'Bhaskar', 6, 'Python')
INSERT INTO[Department]
VALUES('Jack', 'Daniel', 6, 'Java')
INSERT INTO[Department]
VALUES('Anuraj', 'KS', 7, 'Python')
INSERT INTO[Department]
VALUES('Jinesh', 'Raj', 5, 'SharePoint')
INSERT INTO[Department]
VALUES('Mathew', 'John', 8, 'Java')
INSERT INTO[Department]
VALUES('Don', 'Vincent', 9, 'Python')
INSERT INTO[Department]
VALUES('George', 'Miller', 8, 'SharePoint')
Practical Scenario
The table is in place and now we want to implement a scenario where, when the users try to access the table, only their own department rows should be available for the view. By default, we would be able to get all the details from the table, using the select * statement.
Once we implement Row Level Security, the statement given above will restrict the user as he/she is not allowed to access the rows, where he is not granted the access.
After RLS implementation, we can execute the query under specific user context to get only the desired data.
Implement Row Level Security
Now, let’s see how can we implement row level security on the table, which we had created recently. As a part of the demo, we will see how to view only the rows that the department specific users are permitted to see. For this, we will create few department users without the login, as shown below.
CREATE USER SharePoint WITHOUT LOGIN
CREATE USER Python WITHOUT LOGIN
CREATE USER Java WITHOUT LOGIN
CREATE USER SQLServer WITHOUT LOGIN
GRANT SELECT ON Department TO SharePoint
GRANT SELECT ON Department TO Python
GRANT SELECT ON Department TO Java
GRANT SELECT ON Department TO SQLServer
Predicate Function
As the next step, let's create a Predicate function, which will filter out the rows based on the user context. It checks whether the user executes a particular query on the table has the permission to the rows in the table or not. It will return the value 1 for each row in the table , where the user has an access, else returns no result.
CREATE FUNCTION dbo.DepartmentPredicateFunction(@UserDepartment AS varchar(20))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS UserAccess
WHERE @UserDepartment = USER_NAME()
GO
Security Policy
After we have created Predicate function, we have to bind it to the table, using Security Policy. We will be using CREATE SECURITY POLICY command to set the security policy in place.
CREATE SECURITY POLICY DepartmentSecurityPolicy
ADD FILTER PREDICATE dbo.DepartmentPredicateFunction(UserDepartment) ON dbo.Department
WITH(STATE = ON)
We have two kinds of Predicates - Filter Predicates and Block Predicates.
- *Filter Predicates *
Filter Predicate filters the data without raising any error for SELECT, UPDATE, and DELETE operations, as defined in Predicate function.
*
* - *Block Predicates
*Block Predicate explicitly raises an error and blocks the user from performing an AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE operations on the row against Predicate function logic.
RLS in action
We have created Predicate function and applied the security policy on the table. Lets see how RLS works. Prior to RLS implementation, select * command, which is used to get all the table data. After RLS, it will block the user from accessing the rows, which are not permitted for his/her access.
Let's try to access the data under the Java User context. This should pick all the rows, where Java user has row level access
EXECUTE AS USER = 'Java'
SELECT * FROM Department
REVERT
Thus, Filter Predicate is filtering out all the rows, where Java user does not have access. It shows only those rows, where he is assigned row level access in the UserDepartment column. Let's run it again for another department SharePoint. We will get the rows, which are associated with SharePoint User context.
Block Predicate
In addition to Filter Predicates, we have Block Predicates . As we know, Filter Predicates work silently without raising any errors. There would be some instances, where we would like to block a user and raise an error by throwing Row Level Encryption error. Once such instance is when a user from SharePoint department tries to update Java Department row, where he has no access. In such an instance, we can use block Predicate to prevent unintended/intentional data manipulation. We have 4 different types of Block Predicates with respect to the point at which the event happens.
- AFTER INSERT
- AFTER UPDATE
- BEFORE UPDATE
- BEFORE DELETE
In order to test out this scenario, we will grant Insert rights on the table for SharePoint and Java users.
Let’s alter the security policy and add a Block Predicate for AFTER INSERT scenario.
ALTER SECURITY POLICY DepartmentSecurityPolicy
ADD BLOCK PREDICATE dbo.DepartmentPredicateFunction(UserDepartment)
ON dbo.Department AFTER INSERT
Now, if we try to update the row, where the user does not have access; it will throw Block Predicate error.
EXECUTE AS USER = 'SharePoint'
INSERT INTO Department(FirstName, LastName, Experience, UserDepartment)
SELECT 'Nimmy', 'Valsan', 6, 'Java'
REVERT
Thus we can see that Block Predicate prevents the users without row level access from executing an Insert operation. We can define similar Block Predicates for Update and Delete commands.
Disable Row Level Security
If no longer required, we can remove the row level security, which has been defined on the table by using Drop Security Policy and Drop Function command.
Drop Security Policy DepartmentSecurityPolicy
Drop Function DepartmentPredicateFunction
Summary
Thus, we saw how to implement the newly introduced row level security feature in SQL Server 2016.you can build your own customized Security feature for Read & Write operations for Tables by creating Inline Table-Valued Functions with Filter & Block predicates which are linked to the Security Policy for respective tables.
See also
Implementing “Row Level Security” (RLS) with “Block Predicates” in SQL Server 2016