carloscastilla - Fotolia
Microsoft released a preview of a new feature, referred to as row-level security (RLS), in Azure SQL databases. Because the feature is in preview, it is not intended for production workloads and is not fully supported. RLS lets you control access to rows in a database table based on the characteristics of the user executing a query (e.g., group membership or execution context).
As of April 2015, RLS is available in all V12 deployments in all regions. The feature also will be available in the next on-premises version of SQL Server, SQL Server 2016. And given Microsoft's public commitment to a cloud first strategy, it's not unreasonable to expect Azure features like RLS to hint at what's coming in future releases of SQL Server.
Both Oracle and DB2 have had this feature in their DBMS products for many years. SQL Server users have used various methods to address the need for row-level security. When porting an application's back end to SQL Server from an Oracle or DB2 database that used RLS, significant additional coding at the application or database level was often included in the migration. The new RLS feature will still require work to implement, but the effort is reduced because filtering is automatically deployed to base tables according to the logic in a centrally defined security function.
You will still need a way to "map" security of specific rows back to a characteristic of the user. It could be as specific as the username or the database role membership of the user. An additional column could be added to each table in the targeted database, or you could have your security function look up row assignments by joining with one or more helper tables. The security logic can be as simple or complex as your scenario requires. Also note that if a table with RLS is joined with a table that does not have RLS, the result set will remain filtered according to the security context.
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.
Revisit last year's most exciting new feature for SQL Server -- in-memory
Check out the Microsoft SQL Server security checklist for the top three best practices