• about reply
Solidsoft Reply Logo
Menu
  • What we do
  • Pharmaceutical Sector
  • The Solid Blog
  • Newsroom
  • Contact Us
  • about Reply
Solidsoft Reply Logo

Search

Focus On

Blog

Making Azure SQL more secure - Part 2

Author: Nicholas Revell

FOCUS ON: Blog, Azure,

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.

Making Azure SQL more secure

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.

Making Azure SQL more secure

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.

RELATED CONTENTS

02.03.2021 - 04.03.2021 / Live Event

Event

Microsoft Ignite

Microsoft Partners Cluster Reply, Solidsoft Reply, Valorem Reply and WM Reply, are participating in "Microsoft Ignite" with a single virtual stand where participants can discover all the latest on Microsoft solutions.

27.02.2020

News & Communication

Reply becomes a Microsoft Azure Expert Managed Services Provider

Reply announces it has become a Microsoft Azure Expert Managed Services Provider (MSP), which is a status awarded to only a small number of highly capable Microsoft managed services provider partners worldwide.

21.12.2018

Blog

Which Microsoft Azure replication service should you use?

Microsoft Azure supports 4 different replication services, locally-redundant storage, zone-redundant storage, geo-redundant storage and read-access geo-redundant storage (also known as LRS, ZRS, GRS and RA-GRS respectively.)

 
 
 
 
Reply ©​​ 2023​ - Company Information -
 PrivacyCookie Settings​
  • About Reply​​​
  • Inves​tors​​
  • Newsroom
  • Follow Reply on
  • ​
  • ​
​
  • ​About Solidsoft Reply
  • Privacy & Cookies Policy
  • Information (Client)
  • Information (Supplier)​