Row-Level Security Implementations in MSSQL

Microsoft SQL Server is one of the most popular SQL database platforms available today. With its speed, stability, cloud compatibility, and native integration with various Microsoft products, SQL Server has been the first choice for many businesses that prefer Windows and even Linux.

Given the many benefits of MSSQL and its popularity, we wanted to explore key concepts regarding row-level security in MSSQL and how best to plan your RLS strategies in MSSQL.

Row-level security (or RLS) is a type of security measure that limits records in an SQL Server table depending on a user’s authorization. In other words, the data in the tables is presented differently depending on what user is looking at it – a useful method to hide sensitive information without having to change the underlying data manually.

While different users all have access to the same data, what they are actually able to view will differ depending on their authorization. Each user can only view that information to which they have access and authorization, all other sensitive information is masked from view. 

Some common uses for RLS include:

• Sharing data across different departments or roles. For instance, members of the sales and marketing teams might need access to the same records, but the marketing team has no use for lead and client contact information.
• Maintaining data accessibility for those who need it. For instance, a sales team’s table can contain all client information, but each member of the sales team only sees the client records attached to them. Bob only sees his clients; Bill only sees his; Sally only sees hers. But Gina, the team lead, can see the entire table.

RLS has a few notable benefits: it enables programmatic control over access and editing rows of data. This, in turn, generates advantages such as applying database access outside of applications, coding centralization, and minimizing potential security gaps.

There are also some disadvantages to consider: because it’s a user-level security practice, scalability can be inflexible. As your organization and database grow so do the accumulation of costs and potential errors. 

Moreover, RLS should be a part of a comprehensive security strategy, not the only part. While it’s a useful practice, RLS is not a substitute for encryption or thorough user access controls. This is especially true since it has a single point of failure: administrator account access. With that in mind, using RLS as one in a multilayered security policy will help insulate your organization from risks.

Check out our guide on the basics of row-level security to learn more.

Row-level security has been a native part of MSSQL since SQL Server 2016. As mentioned above, RLS in MSSQL restricts and filters table row-level data based on user-defined security policies, enabling the database engine to limit the number of exposed data rows.

RLS in MSSQL uses filter predicates, block predicates, and security predicate behavior:

• Filter predicates restrict read access to data rows but still allow the data to be modified. In other words, a user cannot select, update, or delete filtered rows, but the rows can be updated so long as they remain filtered.
• Block predicates restrict write operations to data rows. For example, using AFTER INSERT or AFTER UPDATE predicates prevent users from updating row data to specific values defined in the security predicate.
• Security predicates define the behaviors filter and block predicates rely on to function as well as how rows are filtered for each user. There can only be one security predicate per operation, even if that predicate is disabled.

Check out our guide on row-level security for MSSQL for more information.

Row-level security can be a great way to keep your database secure if it’s part of a comprehensive, flexible policy. However, there are a few weaknesses that make it difficult to work with if you don’t account for them early on:

Side-channel attacks are when a bad actor attempts to exploit an accidental leakage of data that might seem irrelevant on the surface. Because RLS only filters information, queries can still potentially call data without direct exposure. 

For example, if a hacker wanted to check the value of client projects, they could run a query with the expression “1/(clientvalue-75000)”. If it causes a “divide by zero” error, the hacker can deduce there’s at least one client record with a value of $75,000.

Since RLS is very database-centric work, it is wise to ensure everyone who works on the database’s architecture – both front- and back-end – understands how RLS works and how to troubleshoot various issues that may crop up.

RLS is great to use with many kinds of data tables, but there are some exceptions. Two of these are when using SCHEMABINDING and Indexed Views.

WITH SCHEMABINDING is a useful predicate function that prevents changes to underlying tables. Though it also prevents access to views like sys.database_principals or other local catalog views, not using it when trying to bind a function to a security policy will throw an error. To sidestep this, you’ll have to duplicate your access control lists if they’re centrally stored so your RLS functions can call them.

Similarly, RLS is incompatible with tables that have indexed views. Trying to create an indexed view from a table with a security policy will throw an error; trying to apply a security policy to a table referenced by an index view will throw another error.

RLS can be useful to simplify your database’s code and user access for specific rows. However, just because certain rows cannot be seen by specific users, the queries they use may still call filtered data and inflate performance times. The same can happen when using SESSION_CONTEXT() when not used carefully.

A workaround to this – though costly to implement depending on the size of your database – is manually assigning row access to different users. While this drastically cuts down on read times, applying this at scale would be difficult.

When the organization expands, this also increases the complexity and quantity of restrictions and authorizations, placing a greater burden on engineering teams. In our recent State of DataSecOps Report, we surveyed security professionals and found that 61% still use manual processes for granting access. This makes the access granting process inefficient and subject to errors. Further, the time and resources spent to keep the authorizations up-to-date can keep engineering teams away from their core responsibilities.

Access control is necessary to ensure data security, however too much or too little access can be a problem for the organization. If the RLS conflicts with other types of access controls such as role-based, attribute-based, or just-in-time access controls; this can reduce processing speed and organizational performance.

Row-level security in MSSQL is important to ensure that users are not able to view sensitive data unnessarily. Therefore, adopting MSSQL RLS is necessary to ensure authorization and access are aligned. 

With Satori you can improve productivity and security by applying just-in-time access for data, allowing users access to the appropriate data on MSSQL without the need for any DBA or Data Engineering resources.

To learn more:

• Book a demo with one of our experts
• Simplified Row-Level Security with Satori
• Blog: Data Security Projects Keep Data Teams Away From Their Core Responsibilities