Making Azure SQL more secure - Part 2

I had planned to review Row-level Security and Transparent Data Encryption (TDE), but support for TDE has not been announced yet (even though I can see the relevant system tables in V12 databases). So in this part I will just be giving you my assessment of row-level security.

Row-level security provides a method to filter rows in a table based on the identity of a connected database user.

My conclusion: I can see the convenience of centralising this row filtering logic, but I would not be comfortable including it my development projects. SQL Server has alternative methods of encapsulating filtered views of your data that are transparent, which is important when developing, reviewing, optimising, and testing SQL queries. In my opinion, it would be too easy to overlook the existence and effect of any row-level security predicates.

In summary, you implement row-level security by creating a Security Policy object that adds a predicate function to the database table to be filtered. Once the security policy is enabled, then the function is applied as an implicit WHERE clause for any DML statements that reference the table. You can only add one security policy per table so the function must provide for all your filtering criteria.

Whether or not you can implement row-level security in your application depends upon whether it meets two requirements: a) you can identify the connected user, and b) you can relate that user to the table rows to be filtered. Any function in SQL Server can readily access session information that identifies the database user but, for row-level security to work, there must also be some column (or columns) in your table that can be directly or indirectly related to the user. These columns are then passed as arguments to the predicate function.

In the following code example, I have added a security policy that filters customers to the sales person who is the connected user.

In practice, most applications will use a shared database user, so for this to be effective the calling application must also populate the context information (Context_Info) value that is available to store any session state data. The predicate function would then reference this information rather than the session user’s name or Id.

If you implement row-level security then you need to keep in mind that there is an implicit predicate added to every query on the filtered table. This means that the query optimiser is always factoring this into its execution plan. It’s difficult to optimise queries when you are working with an implicit predicate, so don’t be surprised to see more index scans if there are no usable indexes on the filtered columns. On the plus side, the predicate functions must be in-line table-valued functions so they are included in any query plan, which results in a more efficient execution plan.

Taking the example above, the query plan has included the security policy’s function as a predicate for the filter operation. If there had been an appropriate index on the SalesPerson column then the query plan could even have included an index seek.

As a final consideration, from a security perspective, row-level security depends upon the integrity of the user identifier that is referenced in the predicate function. The supporting documentation from Microsoft does address some concerns in this area, so do not assume it is necessarily a secure way to control data access.