SQL Server row-level security: How to implement in Azure SQL databases

• 
  Rick Heiges

  DB Best Technologies
• Published: 15 May 2015

Microsoft has introduced, as a preview feature, row-level security (RLS) in Azure SQL databases. Row-level security enables customers to 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).

Let's look at how you might implement RLS. This simple example expands on the one in Microsoft's online documentation of RLS. This example will help you understand the basics. You can find more information on the Microsoft Developer Network page on creating security policies.

First, we will create some tables in an Azure SQL database and add some data. We have an "Order" table and "Mapper" table (Figure 1). The purpose of the Mapper table is to associate certain types of orders with specific users.

Next, we will create several users and give them overall access to read the tables (Figure 2).

We will create an inline table valued function in a separate schema (Figure 3). The function will return "True" if the additional security column in the table (in this case "User") is equal to a prescribed USER_NAME or if the USER_NAME is equal to a specific user named "Manager." Note that we're defining a generic condition without reference to a specific table.

Next we will create a new security policy that takes the function described above and add that filter to the Mapper table (Figure 4). The security policy is enabled with the "STATE = ON" portion of the command. In this snippet, the policy is being applied to a single specific table.  If there were multiple tables (in this case, with a "User" column defined) that could use this same filtering condition, those tables could also be affected by the same policy. It is considered best practice to keep the functions and policies in a separate schema for organizational reasons as well as to limit what a user could see based on security context.

Let's try it out (Figure 5).

Now let's disable the security policy. This effectively disables RLS for this table. The first SELECT * statement will return all rows because the policy is not enabled. The second one will once again only return the headers (Figure 6).

In the Order table we created, we could add an additional User column, but we want to use the work we have already done on the Mapper table. So if we join the tables, the results will be filtered (Figure 7).  Try the query with the "Sales2" and "Manager" too.

The main problem with this arrangement is that it requires us to change some of the code in our app to make sure that we always join the Order table to the Mapper table. Without joined Order and Mapper tables, we can't use RLS. In order to avoid this problem, the next step is to add a view that selects rows from both Order and Mapper (Figure 8).

We can now use a view instead of a table for RLS (Figure 9). See it work for Sales2 and Manager users.

Having a view enables a more natural flow of coding than using a join to the Mapper table to have RLS enabled. However, the normal workarounds for using a view instead of a table also apply here.

At this point, it would appear that RLS still involves a lot of work. It is true that implementation requires some effort; however, the view option presented above is most likely the first option that you would consider. There is another option with Microsoft's implementation of RLS that makes this more efficient.

Next, we will establish security based on the "helper" table (Figure 10). In this case, it is the Mapper table. We need to create a security function based on this table.

We will then apply this security function via policy to the Order table (Figure 11).

With this implementation of RLS and the appropriate security functions and policies, we can query the Order table without the need of a view. In other words, we still have RLS security even though we don't have a direct crosswalk from the Order table to the Mapper table (Figure 12).