Introducing row-level security in Azure SQL databases

Benefits of row-level security

At first glance, the major benefit appears to be added security and potentially simplified querying. But simplified querying may be the main reason to consider RLS. With RLS enabled, the system limits the user's access in a more granular manner. If a user's credentials are compromised, the vulnerable surface area is minimized and SQL injection attacks are less fruitful. For example, an application may currently have a statement like this:

It could be easy to either query the database directly or perhaps via a SQL injection attack to change the "A" to a "B" or some other criteria that would produce different results. With RLS enabled, this sort of attack would not be fruitful.

Employing row-level security

The end user will not realize that RLS has been implemented. You'll need to complete work behind the scenes, but there is no required code change. In fact, it could make querying easier because users are already limited to the number of rows that they have access to. Consider implementing RLS in one of your existing applications.

As expected, a certain level of effort is required to attain this extra layer of security. This includes determining which tables will need to have modifications and how to most efficiently map credentials to row-level data. But that is not the entire cost; the nature of RLS is to add another filter predicate to each query on the base tables to limit the number of rows returned. This could affect the performance of any query hitting that table.

As with many brand new features, there are often limitations. That is the case with this feature as well. RLS is incompatible with memory-optimized tables and change data capture. Also Full Text Indices and DBCC SHOW_STATISTICS may "leak" information otherwise protected via RLS.

RLS is a much awaited feature to bring the Microsoft data platform offerings even more on par from a feature-complete point of view with Oracle and DB2. After learning more about the feature and how to implement it in your application, the logic of how it works becomes clear. On the surface, RLS may seem like a feature that would be considered primarily for an application being migrated from Oracle or DB2 that required it. But in today's world where threats to your data are very real, RLS can help to minimize potential exposure to your data. It is transparent to the user and further limits the rows returned. Although there is a way to enforce row-level security through the use of views and triggers, this new feature makes it significantly easier to implement by reducing application maintenance and complexity with access logic centrally located and closer to the data.

About the Author

Rick Heiges is a SQL Server MVP and principal solutions architect for DB Best Technologies LLC. He works with customers educating them about the Microsoft Data Platform to solve business problems and to maximize the value of their data. Rick is very involved in the SQL Server Community via PASS where he spent nine years on the board of directors. You can read his blog on sqlblog.com and follow him on Twitter @heigesr2.