RLS (Row Level Security) basics

RLS (Row Level Security) is a brand new feature which has been introduced in Azure, SQL Databases (v12) and SQL Server 2016. This feature provides you access control on rows of tables for users\logins querying the data. Basically, it filters the rows based on certain characteristics of Users\Logins. After getting this feature it becomes very easy to implement separation of duties & you get the power to deny read rights to even those users who have membership of SysAdmin role. Which in turn provides more confidence to your end users. Your data remain safe & accessible to only authorized users.

This is a great security feature which will help multiple industries, in-fact many organizations developed in-house solutions\workarounds to implement something similar on their own; but the maintenance overhead was too much & it becomes very complex to manage on multiple tables.

With this new feature you get this functionality straight out of the box and you don’t even have to change anything on your application side.

RLS is one of the most robust and reliable security features, reason being RLS (Row level security) is implemented at the database level itself, which means there are no back doors\alternatives, once configured properly no unauthorized user can read\modify your data.

Use Case:
This feature becomes a life saver if you are hosting a multi-tenant application and storing data in the same database or if you want to filter rows based on role\other certain characteristic of end users.

We can create two types of security predicates in RLS:
1. Filter Predicates for SELECT, UPDATE, and DELETE they works silently and filter the rows available to read operations & the end user will never come to know that the results were filtered.
2. Block predicates for AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE for only those rows that violate the predicate and these explicitly block write operations so the end user gets an error message and the write operation fails.

 

Let’s understand how these predicates work in different situations.

Filter Predicate

Select: a filter is applied while reading data from the base table & only selected rows are returned.

Update: end user will be able to update only those rows which are visible & not filtered. But Note that users can update rows which are visible as of now, but eventually they will be filtered post update.

Delete: users will be able to delete only visible rows and will not be able to delete filtered rows.

 

Block Predicate

Update: there are two sub-categories of Block update predicate, Before Update and After Update. It works just like triggers means if you want to block updates based on current value of the same\related columns you must reference inserted and deleted intermediate (Magic) tables to get both values and then compare to make a decision.

After update block predicate will be checked by optimizer only if any column value gets modified which is being used in predicate function.

There is no bulk API change that means after insert will apply to Bulk insert just like it works for the regular insert statement.

 

Enough of talking, but I want to know what all permissions one should have to implement row level security?
Well, there are just two permissions which are required to implement row level security (RLS) and those are:
1. ALTER ANY SECURITY POLICY
2. ALTER permission on Schema

The users must have SELECT and REFRENCES permission on the function being used as a predicate along with REFRENCES Permission on target table which is being bound to the policy; and REFRENCES permission on each column from the target table which is used as an argument.

 

In the next post we’ll see how to implement this amazing feature in Azure\SQL Database, stay tuned.

 

Hope you enjoyed the post, feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox
Follow @SQLChamp