Row level security (RLS) provides us with a security tool that can limit data access on the row level of data. This security technique serves value in situations where we may require delineating access to data where a “whole” is stored, but only a “part” needs to be accessed by users — such as a product list from all companies where a company can only see their products.
While this post will do an example in SQL Server, row level security can be used in SQL Server 2016 and above, Azure SQL, Azure SQL Managed Instances, Azure Synapse and Warehouse in Fabric.
When Would We Use Row Level Security?
While RLS isn’t the only tool that will help us in these situations, it may be a tool that we find useful in the below situations that we could use it. We might consider RLS in the following circumstances:
- Complying with strict security requirements from our company or legal requirements where access to sensitive data must be filtered by user or role within the company.
- When the data we store on the row level relate directly to the responsibilities of the user or role who views the data. If the data relate to the user or role on a column level, we would consider dynamic data masking.
A visual way to think about RLS is a pie chart where each piece of the pie represents a user and each user should only be able to view their piece of the entire pie. Like with dynamic data masking as we’ll see, there are some security concerns with RLS, which is one reason why it’s not high on my recommendation list of security tools.
Row Level Security Considerations
Like with dynamic data masking, row level security (RLS) invites attacks because the data are present in the database. A hacker knows that if he compromises your database, he can get access to your data. In the case of RLS, the hacker knows that it’s only a matter of permissions. Hackers can use social engineering to determine if a company is using row level security and if this is the case, they know they only have to compromise the database. A more specific example of this (and a form of social engineering) would be to use social media to identify who works at a target company, whether they use RLS, and compromise the person’s access (spearphishing from social media analysis would be another route). Even if we assume the hacker only compromises one employee (one makes it easier to compromise a second and third), that is enough to get the data.
By contrast, in the case of a security approach like always encrypted where a different layer has the key to unencrypt the data, the hacker must compromise two layers — simply getting the data won’t be enough. In both the case of dynamic data masking and row level security, this is not the case.
If we plan to use RLS, we need to ensure that it functions through sufficient testing. As we’ve seen above this, AFTER and BEFORE UPDATE differ in what could happen with users. We would want to have detailed testing scenarios that ensure that row level security will function as intended. We should always remember that we’re designing for failure (or in the case of security, compromise). This means that we test with our architecture as if we’re trying to compromise the security. I highly recommend researching people who’ve used this feature with their architecture, as they will make note of some of the issues they’ve seen in their environment.
Top comments (0)