SQL Server Row Level Security (RLS): Basics and a Quick Tutorial
SQL Server Row Level Security (RLS): Basics and a Quick Tutorial
What is 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.
In RLS, the predicates used to enable access to rows can be metadata-based, or may use any other criteria. You can also use RLS to enable access controls based on resource labels.
You apply row-level data security access controls in two steps. First, create an RLS filter function that determines who can view which data. Second, create a security policy at the table-level, using it to implement row-level security access control.
In this article, you will learn:
- Row Level Security Use Cases
- How SQL Server Row Level Security Works
- Quick Tutorial: Adding Row-Level Security to a SQL Server Table
- Best Practices for Row Level Security
- SQL Server Security with Satori
Row Level Security Use Cases
RLS is useful in many use cases. Many organizations need to protect personally identifiable information (PII) in line with the GDPR regulation. Hospitals need to limit access to patient data, to comply with regulations like HIPAA. Multi-tenant applications need logical separation between one tenant’s data and those of others.
In general, RLS can be used to:
- Restrict access to data for organizational reasons, for example to prevent unwanted changes to data by employees who do not directly work on it.
- Restrict access to specific data for security reasons, for example to prevent unauthorized access to sensitive information within a database table.
- Restrict access for compliance purposes, for example to limit access to specific data within a table that is subject to regulatory requirements.
How SQL Server Row Level Security Works
Row Level Security uses inline table-valued functions—these are the security predicate that restricts data access. A table-level security policy invokes and enforces these functions.
Filter predicates
Filter predicates restrict read access to the data, but still allows it to be modified. The application is blind to rows filtered out by the filter predicates; it returns only allowed rows, and if there are no allowed rows returned by a query, the result is a null set. Operations that violate a security predicate fail and return an error.
When reading base table data, filter predicates apply to all get operations—SELECT, DELETE, and UPDATE. Users cannot select, update, or delete filtered rows. They can, however, update them in a manner that leaves them filtered, or INSERT rows.
Block predicates
Block predicates mainly relate to write operations. If you apply an AFTER INSERT or AFTER UPDATE predicate, this prevents users from updating row data to specific values defined in the security predicate. AFTER INSERT block predicates apply to bulk INSERT operations in the same manner as were to singular operations.
BEFORE UPDATE prevents row updates if the current values in the table violate the security predicate. BEFORE DELETE prevents deletion of data that matches the predicate.
A limitation of block UPDATE predicates is that you cannot prevent specific changes to values, for example you cannot prevent users from increasing values in a row. To do that, use triggers to reference new and old values jointly from the intermediate tables.
Security predicate behavior
The following behaviors are common to both filter and block security policies and predicates:
- It is possible to disable a security predicate, and then filtered or blocked rows will not be affected.
- The security policy defines which rows are filtered or blocked for which users and roles—including dbo users and members of db_owner role.
- In a table restricted by a schema-bound security policy, you may alter columns not referenced by security predicates. However, attempting to change schema will generate an error.
- There can be only one security predicate per operation (even if a predicate is disabled, you cannot add another predicate for the same operation).
- Functions used as predicates on a table with a schema-bound security policy cannot be modified (attempts return an error).
Quick Tutorial: Adding Row-Level Security to a SQL Server Table
This tutorial will show you how to add Row-Level Security to a database table. The code is based on the tutorial by Jeff Melnick.
To add an RLS filter predicate to a SQL Server table:
Create a filter predicate that only allows access to a row if the username is ADMIN, or if the username matches the value in the privilegeuser column.
In the code below, the predicate function is defined as schema-binding, and the WHERE statement defines the two conditions for allowing access to the row. Note that at this stage, we do not specify if the predicate is a filter or block predicate.
Now create a security policy that executes the predicate function as a filter predicate. STATE = ON specifies that the predicate is enabled.
The security policy is now enabled in the specified table. Regular users can only access their own rows, while privileged users can access all rows in the table.
Microsoft SQL Server Security With Satori
Satori, The DataSecOps platform, allows companies to enforce security policies from a single location, across SQL Server, as well as their other databases, data warehouses and data lakes. Such security policies can be data masking, data localization, row-level security and more.